SQL JOIN 连接
JOIN 用于将多个表的数据组合在一起。
为什么要使用 JOIN？
将数据拆分到多个表中是数据库设计的基本原则，JOIN 让我们可以把这些数据重新组合起来。
演示表
users 表
| id | name | age | |
|---|---|---|---|
| 1 | 张三 | zhangsan@email.com | 25 |
| 2 | 李四 | lisi@email.com | 30 |
| 3 | 王五 | wangwu@email.com | 28 |
orders 表
| id | user_id | product | amount |
|---|---|---|---|
| 1 | 1 | Phone | 5000 |
| 2 | 1 | Laptop | 8000 |
| 3 | 2 | Tablet | 3000 |
| 4 | 4 | Keyboard | 200 |
JOIN 类型
1. INNER JOIN（内连接）
返回两个表中匹配的记录。
-- 基本语法
SELECT 列名
FROM 表1
INNER JOIN 表2 ON 连接条件;
-- 简写（INNER 可省略）
SELECT *
FROM users
INNER JOIN orders ON users.id = orders.user_id;
结果：
| id | name | age | order_id | user_id | product | amount | |
|---|---|---|---|---|---|---|---|
| 1 | 张三 | zhangsan@email.com | 25 | 1 | 1 | Phone | 5000 |
| 1 | 张三 | zhangsan@email.com | 25 | 2 | 1 | Laptop | 8000 |
| 2 | 李四 | lisi@email.com | 30 | 3 | 2 | Tablet | 3000 |
注意：user_id=4 的订单和 id=3 的用户（王五）没有出现在结果中，因为没有匹配的记录。
2. LEFT JOIN（左连接）
返回左表的所有记录，右表没有匹配的显示 NULL。
SELECT users.id, users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
结果：
| id | name | product | amount |
|---|---|---|---|
| 1 | 张三 | Phone | 5000 |
| 1 | 张三 | Laptop | 8000 |
| 2 | 李四 | Tablet | 3000 |
| 3 | 王五 | NULL | NULL |
注意：王五没有订单，但仍然显示在结果中。
3. RIGHT JOIN（右连接）
返回右表的所有记录，左表没有匹配的显示 NULL。
SELECT users.id, users.name, orders.product, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
结果：
| id | name | product | amount |
|---|---|---|---|
| 1 | 张三 | Phone | 5000 |
| 1 | 张三 | Laptop | 8000 |
| 2 | 李四 | Tablet | 3000 |
| NULL | NULL | Keyboard | 200 |
注意：user_id=4 的订单没有匹配的用户。
4. FULL OUTER JOIN（完全连接）
返回两个表的所有记录，没有匹配的显示 NULL。
-- MySQL 不支持 FULL OUTER JOIN，可使用 UNION 模拟
SELECT users.id, users.name, orders.product, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.id, users.name, orders.product, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
5. CROSS JOIN（交叉连接）
返回两个表的笛卡尔积。
SELECT users.name, orders.product
FROM users
CROSS JOIN orders;
结果：每个用户都会与每个订单组合。
6. SELF JOIN（自连接）
表与自身连接。
-- 查询与张三同部门的员工
SELECT e2.name
FROM employees e1
JOIN employees e2 ON e1.department = e2.department
WHERE e1.name = '张三';
多表 JOIN
SELECT
users.name,
orders.product,
products.category
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id;
JOIN ... USING
当两个表有相同列名时，可以使用 USING。
-- 简化写法
SELECT *
FROM users
JOIN orders USING (user_id);
-- 等价于
SELECT *
FROM users
JOIN orders ON users.id = orders.user_id;
常见错误
1. 忘记 ON 条件
-- 错误
SELECT * FROM users, orders;
-- 正确
SELECT * FROM users JOIN orders ON users.id = orders.user_id;
2. 笛卡尔积
没有正确关联的 JOIN 会产生笛卡尔积，导致数据量爆炸。
-- 危险！笛卡尔积
SELECT COUNT(*) FROM table1 CROSS JOIN table2;
-- 正确
SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id;
小结
本章我们学习了：
- INNER JOIN：内连接
- LEFT JOIN：左连接
- RIGHT JOIN：右连接
- FULL OUTER JOIN：完全连接
- CROSS JOIN：交叉连接
- SELF JOIN：自连接
练习
- 查询所有用户的订单（包含没有订单的用户）
- 查询有订单的用户
- 查询所有订单对应的用户（包含没有用户的订单）
- 实现三表 JOIN 查询