SQL 7种链接方式

概要

1. 内连接: 查询两张表中共有的部分

Select <select_list>
From TableA A
Inner join TableB b
ON A.Key = B.Key

3. 左连接:把左表的内容全查出来, 右表只查满足条件的记录

Select <select_list>
From TableA A
Left Join TableB B
ON A.Key = B.Key

5. 右连接: 把右表的内容全部查出来, 左边只查满足条件的记录

Select <select_list>
From TableA A
Right join TableB B
On A.key = B.key

6. 查询左边独有数据

Select <select_list>
from TableA A
left join TableB B
ON A.key = B.key
Where B.key is null

8. 查询右表独有数据

Select <select_list>
From tableA A 
right join TableB B
ON A.key = B.key
where B.key is null

10. 全连接: 查询两个表的全部信息(MySQL 不支持 full outer)

//(MySQL 不支持 full outer)
Select <select_list>
from TableA A
Full outer Join TableB 
ON A.Key = B.key

Select <select_list>
From TableA A
Left Join TableB B
ON A.Key = B.Key
union
Select <select_list>
From TableA A
Right join TableB B
On A.key = B.key

11. 查询左右表各自的独有数据

//(MySQL 不支持 full outer)
Select <select_list>
From TableA 
Full Outer Join TableB B
ON A.key = B.key
Where A.key is nullor B.key is null 

Select <select_list>
from TableA A
left join TableB B
ON A.key = B.key
Where B.key is null
union
Select <select_list>
From tableA A 
right join TableB B
ON A.key = B.key
where B.key is null