Multi-Table Joins

In real applications, data is usually distributed across multiple tables. SQL's JOIN operation allows us to combine data from multiple tables for querying. This chapter will introduce various JOIN types and their use cases in detail.

Why Do We Need JOIN?

Data Normalization

To avoid data redundancy, we usually distribute data across multiple tables:

sql

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2),
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

To query users and their order information, we need to use JOIN:

sql

SELECT 
    users.name,
    orders.total_amount,
    orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id;

Prepare Sample Data

Create Tables

sql

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    amount DECIMAL(10, 2)
);

Insert Data

sql

-- Insert user data
INSERT INTO users VALUES
(1, 'Zhang San', 'Beijing'),
(2, 'Li Si', 'Shanghai'),
(3, 'Wang Wu', 'Guangzhou'),
(4, 'Zhao Liu', 'Shenzhen');

-- Insert order data
INSERT INTO orders VALUES
(101, 1, 'Laptop', 5000),
(102, 1, 'Mouse', 50),
(103, 2, 'Keyboard', 200),
(104, 3, 'Monitor', 1500);

Data Overview:

users table:

id	name	city
1	Zhang San	Beijing
2	Li Si	Shanghai
3	Wang Wu	Guangzhou
4	Zhao Liu	Shenzhen

orders table:

id	user_id	product	amount
101	1	Laptop	5000
102	1	Mouse	50
103	2	Keyboard	200
104	3	Monitor	1500

INNER JOIN

Basic Syntax

sql

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Example

sql

SELECT 
    users.name,
    users.city,
    orders.product,
    orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Result:

name	city	product	amount
Zhang San	Beijing	Laptop	5000
Zhang San	Beijing	Mouse	50
Li Si	Shanghai	Keyboard	200
Wang Wu	Guangzhou	Monitor	1500

Note: Zhao Liu has no orders, so he is not in the result.

Shorthand Form

The INNER keyword can be omitted:

sql

SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id;

Using Table Aliases

sql

SELECT 
    u.name,
    o.product,
    o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

LEFT JOIN

Basic Syntax

sql

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Example

sql

SELECT 
    users.name,
    users.city,
    orders.product,
    orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Result:

name	city	product	amount
Zhang San	Beijing	Laptop	5000
Zhang San	Beijing	Mouse	50
Li Si	Shanghai	Keyboard	200
Wang Wu	Guangzhou	Monitor	1500
Zhao Liu	Shenzhen	NULL	NULL

Note: Zhao Liu has no orders but still appears in the result with NULL order information.

Find Users Without Orders

sql

SELECT 
    users.name,
    users.city
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

Result:

name	city
Zhao Liu	Shenzhen

RIGHT JOIN

Basic Syntax

sql

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Example

sql

SELECT 
    users.name,
    orders.product,
    orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

Explanation: RIGHT JOIN returns all records from the right table (orders), even if there are no matching records in the left table (users).

Note: In practice, LEFT JOIN is more commonly used. RIGHT JOIN can be implemented by swapping table positions with LEFT JOIN.

FULL OUTER JOIN

Basic Syntax

sql

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

Example

sql

SELECT 
    users.name,
    orders.product
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Explanation: Returns all records from both tables, filling unmatched places with NULL.

Note: MySQL does not support FULL OUTER JOIN, but it can be implemented using UNION:

sql

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

CROSS JOIN

Basic Syntax

sql

SELECT columns
FROM table1
CROSS JOIN table2;

Example

sql

SELECT 
    users.name,
    orders.product
FROM users
CROSS JOIN orders;

Explanation: Returns the Cartesian product of two tables, i.e., every combination of each user with each order.

Result rows: 4 users × 4 orders = 16 rows

Use cases: Generate all possible combinations, such as shift schedules, test data, etc.

SELF JOIN

Concept

A table joins with itself, usually used to handle hierarchical relationships.

Example: Employees and Managers

sql

-- Employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO Zhang', NULL),
(2, 'Manager Li', 1),
(3, 'Supervisor Wang', 2),
(4, 'Employee Zhao', 2);

Query employees and their managers:

sql

SELECT 
    e.name AS Employee,
    m.name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Result:

Employee	Manager
CEO Zhang	NULL
Manager Li	CEO Zhang
Supervisor Wang	Manager Li
Employee Zhao	Manager Li