深入理解 SQL 的 7 种 JOIN 操作
在 SQL 中，JOIN 是一个非常重要的操作，它用于连接两个或多个表，从而根据条件返回相关数据。不同类型的 JOIN 有不同的连接方式，根据实际需求可以返回不同的数据集。在本文中，我们将通过 学生表（Students） 和 成绩表（Scores） 的例子，深入了解七种常见的 JOIN 操作，并通过 员工表（Employees） 和 部门表（Departments） 的例子讲解 SELF JOIN 和 NATURAL JOIN。
学生表（Students）
| student_id |
name |
department_id |
| 1 |
Alice |
10 |
| 2 |
Bob |
20 |
| 3 |
Charlie |
10 |
| 4 |
David |
NULL |
成绩表（Scores）
| student_id |
score |
| 1 |
85 |
| 2 |
90 |
| 3 |
75 |
| 5 |
80 |
1. INNER JOIN
INNER JOIN 返回两个表中 有匹配的行。如果在一个表中没有匹配的记录，那么这条记录将不会出现在结果中。
SQL 查询
1
2
3
4
| SELECT *
FROM students
INNER JOIN scores
ON students.student_id = scores.student_id;
|
结果
| student_id |
name |
department_id |
student_id |
score |
| 1 |
Alice |
10 |
1 |
85 |
| 2 |
Bob |
20 |
2 |
90 |
| 3 |
Charlie |
10 |
3 |
75 |
- Alice 和 Charlie 的
student_id 都在成绩表中有记录，匹配到成绩。Bob 也有成绩记录。
- David 没有成绩记录，所以没有出现在结果中。
2. LEFT JOIN (LEFT OUTER JOIN)
LEFT JOIN 返回 左表（students） 中的所有行，以及右表（成绩表）中与之匹配的行。如果右表没有匹配的行，右表的列会显示 NULL。
SQL 查询
1
2
3
4
| SELECT *
FROM students
LEFT JOIN scores
ON students.student_id = scores.student_id;
|
结果
| student_id |
name |
department_id |
student_id |
score |
| 1 |
Alice |
10 |
1 |
85 |
| 2 |
Bob |
20 |
2 |
90 |
| 3 |
Charlie |
10 |
3 |
75 |
| 4 |
David |
NULL |
NULL |
NULL |
- David 没有成绩记录，因此他的成绩为
NULL。
- 其他学生的成绩都正常返回。
3. RIGHT JOIN (RIGHT OUTER JOIN)
RIGHT JOIN 与 LEFT JOIN 相似，不过它更关注右表（成绩表），返回右表中的所有行，即使左表没有匹配的行。如果左表没有匹配的行，左表的列会显示 NULL。
SQL 查询
1
2
3
4
| SELECT *
FROM students
RIGHT JOIN scores
ON students.student_id = scores.student_id;
|
结果
| student_id |
name |
department_id |
student_id |
score |
| 1 |
Alice |
10 |
1 |
85 |
| 2 |
Bob |
20 |
2 |
90 |
| 3 |
Charlie |
10 |
3 |
75 |
| NULL |
NULL |
NULL |
5 |
80 |
- 结果中包含了右表（成绩表）中的所有记录。
- NULL 行显示的是成绩表中的一条记录（
student_id = 5），但在学生表中没有对应记录。
4. FULL JOIN (FULL OUTER JOIN)
FULL JOIN 返回 左表 和 右表 中的所有行，即使某一表没有匹配的行。如果没有匹配的行，缺少的部分会显示 NULL。
SQL 查询
1
2
3
4
| SELECT *
FROM students
FULL JOIN scores
ON students.student_id = scores.student_id;
|
结果
| student_id |
name |
department_id |
student_id |
score |
| 1 |
Alice |
10 |
1 |
85 |
| 2 |
Bob |
20 |
2 |
90 |
| 3 |
Charlie |
10 |
3 |
75 |
| 4 |
David |
NULL |
NULL |
NULL |
| NULL |
NULL |
NULL |
5 |
80 |
- David 没有成绩记录，因此他的成绩为
NULL。
- 成绩表中
student_id = 5 的记录没有对应的学生，学生名为 NULL。
5. CROSS JOIN
CROSS JOIN 返回 左表 和 右表 的 笛卡尔积，即每一行左表与每一行右表的所有可能组合。它没有连接条件，所有行都会组合。
SQL 查询
1
2
3
| SELECT *
FROM students
CROSS JOIN scores;
|
结果
| student_id |
name |
department_id |
student_id |
score |
| 1 |
Alice |
10 |
1 |
85 |
| 1 |
Alice |
10 |
2 |
90 |
| 1 |
Alice |
10 |
3 |
75 |
| 1 |
Alice |
10 |
5 |
80 |
| 2 |
Bob |
20 |
1 |
85 |
| 2 |
Bob |
20 |
2 |
90 |
| 2 |
Bob |
20 |
3 |
75 |
| 2 |
Bob |
20 |
5 |
80 |
| 3 |
Charlie |
10 |
1 |
85 |
| 3 |
Charlie |
10 |
2 |
90 |
| 3 |
Charlie |
10 |
3 |
75 |
| 3 |
Charlie |
10 |
5 |
80 |
| 4 |
David |
NULL |
1 |
85 |
| 4 |
David |
NULL |
2 |
90 |
| 4 |
David |
NULL |
3 |
75 |
| 4 |
David |
NULL |
5 |
80 |
- 结果显示了左表（学生表）与右表（成绩表）的每一行组合。
- 总共生成了 4 个学生 × 4 个成绩 = 16 条记录。
6. SELF JOIN
SELF JOIN 是对同一个表进行连接。我们通过为同一表指定不同的别名，来实现表与自身之间的连接。SELF JOIN 适用于查找表中记录之间的关系，例如员工与经理的关系。
假设我们有一个员工表（Employees）：
| emp_id |
name |
manager_id |
| 1 |
Alice |
3 |
| 2 |
Bob |
3 |
| 3 |
Charlie |
NULL |
| 4 |
David |
2 |
我们想要查询每个员工的姓名及其经理的姓名。
SQL 查询
1
2
3
4
| SELECT *
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.emp_id;
|
结果
| emp_id |
name |
manager_id |
emp_id |
name |
manager_id |
| 1 |
Alice |
3 |
3 |
Charlie |
NULL |
| 2 |
Bob |
3 |
3 |
Charlie |
NULL |
| 3 |
Charlie |
NULL |
NULL |
NULL |
NULL |
| 4 |
David |
2 |
2 |
Bob |
3 |
- Alice 和 Bob 的经理是 Charlie，而 David 的经理是 Bob。
7. NATURAL JOIN
NATURAL JOIN 自动根据两个表中 列名相同的列 进行连接。你不需要显式指定连接条件，它会自动查找并使用同名的列。
假设我们有一个员工表（Employees）和部门表（Departments）：
员工表（Employees）：
| emp_id |
name |
department_id |
| 1 |
Alice |
10 |
| 2 |
Bob |
20 |
| 3 |
Charlie |
10 |
| 4 |
David |
NULL |
部门表（Departments）：
| department_id |
department_name |
| 10 |
HR |
| 20 |
IT |
我们希望查询每个员工的姓名及其部门名称。
SQL 查询
1
2
3
| SELECT *
FROM employees
NATURAL JOIN departments;
|
结果
| emp_id |
name |
department_id |
department_name |
| 1 |
Alice |
10 |
HR |
| 2 |
Bob |
20 |
IT |
| 3 |
Charlie |
10 |
HR |
NATURAL JOIN 自动使用 department_id 列进行连接。- 结果中不再重复显示
department_id 列，因为它已经被自动连接并去除。
总结归纳
SQL 提供了多种 JOIN 操作方式，每种方式根据不同的需求连接表格数据。通过我们在 学生表（Students） 和 成绩表（Scores） 的例子，你可以清晰地了解每种 JOIN 类型的特点和用法。
- INNER JOIN：返回匹配的记录。
- LEFT JOIN：返回左表中的所有记录，右表没有匹配的返回
NULL。
- RIGHT JOIN：返回右表中的所有记录，左表没有匹配的返回
NULL。
- FULL JOIN：返回左表和右表中的所有记录，缺少匹配的返回
NULL。
- CROSS JOIN：返回左表和右表的笛卡尔积，所有可能的组合。
- SELF JOIN：一个表与自身进行连接，通常用于查找表中记录之间的关系。
- NATURAL JOIN：根据列名相同的列自动连接表。
