多表连接

在实际应用中，数据通常分散在多个表中。SQL 的 JOIN 操作允许我们将多个表的数据组合在一起进行查询。本章将详细介绍各种 JOIN 类型及其使用场景。

为什么需要 JOIN？

数据规范化

为了避免数据冗余，我们通常将数据分散到多个表中：

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 订单表
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)
);

查询关联数据

要查询用户及其订单信息，就需要使用 JOIN：

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

准备示例数据

创建表

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

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

插入数据

-- 插入用户数据
INSERT INTO users VALUES
(1, '张三', '北京'),
(2, '李四', '上海'),
(3, '王五', '广州'),
(4, '赵六', '深圳');

-- 插入订单数据
INSERT INTO orders VALUES
(101, 1, '笔记本电脑', 5000),
(102, 1, '鼠标', 50),
(103, 2, '键盘', 200),
(104, 3, '显示器', 1500);

数据概览：

users 表：

id	name	city
1	张三	北京
2	李四	上海
3	王五	广州
4	赵六	深圳

orders 表：

id	user_id	product	amount
101	1	笔记本电脑	5000
102	1	鼠标	50
103	2	键盘	200
104	3	显示器	1500

INNER JOIN（内连接）

基本语法

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

示例

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

结果：

name	city	product	amount
张三	北京	笔记本电脑	5000
张三	北京	鼠标	50
李四	上海	键盘	200
王五	广州	显示器	1500

注意：赵六没有订单，所以不在结果中。

简写形式

INNER 关键字可以省略：

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

使用表别名

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

LEFT JOIN（左连接）

基本语法

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

示例

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

结果：

name	city	product	amount
张三	北京	笔记本电脑	5000
张三	北京	鼠标	50
李四	上海	键盘	200
王五	广州	显示器	1500
赵六	深圳	NULL	NULL

注意：赵六没有订单，但仍然出现在结果中，订单信息为 NULL。

查找没有订单的用户

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

结果：

name	city
赵六	深圳

RIGHT JOIN（右连接）

基本语法

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

示例

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

说明：RIGHT JOIN 返回右表（orders）的所有记录，即使左表（users）中没有匹配的记录。

注意：在实际应用中，LEFT JOIN 更常用，RIGHT JOIN 可以通过调换表的位置用 LEFT JOIN 实现。

FULL OUTER JOIN（全外连接）

基本语法

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

示例

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

说明：返回两个表中的所有记录，没有匹配的地方用 NULL 填充。

注意：MySQL 不支持 FULL OUTER JOIN，但可以用 UNION 实现：

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（交叉连接）

基本语法

SELECT columns
FROM table1
CROSS JOIN table2;

示例

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

说明：返回两个表的笛卡尔积，即每个用户与每个订单的组合。

结果行数：4 个用户 × 4 个订单 = 16 行

使用场景：生成所有可能的组合，如排班表、测试数据等。

SELF JOIN（自连接）

概念

表与自身进行连接，通常用于处理层级关系。

示例：员工和经理

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, '张总', NULL),
(2, '李经理', 1),
(3, '王主管', 2),
(4, '赵员工', 2);

查询员工及其经理：

SELECT 
    e.name AS 员工,
    m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

结果：

员工	经理
张总	NULL
李经理	张总
王主管	李经理
赵员工	李经理

多表连接

连接三个表

-- 添加产品表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    category VARCHAR(50)
);

-- 查询用户、订单和产品信息
SELECT 
    u.name AS 用户,
    o.amount AS 金额,
    p.name AS 产品,
    p.category AS 分类
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

连接顺序

-- 先连接 users 和 orders，再连接 products
SELECT *
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id;

连接条件

等值连接

SELECT *
FROM users
JOIN orders ON users.id = orders.user_id;

非等值连接

-- 查询价格在某个范围内的订单
SELECT *
FROM orders o
JOIN price_ranges pr ON o.amount BETWEEN pr.min_price AND pr.max_price;

多条件连接

SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id 
    AND o.order_date >= '2024-01-01';

使用 WHERE 过滤

在 JOIN 后过滤

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

JOIN 条件 vs WHERE 条件

-- 在 JOIN 条件中过滤（推荐）
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;

-- 在 WHERE 中过滤
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

区别：

JOIN 条件中的过滤不影响 LEFT JOIN 返回左表的所有记录
WHERE 中的过滤会影响最终结果

聚合与 JOIN

统计每个用户的订单数

SELECT 
    u.name,
    COUNT(o.id) AS 订单数,
    COALESCE(SUM(o.amount), 0) AS 总金额
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

结果：

name	订单数	总金额
张三	2	5050
李四	1	200
王五	1	1500
赵六	0	0

性能优化

1. 使用索引

-- 为连接列创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

2. 只选择需要的列

-- 不推荐
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

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

3. 先过滤再连接

-- 先过滤数据，减少连接的数据量
SELECT u.name, o.product
FROM users u
JOIN (
    SELECT * FROM orders WHERE amount > 1000
) o ON u.id = o.user_id;

4. 使用 EXPLAIN 分析

EXPLAIN SELECT *
FROM users
JOIN orders ON users.id = orders.user_id;

实战示例

示例 1：查询用户订单统计

SELECT 
    u.name AS 用户名,
    u.city AS 城市,
    COUNT(o.id) AS 订单数,
    COALESCE(SUM(o.amount), 0) AS 总消费,
    COALESCE(AVG(o.amount), 0) AS 平均消费
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.city
ORDER BY 总消费 DESC;

示例 2：查询最近订单

SELECT 
    u.name,
    o.product,
    o.amount,
    o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY o.order_date DESC;

示例 3：查询高价值客户

SELECT 
    u.name,
    u.email,
    SUM(o.amount) AS 总消费
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING 总消费 > 5000
ORDER BY 总消费 DESC;

常见错误

错误 1：忘记 ON 条件

-- 错误：缺少 ON 条件
SELECT * FROM users JOIN orders;

-- 正确
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

错误 2：列名歧义

-- 错误：id 列在两个表中都存在
SELECT id, name FROM users JOIN orders ON users.id = orders.user_id;

-- 正确：使用表名或别名限定列名
SELECT users.id, users.name FROM users JOIN orders ON users.id = orders.user_id;

错误 3：混淆 LEFT JOIN 和 INNER JOIN

-- 如果想要所有用户（包括没有订单的），使用 LEFT JOIN
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;

-- 如果只想要有订单的用户，使用 INNER JOIN
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

JOIN 类型对比

JOIN 类型	说明	使用场景
INNER JOIN	返回两表匹配的记录	查询有关联的数据
LEFT JOIN	返回左表所有记录	查询主表及其关联数据
RIGHT JOIN	返回右表所有记录	较少使用，可用 LEFT JOIN 替代
FULL OUTER JOIN	返回两表所有记录	查询所有数据，包括未匹配的
CROSS JOIN	返回笛卡尔积	生成所有可能的组合
SELF JOIN	表与自身连接	处理层级关系

小结

本章介绍了 SQL 中的多表连接：

INNER JOIN：返回两表匹配的记录
LEFT JOIN：返回左表所有记录，右表没有匹配时返回 NULL
RIGHT JOIN：返回右表所有记录，左表没有匹配时返回 NULL
FULL OUTER JOIN：返回两表所有记录
CROSS JOIN：返回笛卡尔积
SELF JOIN：表与自身连接
多表连接：可以连接多个表
性能优化：使用索引、只选择需要的列、先过滤再连接

掌握 JOIN 操作是 SQL 学习的重要里程碑，它让你能够处理复杂的多表查询。

下一步： 学习子查询，学习如何在查询中嵌套查询。

#多表连接

#为什么需要 JOIN？

#数据规范化

#查询关联数据

#准备示例数据

#创建表

#插入数据

#INNER JOIN（内连接）

#基本语法

#示例

#简写形式

#使用表别名

#LEFT JOIN（左连接）

#基本语法

#示例

#查找没有订单的用户

#RIGHT JOIN（右连接）

#基本语法

#示例

#FULL OUTER JOIN（全外连接）

#基本语法

#示例

#CROSS JOIN（交叉连接）

#基本语法

#示例

#SELF JOIN（自连接）

#概念

#示例：员工和经理

#多表连接

#连接三个表

#连接顺序

#连接条件

#等值连接

#非等值连接

#多条件连接

#使用 WHERE 过滤

#在 JOIN 后过滤

#JOIN 条件 vs WHERE 条件

#聚合与 JOIN

#统计每个用户的订单数

#性能优化

#1. 使用索引

#2. 只选择需要的列

#3. 先过滤再连接

#4. 使用 EXPLAIN 分析

#实战示例

#示例 1：查询用户订单统计

#示例 2：查询最近订单

#示例 3：查询高价值客户

#常见错误

#错误 1：忘记 ON 条件

#错误 2：列名歧义

#错误 3：混淆 LEFT JOIN 和 INNER JOIN

#JOIN 类型对比

#小结

多表连接

为什么需要 JOIN？

数据规范化

查询关联数据

准备示例数据

创建表

插入数据

INNER JOIN（内连接）

基本语法

示例

简写形式

使用表别名

LEFT JOIN（左连接）

基本语法

示例

查找没有订单的用户

RIGHT JOIN（右连接）

基本语法

示例

FULL OUTER JOIN（全外连接）

基本语法

示例

CROSS JOIN（交叉连接）

基本语法

示例

SELF JOIN（自连接）

概念

示例：员工和经理

多表连接

连接三个表

连接顺序

连接条件

等值连接

非等值连接

多条件连接

使用 WHERE 过滤

在 JOIN 后过滤

JOIN 条件 vs WHERE 条件

聚合与 JOIN

统计每个用户的订单数

性能优化

1. 使用索引

2. 只选择需要的列

3. 先过滤再连接

4. 使用 EXPLAIN 分析

实战示例

示例 1：查询用户订单统计

示例 2：查询最近订单

示例 3：查询高价值客户

常见错误

错误 1：忘记 ON 条件

错误 2：列名歧义

错误 3：混淆 LEFT JOIN 和 INNER JOIN

JOIN 类型对比

小结