MySQL多表查询 #
一、连接概述 #
1.1 为什么需要多表查询 #
text
┌─────────────┐ ┌─────────────┐
│ users │ │ orders │
├─────────────┤ ├─────────────┤
│ id │←────│ user_id │
│ name │ │ id │
│ email │ │ amount │
└─────────────┘ │ order_date │
└─────────────┘
-- 查询用户及其订单信息需要连接两个表
1.2 连接类型 #
| 连接类型 | 说明 |
|---|---|
| INNER JOIN | 内连接，返回匹配的行 |
| LEFT JOIN | 左连接，返回左表所有行 |
| RIGHT JOIN | 右连接，返回右表所有行 |
| CROSS JOIN | 交叉连接，笛卡尔积 |
| NATURAL JOIN | 自然连接，自动匹配同名列 |
1.3 示例表结构 #
sql
-- 用户表
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT
);
INSERT INTO users VALUES
(1, 'John', 1),
(2, 'Jane', 1),
(3, 'Bob', 2),
(4, 'Alice', NULL);
-- 部门表
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO departments VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Sales');
-- 订单表
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
order_date DATE
);
INSERT INTO orders VALUES
(1, 1, 100.00, '2024-01-15'),
(2, 1, 200.00, '2024-01-16'),
(3, 2, 150.00, '2024-01-17'),
(4, 5, 300.00, '2024-01-18');
二、内连接（INNER JOIN） #
2.1 基本语法 #
sql
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
-- INNER可以省略
SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column;
2.2 基本示例 #
sql
-- 查询用户及其部门
SELECT u.name, d.name AS department
FROM users u
INNER JOIN departments d ON u.department_id = d.id;
+------+-------------+
| name | department |
+------+-------------+
| John | Engineering |
| Jane | Engineering |
| Bob | Marketing |
+------+-------------+
-- 注意：Alice没有部门，不会出现在结果中
2.3 多表内连接 #
sql
-- 三表连接
SELECT u.name, d.name AS department, o.amount
FROM users u
INNER JOIN departments d ON u.department_id = d.id
INNER JOIN orders o ON u.id = o.user_id;
+------+-------------+--------+
| name | department | amount |
+------+-------------+--------+
| John | Engineering | 100.00 |
| John | Engineering | 200.00 |
| Jane | Engineering | 150.00 |
+------+-------------+--------+
2.4 使用USING #
sql
-- 当连接列名相同时，可以使用USING
SELECT u.name, d.name AS department
FROM users u
INNER JOIN departments d USING (id);
-- 等价于
SELECT u.name, d.name AS department
FROM users u
INNER JOIN departments d ON u.id = d.id;
三、左连接（LEFT JOIN） #
3.1 基本语法 #
sql
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
3.2 基本示例 #
sql
-- 查询所有用户及其部门（包括没有部门的用户）
SELECT u.name, d.name AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;
+-------+-------------+
| name | department |
+-------+-------------+
| John | Engineering |
| Jane | Engineering |
| Bob | Marketing |
| Alice | NULL |
+-------+-------------+
-- Alice没有部门，但仍然出现在结果中
3.3 查找不匹配的行 #
sql
-- 查找没有部门的用户
SELECT u.name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
WHERE d.id IS NULL;
+-------+
| name |
+-------+
| Alice |
+-------+
3.4 左连接与COUNT #
sql
-- 统计每个部门的用户数（包括没有用户的部门）
SELECT d.name, COUNT(u.id) AS user_count
FROM departments d
LEFT JOIN users u ON d.id = u.department_id
GROUP BY d.id;
-- 注意：使用COUNT(u.id)而不是COUNT(*)，避免NULL被计数
四、右连接（RIGHT JOIN） #
4.1 基本语法 #
sql
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
4.2 基本示例 #
sql
-- 查询所有部门及其用户（包括没有用户的部门）
SELECT u.name, d.name AS department
FROM users u
RIGHT JOIN departments d ON u.department_id = d.id;
+------+-------------+
| name | department |
+------+-------------+
| John | Engineering |
| Jane | Engineering |
| Bob | Marketing |
| NULL | Sales |
+------+-------------+
-- Sales部门没有用户，但仍然出现在结果中
4.3 右连接转左连接 #
sql
-- 右连接可以转换为左连接（推荐使用左连接）
SELECT u.name, d.name AS department
FROM departments d
LEFT JOIN users u ON d.id = u.department_id;
五、全外连接 #
5.1 MySQL实现全外连接 #
sql
-- MySQL不直接支持FULL OUTER JOIN
-- 使用UNION实现
SELECT u.name, d.name AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
UNION
SELECT u.name, d.name AS department
FROM users u
RIGHT JOIN departments d ON u.department_id = d.id;
+-------+-------------+
| name | department |
+-------+-------------+
| John | Engineering |
| Jane | Engineering |
| Bob | Marketing |
| Alice | NULL |
| NULL | Sales |
+-------+-------------+
六、交叉连接（CROSS JOIN） #
6.1 基本语法 #
sql
SELECT columns
FROM table1
CROSS JOIN table2;
-- 等价于
SELECT columns
FROM table1, table2;
6.2 基本示例 #
sql
-- 生成所有组合
SELECT u.name AS user, d.name AS department
FROM users u
CROSS JOIN departments d;
-- 4个用户 × 3个部门 = 12行结果
+-------+-------------+
| user | department |
+-------+-------------+
| John | Engineering |
| John | Marketing |
| John | Sales |
| Jane | Engineering |
| ... | ... |
+-------+-------------+
6.3 使用场景 #
sql
-- 生成日期和产品的所有组合
SELECT d.date, p.product_name
FROM dates d
CROSS JOIN products p;
-- 生成报表模板
七、自连接 #
7.1 基本概念 #
sql
-- 自连接：表与自身连接
-- 需要使用别名区分
7.2 示例：员工与经理 #
sql
-- 员工表（包含经理ID）
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);
INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'Manager1', 1),
(3, 'Manager2', 1),
(4, 'Employee1', 2),
(5, 'Employee2', 3);
-- 查询员工及其经理
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
+-----------+----------+
| employee | manager |
+-----------+----------+
| CEO | NULL |
| Manager1 | CEO |
| Manager2 | CEO |
| Employee1 | Manager1 |
| Employee2 | Manager2 |
+-----------+----------+
7.3 示例：查找同一部门的用户 #
sql
-- 查找同一部门的用户对
SELECT u1.name, u2.name, u1.department_id
FROM users u1
JOIN users u2 ON u1.department_id = u2.department_id
WHERE u1.id < u2.id;
八、自然连接（NATURAL JOIN） #
8.1 基本语法 #
sql
SELECT columns
FROM table1
NATURAL JOIN table2;
8.2 基本示例 #
sql
-- 自动匹配同名列
SELECT * FROM users
NATURAL JOIN departments;
-- 等价于
SELECT * FROM users
INNER JOIN departments USING (id);
8.3 注意事项 #
sql
-- 不推荐使用NATURAL JOIN
-- 原因：
-- 1. 不明确连接条件
-- 2. 如果表结构变化可能导致意外结果
-- 3. 难以调试和维护
-- 推荐：明确指定连接条件
SELECT * FROM users u
INNER JOIN departments d ON u.department_id = d.id;
九、连接条件详解 #
9.1 ON与WHERE区别 #
sql
-- ON：连接条件，决定如何连接
-- WHERE：过滤条件，在连接后过滤
-- 左连接中使用ON
SELECT u.name, d.name AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id AND d.name = 'Engineering';
-- 左连接中使用WHERE
SELECT u.name, d.name AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
WHERE d.name = 'Engineering';
-- 区别：
-- ON中的条件不会过滤左表
-- WHERE中的条件会过滤结果
9.2 复杂连接条件 #
sql
-- 多条件连接
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
AND o.order_date >= '2024-01-01'
AND o.amount > 100;
-- 使用函数
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
AND YEAR(o.order_date) = 2024;
十、连接性能优化 #
10.1 索引优化 #
sql
-- 确保连接列有索引
CREATE INDEX idx_users_department ON users(department_id);
CREATE INDEX idx_orders_user ON orders(user_id);
-- 查看执行计划
EXPLAIN SELECT u.name, d.name
FROM users u
JOIN departments d ON u.department_id = d.id;
10.2 小表驱动大表 #
sql
-- 让小表作为驱动表
-- MySQL优化器通常会自动选择
-- 手动指定（使用STRAIGHT_JOIN）
SELECT STRAIGHT_JOIN u.name, d.name
FROM small_table u
JOIN large_table d ON u.id = d.user_id;
10.3 避免笛卡尔积 #
sql
-- 错误：忘记连接条件
SELECT * FROM users, departments; -- 笛卡尔积
-- 正确：添加连接条件
SELECT * FROM users u
JOIN departments d ON u.department_id = d.id;
十一、复杂连接示例 #
11.1 多表连接查询 #
sql
-- 查询用户、部门、订单信息
SELECT
u.name AS user_name,
d.name AS department_name,
o.amount,
o.order_date
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100 OR o.amount IS NULL;
11.2 分组统计 #
sql
-- 统计每个部门的订单总额
SELECT
d.name AS department,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_amount
FROM departments d
LEFT JOIN users u ON d.id = u.department_id
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY d.id
ORDER BY total_amount DESC;
11.3 层级查询 #
sql
-- 使用自连接查询组织架构
SELECT
e.name AS employee,
m.name AS manager,
mm.name AS manager_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees mm ON m.manager_id = mm.id;
十二、总结 #
连接类型对比：
| 连接类型 | 说明 | 结果 |
|---|---|---|
| INNER JOIN | 内连接 | 只返回匹配行 |
| LEFT JOIN | 左连接 | 返回左表所有行 |
| RIGHT JOIN | 右连接 | 返回右表所有行 |
| CROSS JOIN | 交叉连接 | 笛卡尔积 |
| SELF JOIN | 自连接 | 表与自身连接 |
最佳实践：
- 优先使用LEFT JOIN，语义更清晰
- 确保连接列有索引
- 明确指定连接条件，避免NATURAL JOIN
- 注意ON和WHERE的区别
- 使用EXPLAIN分析查询计划
下一步，让我们学习索引！
最后更新：2026-03-26