Introduction to JOIN Queries

Introduction to JOIN Queries

Imagine you go to a bank, and the teller needs to view both your "account information" and "personal information" tables simultaneously. She doesn't query them separately and manually match them up—instead, she "joins" the two tables using your ID number to see the complete picture at once. JOIN is the magic in SQL that lets multiple tables "talk" to each other.

1. Core Concepts

Why Do We Need JOINs?

When designing a database, we split data into multiple tables to avoid redundancy. For example, the employees table only stores department_id, rather than duplicating the department name and address. But queries often need to "piece back" the complete information, which is where JOINs come in.

-- Without JOIN: can only see department_id, not the department name
SELECT name, department_id, salary FROM employees;

-- With JOIN: employee name and department name displayed together
SELECT e.name, d.name AS department, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;

Table Relationship Types

In our example database:

• departments → employees: One-to-Many (one department has multiple employees)
• products → orders: One-to-Many (one product can appear in multiple orders)

Foreign Key

A foreign key is the bridge that establishes table relationships. It is a column in table A that references the primary key of table B, ensuring data consistency.

-- department_id in the employees table is a foreign key pointing to the id in departments table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department_id INTEGER,           -- Foreign key
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

INNER JOIN Syntax

INNER JOIN returns only rows that match in both tables. If a row has no corresponding data in the other table, it is excluded.

SELECT column_name
FROM table_A
INNER JOIN table_B ON table_A.column = table_B.column;

ON Condition

The ON clause specifies the join condition—i.e., which column is used to match rows between the two tables. It typically compares a foreign key with a primary key.

-- ON condition: employees.department_id = departments.id
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Table Aliases (AS)

When table names are long or you need to distinguish columns with the same name, you can use AS to create aliases for tables, simplifying the writing:

-- Without aliases: must write full names every time
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

-- With aliases: more concise
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

2. Basic Syntax/Usage

INNER JOIN Complete Syntax

SELECT table_A.column1, table_A.column2, table_B.column1
FROM table_A
INNER JOIN table_B ON table_A.foreign_key = table_B.primary_key
WHERE condition
ORDER BY column_name;

Example: Query Employees and Their Departments (Difficulty ⭐)

The most basic need—view the department name each employee belongs to:

SELECT e.name AS employee, d.name AS department, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Output:

employee  department  salary
--------  ----------  --------
Zhang San Technology  15000.00
Li Si     Technology  18000.00
Wang Wu   Marketing   12000.00
Zhao Liu  Finance     13000.00
Qian Qi   Technology  20000.00
Sun Ba    Marketing   11000.00
Wu Shi    Finance     14000.00

Note: Zhou Jiu does not appear because his department_id is NULL. INNER JOIN only retains rows that match in both tables.

Example: Query Order Details (Three-Table Join) (Difficulty ⭐⭐)

In real business scenarios, joining multiple tables is common. Query customer name, product name, unit price, and total for each order:

SELECT o.customer_name, p.name AS product,
       p.price, o.quantity,
       (p.price * o.quantity) AS total
FROM orders o
INNER JOIN products p ON o.product_id = p.id
ORDER BY total DESC;

Output:

customer_name  product         price     quantity  total
-------------  --------------  --------  --------  --------
Xiao Li        iPad Air        4799.00   3         14397.00
Xiao Gang      MacBook Pro     12999.00  1         12999.00
Xiao Wang      iPhone 15       5999.00   2         11998.00
Xiao Ming      iPhone 15       5999.00   1         5999.00
Xiao Gang      iPhone 14       4999.00   1         4999.00
Xiao Hong      Magic Keyboard  999.00    5         4995.00
Xiao Ming      Apple Watch     2999.00   1         2999.00
Xiao Hong      AirPods Pro     1899.00   2         3798.00

3. Common Use Cases

Case 1: Query All Employees in the Technology Department

First find the department ID via JOIN, then filter with WHERE:

SELECT e.name, e.salary, e.hire_date
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Technology';

Output:

name    salary    hire_date
------  --------  ----------
Zhang San 15000.00  2023-01-15
Li Si   18000.00  2022-06-01
Qian Qi 20000.00  2020-08-05

Case 2: Count Employees per Department

SELECT d.name AS department, COUNT(e.id) AS emp_count
FROM departments d
INNER JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Output:

department  emp_count
----------  ---------
Technology  3
Marketing   2
Finance     2

❓ FAQ

Q: Which performs better, JOIN or subquery? A: In general, JOINs perform better because database engines have specialized optimizers for JOINs. Subqueries in some cases may produce temporary tables, resulting in slightly worse performance. However, it depends on data volume and indexing—use EXPLAIN to check the execution plan.

Q: What's the difference between ON and WHERE? A: ON is the join condition that determines how two tables are paired; WHERE is the filter condition that determines which rows are displayed. They have similar effects in INNER JOIN, but differ significantly in LEFT JOIN—ON doesn't affect the preservation of the left table, while WHERE filters out NULL rows.

Q: Are table aliases required? A: Not required, but strongly recommended. Table aliases make SQL more concise, especially in multi-table joins where they significantly improve readability.

Q: What happens if a foreign key is NULL? A: INNER JOIN will exclude that row because there's no matching associated data. If you want to retain those rows, use LEFT JOIN (covered in the next lesson).

📖 Summary

• Databases split data into tables to avoid redundancy, and JOINs "piece back" complete information when querying
• There are three types of table relationships: one-to-one, one-to-many, and many-to-many; the most common is one-to-many
• A foreign key is the bridge between tables, referencing the primary key of another table
• INNER JOIN returns only rows that match in both tables, using ON to specify the join condition
• Table aliases (AS) make SQL more concise; always use them in multi-table joins

📝 Exercises

Exercise 1 (⭐): Write a query to display the customer name, product name, and product category for all orders (requires joining the orders and products tables).

Exercise 2 (⭐⭐): Write a query to display the average salary per department, showing only departments where the average salary exceeds 12000 (hint: JOIN + GROUP BY + HAVING).

Exercise 3 (⭐⭐⭐): Write a query to display the total amount spent by each customer on products, sorted by total amount in descending order. Show customer name, order count, and total amount.

Next Lesson

👉 08-join-types - Join Types Explained: Dive deep into LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN, and master the strategy for choosing the right join type!

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us