SQL JOINS 的几种类型
SQL JOINS
Relation algebra
Natural join (⋈)
The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names. For an example consider the tables Employee and Dept and their natural join:
自然连接是比较常见的，其结果就是依靠公共属性相同连接在一块的R和S中所有元组组成的集合。如下实例：
Employee
| Name | EmpId | DeptName |
|---|---|---|
| Harry | 3415 | Finance |
| Sally | 2241 | Sales |
| George | 3401 | Finance |
| Harriet | 2202 | Sales |
| Mary | 1257 | Human Resources |
Dept
| DeptName | Manager |
|---|---|
| Finance | George |
| Sales | Harriet |
| Production | Charles |
公共属性是部门（deptartment），两个表就是用这个属性进行连接。
参与连接条件的元组内容是（Finace和Sales）
最后将Employee和Dept两个表整个，公共属性合并，形成一个新的表。设计的元组内容也按照上述方法排列。
得到如下Nature Join结果：
**Employee ⋈ Dept **
| Name | EmpId | DeptName | Manager |
|---|---|---|---|
| Harry | 3415 | Finance | George |
| Sally | 2241 | Sales | Harriet |
| George | 3401 | Finance | George |
| Harriet | 2202 | Sales | Harriet |
θ-join(⋈θ) and equijoin
比自然连接情况更近一步，有时候我们连接两个表所需的属性还是和自然连接一样，但是数据组成元组的条件相较于自然连接好多一写，比方说>，<，≥，≤。这个时候连接方式就是⋈θ。所以对于这一运算的模拟如下：
R ⋈θ S = σθ(R × S)首先算笛卡尔积，然后在其中取符合条件的元组。
equijion也就是这其中θ取=的时候了。
Car
| CarModel | CarPrice |
|---|---|
| CarA | 20,000 |
| CarB | 30,000 |
| CarC | 50,000 |
Boat
| BoatModel | BoatPrice |
|---|---|
| Boat1 | 10,000 |
| Boat2 | 40,000 |
| Boat3 | 60,000 |
Car ⋈ Boat CarPrice ≥ BoatPrice
| CarModel | CarPrice | BoatModel | BoatPrice |
|---|---|---|---|
| CarA | 20,000 | Boat1 | 10,000 |
| CarB | 30,000 | Boat1 | 10,000 |
| CarC | 50,000 | Boat1 | 10,000 |
| CarC | 50,000 | Boat2 | 40,000 |
Semijoin (⋉)(⋊)
left semijoin写作⋉，和自然连接的区别就是，结果的元组中只有左关系表中的属性，不出现右表中的属性。
常用EXISTS实现
select * from table_a where exists(select * from table_b where table_a = table_b);
Employee
| Name | EmpId | DeptName |
|---|---|---|
| Harry | 3415 | Finance |
| Sally | 2241 | Sales |
| George | 3401 | Finance |
| Harriet | 2202 | Production |
Dept
| DeptName | Manager |
|---|---|
| Sales | Sally |
| Production | Harriet |
left semijoin就是在自然连接的基础上，刨除了Dept的内容。
Employee ⋉ Dept
| Name | EmpId | DeptName |
|---|---|---|
| Sally | 2241 | Sales |
| Harriet | 2202 | Production |
Antijoin (▷)
antijoin和semijoin很像，区别在于连接时候，semijoin是结果中公共属性相等的元组。而antijioin指的是结果中公共属性不同的元组。
常用NOT EXISTS实现
select * from table_a where not exists(select * from table_b where table_a = table_b);
Employee
| Name | EmpId | DeptName |
|---|---|---|
| Harry | 3415 | Finance |
| Sally | 2241 | Sales |
| George | 3401 | Finance |
| Harriet | 2202 | Production |
Dept
| DeptName | Manager |
|---|---|
| Sales | Sally |
| Production | Harriet |
left antijoin就是在自然连接的基础上，刨除了右侧Dept的属性内容。同时选取了公共属性不相同的元组作为最终结果。
Employee ▷ Dept
| Name | EmpId | DeptName |
|---|---|---|
| Harry | 3415 | Finance |
| George | 3401 | Finance |
Division (÷)
division一般不能直接通过SQL语句写出来。division的header结果为仅在左侧关系中出现的属性。假设参与运算的关系为R ÷ S
也就是在R中筛选出能够同时包含（R与S公共部分数据全集的那些元组），并将这些元组中只属于R的那些属性予以显示。
Completed
| Student | Task |
|---|---|
| Fred | Database1 |
| Fred | Database2 |
| Fred | Compiler1 |
| Eugene | Database1 |
| Eugene | Compiler1 |
| Sarah | Database1 |
| Dante | Database2 |
DBProject
| Task |
|---|
| Database1 |
| Database2 |
上面可以看出S的属性仅有Task，元组为Database1和Database2。下面将列出符合第一次筛选的结果
| Student | Task |
|---|---|
| Fred | Database1 |
| Fred | Database2 |
| Student | Task |
|---|---|
| Eugene | Database1 |
| Student | Task |
|---|---|
| Sarah | Database1 |
| Student | Task |
|---|---|
| Dante | Database2 |
这里面仅有Fred符合全部包含公共属性Task(Database1、Database2)，所以最后R÷S的结果就是Fred。
Completeed ÷ DBProject
| Student |
|---|
| Fred |
-
第一步：找出关系R和关系S中相同的属性，即Y属性。在关系S中对Y做投影（即将Y列取出）；
-
第二步：被除关系R中与S中不相同的属性列是X ，关系R在属性（X）上做取消重复值的投影；
-
第三步：求关系R中X属性对应的像集Y ；
-
第四步：判断包含关系；
-
第五步：根据符合的包含关系去除属性列X的值。
CROSS JOIN
对集合进行笛卡尔积。
SELECT
A.a AS A_a,
A.b A_b,
B.a AS B_a,
B.b B_b
FROM
table_a A CROSS JOIN table_b B ;
a_a | a_b | b_a | b_b
-----+-----+-----+-----
1 | 123 | 3 | 345
1 | 123 | 2 | 234
1 | 123 | 1 | 123
1 | 123 | 7 | 789
1 | 123 | 8 | 890
1 | 123 | 9 | 999
2 | 234 | 3 | 345
2 | 234 | 2 | 234
2 | 234 | 1 | 123
2 | 234 | 7 | 789
2 | 234 | 8 | 890
2 | 234 | 9 | 999
3 | 345 | 3 | 345
3 | 345 | 2 | 234
3 | 345 | 1 | 123
3 | 345 | 7 | 789
3 | 345 | 8 | 890
3 | 345 | 9 | 999
4 | 456 | 3 | 345
4 | 456 | 2 | 234
4 | 456 | 1 | 123
4 | 456 | 7 | 789
4 | 456 | 8 | 890
4 | 456 | 9 | 999
5 | 567 | 3 | 345
5 | 567 | 2 | 234
5 | 567 | 1 | 123
5 | 567 | 7 | 789
5 | 567 | 8 | 890
5 | 567 | 9 | 999
6 | 678 | 3 | 345
6 | 678 | 2 | 234
6 | 678 | 1 | 123
6 | 678 | 7 | 789
6 | 678 | 8 | 890
6 | 678 | 9 | 999
(36 rows)
INNER JOIN(JOIN)
内连接即两个集合的交集。
SELECT
A.a AS A_a,
A.b A_b,
B.a AS B_a,
B.b B_b
FROM
table_a A INNER JOIN table_b B
ON A.a = B.a;
a_a | a_b | b_a | b_b
-----+-----+-----+-----
1 | 123 | 1 | 123
2 | 234 | 2 | 234
3 | 345 | 3 | 345
(3 rows)
LEFT JOIN（LEFT OUTER JOIN)
左连接是左边表的所有数据都显示出来，右边的只显示共有的部分。
SELECT
A.a AS A_a,
A.b A_b,
B.a AS B_a,
B.b B_b
FROM
table_a A LEFT JOIN table_b B
ON
A.a = B.a;
a_a | a_b | b_a | b_b
-----+-----+-----+-----
1 | 123 | 1 | 123
2 | 234 | 2 | 234
3 | 345 | 3 | 345
4 | 456 | |
5 | 567 | |
6 | 678 | |
(6 rows)
RIGHT JION (RIGHT OUTER JOIN)
右连接是右边表的所有数据都显示出来，左边的只显示共有的部分。
SELECT
A.a AS A_a,
A.b A_b,
B.a AS B_a,
B.b B_b
FROM
table_a A RIGHT JOIN table_b B
ON
A.a = B.a;
a_a | a_b | b_a | b_b
-----+-----+-----+-----
3 | 345 | 3 | 345
2 | 234 | 2 | 234
1 | 123 | 1 | 123
| | 7 | 789
| | 8 | 890
| | 9 | 999
(6 rows)
FULL JOIN （FULL OUTER JOIN）
全连接就是指的是两个集合取并集。
SELECT
A.a AS A_a,
A.b A_b,
B.a AS B_a,
B.b B_b
FROM
table_a A FULL OUTER JOIN table_b B
ON
A.a = B.a;
a_a | a_b | b_a | b_b
-----+-----+-----+-----
1 | 123 | 1 | 123
2 | 234 | 2 | 234
3 | 345 | 3 | 345
4 | 456 | |
5 | 567 | |
6 | 678 | |
| | 8 | 890
| | 9 | 999
| | 7 | 789
(9 rows)
