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)