在SQL中使用JOIN子句，可以通过指定的关联条件，将两个或多个表的记录结合起来…
Joins又可以分为多个关键字(括号中的OUTER是可省略的)，每个关键字都是关联两张表，但是结果有些不同。
INNER JOIN（可以简化为JOIN）LEFT(OUTER) JOINRIGHT(OUTER) JOINFULL(OUTTER) JOINCROSS JOIN
首先考虑有下边两张表
employee 表
1
2
3
4
5
6
7
8
9
10
|
┌─────────┬──────────────┬───────────────┐
│ (index) │ LastName │ DepartmentID │
├─────────┼──────────────┼───────────────┤
│ 0 │ 'Rafferty' │ 31 │
│ 1 │ 'Jones' │ 33 │
│ 2 │ 'Heisenberg' │ 33 │
│ 3 │ 'Robinson' │ 34 │
│ 4 │ 'Smith' │ 34 │
│ 5 │ 'Williams' │ NULL │
└─────────┴──────────────┴───────────────┘
|
department 表
1
2
3
4
5
6
7
8
|
┌─────────┬───────────────┬─────────────────┐
│ (index) │ DepartmentID │ DepartmentName │
├─────────┼───────────────┼─────────────────┤
│ 0 │ 31 │ 'Sales' │
│ 1 │ 33 │ 'Engineering' │
│ 2 │ 34 │ 'Clerical' │
│ 3 │ 35 │ 'Marketing' │
└─────────┴───────────────┴─────────────────┘
|
其中 department.DepartmentID 是 department 表的主键, employee.DepartmentID 是外键。
INNER JOIN
INNER JOIN 会通过JOIN的条件将两个表（A和B）的列值结合起来创建一个新的表，具体的方式是将A的每条记录与B的每条记录进行比较，以找到所有满足JOIN条件的记录对，这里找到的都是非NULL的值，也就是说最后的记录对都是非NULL的，就像取两个集合的交集
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
┌─────────┬───────────────────┬───────────────────────┬───────────────────────────┐
│ (index) │ Employee.LastName │ Employed.DepartmentID │ Department.DepartmentName │
├─────────┼───────────────────┼───────────────────────┼───────────────────────────┤
│ 0 │ 'Robinson' │ 34 │ 'Clerical' │
│ 1 │ 'Jones' │ 33 │ 'Engineering' │
│ 2 │ 'Smith' │ 34 │ 'Clerical' │
│ 3 │ 'Heisenberg' │ 33 │ 'Engineering' │
│ 4 │ 'Rafferty' │ 31 │ 'Sales' │
└─────────┴───────────────────┴───────────────────────┴───────────────────────────┘
|
在查询的结果中可以看到没有 'Williams' 因为他在department表中没有对应的记录（DepartmentID is NULL）。
LEFT JOIN
表A和表B的LEFT JOIN结果总是包含"左表”（A）的所有记录和"右表”（B）的匹配记录，即使JOIN条件在"右表”（B）中没有找到任何匹配的记录。也就是说对于"左表”（A）中的某条记录，如果通过ON子句在B中匹配了到零条记录，A中的记录还是会被放到结果中，只不过对应的B的每一列都是NULL。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT * FROM employee
LEFT JOIN department ON employee.DepartmentID = department.DepartmentID;
┌─────────┬───────────────────┬───────────────────────┬───────────────────────────┬─────────────────────────┐
│ (index) │ Employee.LastName │ Employee.DepartmentID │ Department.DepartmentName │ Department.DepartmentID │
├─────────┼───────────────────┼───────────────────────┼───────────────────────────┼─────────────────────────┤
│ 0 │ 'Jones' │ 33 │ 'Engineering' │ 33 │
│ 1 │ 'Rafferty' │ 31 │ 'Sales' │ 31 │
│ 2 │ 'Robinson' │ 34 │ 'Clerical' │ 34 │
│ 3 │ 'Smith' │ 34 │ 'Clerical' │ 34 │
│ 4 │ 'Williams' │ NULL │ NULL │ NULL │
│ 5 │ 'Heisenberg' │ 33 │ 'Engineering' │ 33 │
└─────────┴───────────────────┴───────────────────────┴───────────────────────────┴─────────────────────────┘
|
RIGHT JOIN
和LEFT JOIN原理类似，只不过是反过来，返回"右表”（B）的所有记录和"左表”（A）的匹配记录
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT * FROM employee
RIGHT JOIN department ON employee.DepartmentID = department.DepartmentID;
┌─────────┬───────────────────┬───────────────────────┬───────────────────────────┬─────────────────────────┐
│ (index) │ Employee.LastName │ Employee.DepartmentID │ Department.DepartmentName │ Department.DepartmentID │
├─────────┼───────────────────┼───────────────────────┼───────────────────────────┼─────────────────────────┤
│ 0 │ 'Smith' │ 34 │ 'Clerical' │ 34 │
│ 1 │ 'Jones' │ 33 │ 'Engineering' │ 33 │
│ 2 │ 'Robinson' │ 34 │ 'Clerical' │ 34 │
│ 3 │ 'Heisenberg' │ 33 │ 'Engineering' │ 33 │
│ 4 │ 'Rafferty' │ 31 │ 'Sales' │ 31 │
│ 5 │ null │ null │ 'Marketing' │ 35 │
└─────────┴───────────────────┴───────────────────────┴───────────────────────────┴─────────────────────────┘
|
FULL JOIN
FULL JOIN通过合并LEFT JOIN和RIGHT JOIN的结果创建结果集，结果集将包含两个表中的所有记录，对于那些没有匹配的记录，结果集将包含NULL值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT * FROM employee
FULL JOIN department ON employee.DepartmentID = department.DepartmentID;
┌─────────┬───────────────────┬───────────────────────┬───────────────────────────┬─────────────────────────┐
│ (index) │ Employee.LastName │ Employee.DepartmentID │ Department.DepartmentName │ Department.DepartmentID │
├─────────┼───────────────────┼───────────────────────┼───────────────────────────┼─────────────────────────┤
│ 0 │ 'Smith' │ 34 │ 'Clerical' │ 34 │
│ 1 │ 'Jones' │ 33 │ 'Engineering' │ 33 │
│ 2 │ 'Robinson' │ 34 │ 'Clerical' │ 34 │
│ 3 │ 'Williams' │ NULL │ NULL │ NULL │
│ 4 │ 'Heisenberg' │ 33 │ 'Engineering' │ 33 │
│ 5 │ 'Rafferty' │ 31 │ 'Sales' │ 31 │
│ 6 │ NULL │ NULL │ 'Marketing' │ 35 │
└─────────┴───────────────────┴───────────────────────┴───────────────────────────┴─────────────────────────┘
|
CROSS JOIN
CROSS JOIN 返回两个表的行的笛卡尔乘积，也就是它将产生第一张表的每一条记录与第二张表的每一条记录的组合。它有多种实现方式
- 显式
CROSS JOIN
1
|
SELECT * FROM employee CROSS JOIN department;
|
- 隐式
CROSS JOIN
1
|
SELECT * FROM employee, department;
|
- 通过给
INNER JOIN 一个总是为true的条件来实现
1
|
SELECT * FROM employee INNER JOIN department ON 1=1;
|
结果如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
┌─────────┬───────────────────┬───────────────────────┬───────────────────────────┬─────────────────────────┐
│ (index) │ Employee.LastName │ Employee.DepartmentId │ Department.DepartmentName │ Department.DepartmentID │
├─────────┼───────────────────┼───────────────────────┼───────────────────────────┼─────────────────────────┤
│ 0 │ 'Rafferty' │ 31 │ 'Sales' │ 31 │
│ 1 │ 'Jones' │ 33 │ 'Sales' │ 31 │
│ 2 │ 'Heisenberg' │ 33 │ 'Sales' │ 31 │
│ 3 │ 'Smith' │ 34 │ 'Sales' │ 31 │
│ 4 │ 'Robinson' │ 34 │ 'Sales' │ 31 │
│ 5 │ 'Williams' │ null │ 'Sales' │ 31 │
│ 6 │ 'Rafferty' │ 31 │ 'Engineering' │ 33 │
│ 7 │ 'Jones' │ 33 │ 'Engineering' │ 33 │
│ 8 │ 'Heisenberg' │ 33 │ 'Engineering' │ 33 │
│ 9 │ 'Smith' │ 34 │ 'Engineering' │ 33 │
│ 10 │ 'Robinson' │ 34 │ 'Engineering' │ 33 │
│ 11 │ 'Williams' │ null │ 'Engineering' │ 33 │
│ 12 │ 'Rafferty' │ 31 │ 'Clerical' │ 34 │
│ 13 │ 'Jones' │ 33 │ 'Clerical' │ 34 │
│ 14 │ 'Heisenberg' │ 33 │ 'Clerical' │ 34 │
│ 15 │ 'Smith' │ 34 │ 'Clerical' │ 34 │
│ 16 │ 'Robinson' │ 34 │ 'Clerical' │ 34 │
│ 17 │ 'Williams' │ null │ 'Clerical' │ 34 │
│ 18 │ 'Rafferty' │ 31 │ 'Marketing' │ 35 │
│ 19 │ 'Jones' │ 33 │ 'Marketing' │ 35 │
│ 20 │ 'Heisenberg' │ 33 │ 'Marketing' │ 35 │
│ 21 │ 'Smith' │ 34 │ 'Marketing' │ 35 │
│ 22 │ 'Robinson' │ 34 │ 'Marketing' │ 35 │
│ 23 │ 'Williams' │ null │ 'Marketing' │ 35 │
└─────────┴───────────────────┴───────────────────────┴───────────────────────────┴─────────────────────────┘
|
文章作者
杂毛小道
上次更新
2021-06-14
许可协议
署名 4.0 国际 (CC BY 4.0)
