MySQL 进阶专题：笛卡尔积内连接外连接详解（JOIN ON/ OUTER JOIN)
从笛卡尔积到 JOIN 的全面解析
什么是笛卡尔积？
笛卡尔积是 SQL 中两个表在没有任何连接条件的情况下进行组合时的结果。每个表中的行会与另一个表的每一行进行配对，从而生成巨大的数据量。
示例：
假设有两个表：
表 student：
| id | name |
|---|---|
| 1 | 张三 |
| 2 | 李四 |
表 score：
| id | student_id | score |
|---|---|---|
| 1 | 1 | 80 |
| 2 | 1 | 90 |
| 3 | 2 | 70 |
执行如下查询：
SELECT *
FROM student, score;
结果：
| student.id | student.name | score.id | score.student_id | score.score |
|---|---|---|---|---|
| 1 | 张三 | 1 | 1 | 80 |
| 1 | 张三 | 2 | 1 | 90 |
| 1 | 张三 | 3 | 2 | 70 |
| 2 | 李四 | 1 | 1 | 80 |
| 2 | 李四 | 2 | 1 | 90 |
| 2 | 李四 | 3 | 2 | 70 |
这里生成了 student 表和 score 表所有行的笛卡尔积，总共有 6 行。这种结果通常是我们不希望出现的，因为它没有任何逻辑意义。
添加条件过滤笛卡尔积
为了避免笛卡尔积的无意义结果，我们需要添加过滤条件，将关联的表按逻辑连接起来。
示例：
SELECT *
FROM student, score
WHERE student.id = score.student_id;
结果：
| student.id | student.name | score.id | score.student_id | score.score |
|---|---|---|---|---|
| 1 | 张三 | 1 | 1 | 80 |
| 1 | 张三 | 2 | 1 | 90 |
| 2 | 李四 | 3 | 2 | 70 |
通过 WHERE 子句，我们将 student.id 和 score.student_id 进行匹配，过滤掉了无意义的组合。
使用 JOIN 优化连接
虽然使用 WHERE 可以避免笛卡尔积，但在语义上显得不够直观。而 JOIN 提供了一种更清晰的写法，明确表达了表与表之间的关系。
示例：
SELECT *
FROM student
JOIN score ON student.id = score.student_id;
结果与上述相同，但语义更清晰：
| student.id | student.name | score.id | score.student_id | score.score |
|---|---|---|---|---|
| 1 | 张三 | 1 | 1 | 80 |
| 1 | 张三 | 2 | 1 | 90 |
| 2 | 李四 | 3 | 2 | 70 |
内连接 (INNER JOIN)
内连接是最常用的连接类型，用于返回两个表中满足连接条件的记录。
示例：
SELECT student.name, score.score
FROM student
INNER JOIN score ON student.id = score.student_id;
结果：
| name | score |
|---|---|
| 张三 | 80 |
| 张三 | 90 |
| 李四 | 70 |
特点：
- 只返回两个表中满足条件的记录。
- 默认情况下，
JOIN等价于INNER JOIN。
外连接 (OUTER JOIN)
外连接分为左连接 (LEFT JOIN)、右连接 (RIGHT JOIN) 和全连接 (FULL OUTER JOIN)。
左连接 (LEFT JOIN)
左连接返回左表的所有记录，以及右表中满足条件的记录。如果右表中没有匹配项，则对应的列返回 NULL。
示例：
SELECT student.name, score.score
FROM student
LEFT JOIN score ON student.id = score.student_id;
结果：
| name | score |
|---|---|
| 张三 | 80 |
| 张三 | 90 |
| 李四 | 70 |
| 王五 | NULL |
右连接 (RIGHT JOIN)
右连接返回右表的所有记录，以及左表中满足条件的记录。如果左表中没有匹配项，则对应的列返回 NULL。
示例：
SELECT student.name, score.score
FROM student
RIGHT JOIN score ON student.id = score.student_id;
结果：与左连接类似，只是记录方向相反。
结合 GROUP BY 的案例
在实际业务中，除了表连接，我们还经常需要对数据进行分组统计。
示例：
SELECT student.id AS id, student.name, SUM(score.score) AS total_score
FROM student
JOIN score ON student.id = score.student_id
GROUP BY student.id;
结果：
| id | name | total_score |
|---|---|---|
| 1 | 张三 | 170 |
| 2 | 李四 | 70 |
这里我们通过 GROUP BY 对每个学生的成绩进行了汇总统计。
总结
- 笛卡尔积 是表连接的基础，但结果可能不符合实际需求。
- 使用
WHERE可以过滤笛卡尔积，但写法不够直观。 - 使用
JOIN是更推荐的做法，语义清晰且功能强大。 - 内连接用于返回两个表中满足条件的记录。
- 外连接可以返回一个表的所有记录，适用于需要保留部分空值的场景。
- 结合
GROUP BY，可以对连接后的数据进行分组和统计。