JOIN

导学

现实世界中的数据分散在多张表中。JOIN 是 SQL 中最强大的操作之一，它让你像拼拼图一样把多张表的数据组合起来。但 JOIN 也是新手最容易写出 Bug 的地方——尤其是"忘记写连接条件导致笛卡尔积"。

定义

JOIN：用于根据两个或多个表之间的相关列，将它们的数据行组合在一起的操作。JOIN 是关系型数据库"关系"二字的体现。

完整示例准备：建表与数据

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

-- 考核成绩表
CREATE TABLE scores (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    score DECIMAL(5,2)
);

INSERT INTO scores (emp_id, score) VALUES
(1, 90.00),
(2, 85.00);

完整示例一：INNER JOIN（内连接）

当前数据状态

employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

scores 表：

score_idemp_idscore
1190.00
2285.00

执行 INNER JOIN

SELECT e.emp_id, e.emp_name, s.score AS 考核分
FROM employees e
INNER JOIN scores s ON e.emp_id = s.emp_id;

操作后的结果

emp_idemp_name考核分
1大翔90.00
2白歌85.00

结果解读

  • INNER JOIN 只返回两个表中匹配条件都满足的行
  • 两个员工在 scores 表中都有记录，因此都出现
  • INNER JOIN 的 INNER 关键字可以省略，写成 JOIN 效果相同

完整示例二：LEFT JOIN（左外连接）

当前数据状态

基于上面的 employees 表和 scores 表。

执行 LEFT JOIN

SELECT e.emp_id, e.emp_name, s.score AS 考核分
FROM employees e
LEFT JOIN scores s ON e.emp_id = s.emp_id;

操作后的结果

emp_idemp_name考核分
1大翔90.00
2白歌85.00

结果解读

  • LEFT JOIN 返回左表（employees）所有行，右表中匹配的行显示数据，不匹配的行显示 NULL
  • 本例中两个员工都有考核记录，因此结果与 INNER JOIN 相同
  • 如果某员工没有考核记录，考核分会显示为 NULL

完整示例三：LEFT JOIN 中 ON 与 WHERE 的区别

当前数据状态

基于上面的 employees 表和 scores 表。

错误示范：将右表条件放在 WHERE 中

SELECT e.emp_name, s.score
FROM employees e
LEFT JOIN scores s ON e.emp_id = s.emp_id
WHERE s.score >= 90;

操作后的结果

emp_namescore
大翔90.00

正确示范：将右表条件放在 ON 中

SELECT e.emp_name, s.score
FROM employees e
LEFT JOIN scores s ON e.emp_id = s.emp_id AND s.score >= 90;

操作后的结果

emp_namescore
大翔90.00
白歌NULL

结果解读对比

写法效果
WHERE s.score >= 90先 LEFT JOIN 保留所有员工，再用 WHERE 过滤，成绩低于 90 的行被删除，结果等价于 INNER JOIN
ON ... AND s.score >= 90在连接时就过滤右表数据，左表所有行仍然保留，不匹配的行显示 NULL

核心区别：ON 是在连接时过滤右表数据，不影响左表的返回；WHERE 是在连接完成后过滤整个结果集，会把左表不匹配的行也过滤掉。

完整示例四：RIGHT JOIN（右外连接）

当前数据状态

基于上面的 employees 表和 scores 表。

执行 RIGHT JOIN

SELECT e.emp_id, e.emp_name, s.score
FROM employees e
RIGHT JOIN scores s ON e.emp_id = s.emp_id;

操作后的结果

emp_idemp_namescore
1大翔90.00
2白歌85.00

结果解读

  • RIGHT JOIN 与 LEFT JOIN 对称，返回右表（scores）所有行
  • 由于 scores 表中的 emp_id 都在 employees 表中有匹配，结果与 INNER JOIN 相同
  • 实际开发中极少使用 RIGHT JOIN——因为任何 RIGHT JOIN 都可以改写为 LEFT JOIN，统一用 LEFT JOIN 更符合阅读习惯

完整示例五：自连接（同一张表当两张表用）

当前数据状态

基于上面的 employees 表：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

执行自连接查询

-- 查询每个员工的同部门同事（不包含自己）
SELECT a.emp_name AS 员工, b.emp_name AS 同部门同事, a.dept AS 部门
FROM employees a
INNER JOIN employees b ON a.dept = b.dept AND a.emp_id <> b.emp_id
ORDER BY a.emp_name;

操作后的结果

员工同部门同事部门
白歌大翔技术部
大翔白歌技术部

结果解读

  • 自连接将同一张表视为两个不同的表（别名 a 和 b）
  • a.dept = b.dept 确保是同一部门
  • a.emp_id <> b.emp_id 排除自己与自己配对
  • 大翔和白歌互为同部门同事，因此出现两行

完整示例六：CROSS JOIN（交叉连接 / 笛卡尔积）

当前数据状态

employees 表：

emp_idemp_name
1大翔
2白歌

scores 表：

score_idemp_idscore
1190.00
2285.00

显式 CROSS JOIN

SELECT e.emp_name, s.score
FROM employees e
CROSS JOIN scores s;

操作后的结果

emp_namescore
大翔90.00
大翔85.00
白歌90.00
白歌85.00

隐式写法（逗号连接，危险！）

SELECT e.emp_name, s.score
FROM employees e, scores s;

结果与上面相同。

结果解读

  • CROSS JOIN 返回两个表的笛卡尔积——左表的每一行与右表的每一行组合
  • 2 行 employees × 2 行 scores = 4 行结果
  • 如果忘记写 ON 条件，大数据量下可能瞬间产生海量数据，打爆数据库

完整示例七：忘记写连接条件的灾难

当前数据状态

基于上面的 employees 表和 scores 表。

错误写法

-- 错误：忘记写 ON 条件，产生笛卡尔积
SELECT e.emp_name, s.score
FROM employees e
JOIN scores s;

操作后的结果

会产生 2（employees 行数）× 2（scores 行数）= 4 行无意义数据：

emp_namescore
大翔90.00
大翔85.00
白歌90.00
白歌85.00

正确写法

SELECT e.emp_name, s.score
FROM employees e
JOIN scores s ON e.emp_id = s.emp_id;

结果解读

  • 忘记写 ON 条件会产生笛卡尔积，是生产事故的常见原因
  • 大数据量下，两张百万级表的笛卡尔积会产生万亿行数据，可能导致数据库崩溃
  • 务必养成写 JOIN 时立即写 ON 条件的习惯

完整示例八：用 UNION 模拟 FULL OUTER JOIN

当前数据状态

基于上面的 employees 表和 scores 表。

先插入一条测试数据：

INSERT INTO scores (emp_id, score) VALUES (99, 70.00);

scores 表现在：

score_idemp_idscore
1190.00
2285.00
39970.00

模拟 FULL OUTER JOIN

SELECT e.emp_id, e.emp_name, s.score
FROM employees e
LEFT JOIN scores s ON e.emp_id = s.emp_id

UNION

SELECT e.emp_id, e.emp_name, s.score
FROM employees e
RIGHT JOIN scores s ON e.emp_id = s.emp_id;

操作后的结果

emp_idemp_namescore
1大翔90.00
2白歌85.00
99NULL70.00

结果解读

  • MySQL 5.7 不支持 FULL OUTER JOIN
  • 可用 LEFT JOIN UNION RIGHT JOIN 模拟
  • emp_id=99 的记录在 employees 表中不存在，因此 emp_name 显示为 NULL
  • 大翔和白歌在 employees 表中都有记录，正常显示

常见误区

误区正解
WHERE 过滤条件可以替代 ON对于 INNER JOIN 结果相同，但 LEFT JOIN 中 ON 和 WHERE 行为不同——ON 在连接时过滤，WHERE 在连接后过滤。
LEFT JOIN 后 WHERE 右表.列 = 'x'会把左表不匹配的行过滤掉，结果等价于 INNER JOIN。应把右表条件放在 ON 子句中。
JOIN 越多越慢确实如此，但合理使用索引的 3-5 表 JOIN 在 MySQL 5.7 中完全可接受。

面试考点

Q：INNER JOIN 和 LEFT JOIN 的区别？

INNER JOIN 只返回两表匹配的行；LEFT JOIN 返回左表所有行，右表不匹配的行补 NULL。

Q：ON 和 WHERE 在 LEFT JOIN 中的区别？

ON 是在连接时过滤右表数据，不影响左表的返回；WHERE 是在连接完成后过滤整个结果集，会把左表不匹配的行也过滤掉。

Q：MySQL 如何模拟 FULL OUTER JOIN？

MySQL 5.7 不支持 FULL JOIN。可用 LEFT JOIN UNION RIGHT JOIN 模拟。

小结

  • INNER JOIN 取交集，LEFT JOIN 保留左表全部
  • 忘记写 ON 条件会产生笛卡尔积，是生产事故的常见原因
  • LEFT JOIN 的条件应谨慎放在 ON 还是 WHERE 中
  • 多表 JOIN 时，确保连接列有索引

下一章引子：JOIN 把多张表横向拼接，而子查询则允许你在一条 SQL 中嵌套另一条查询。