SQL连接与联合详解-优快云博客

SQL语句中的三类表连接和两类联合

在SQL语句中有很多连接，大致可分为一下三种连接:

内连接：join，inner join
外连接：left join，left outer join，right join，right outer join，union
交叉连接：cross join

还有两种联合:union和union all

下面以实例进行分析

在数据库中新建两个表A和B：
表A

表B

1.内连接

inner join或者join

SELECT a.*,b.*
FROM A a INNER JOIN B b
ON a.id = b.id

结果:
内连接结果
应用场景:

满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

2.外连接

外连接有六种场景:

2.1.left join 或者left outer join

select a.*, b.* 
from A a left join B b  
on a.id = b.id

结果如下，B中不存在的记录填充Null
左连接结果
应用场景:

该场景得到的是:A的所有数据，和满足某一条件的B的数据;

2.2.[left join 或者left outer join] + [where B.column is null]

select a.id aid,a.age,b.id bid,b.grade 
from A a  
left join B b  
on a.id = b.id  
Where B.id is null

结果如下:
左左连接结果
应用场景：

该场景得到的是A中的所有数据减去"与B满足同一条件的数据"，然后得到的A剩余数据；

2.3.right join 或者fight outer join

select a.id aid,a.age,b.id bid,b.grade 
from A a  
right join B b  
on a.id = b.id

结果如下。A中不存在的记录填充Null：
右连接结果
应用场景:

该场景得到的是B的所有数据，和满足某一条件的A的数据；

2.4.[right join 或者right outer join] + [where A.column is null]

select a.id aid,a.age,b.id bid,b.grade 
from A a  
right join B b  
on a.id = b.id  
where a.id is null

结果如下:
右右连接结果
应用场景：

该场景下得到的是B中的所有数据减去 "与A满足同一条件的数据“，然后得到的B剩余数据；

2.5.full join （mysql不支持，但是可以用 left join union right join代替）

select a.id aid,a.age,b.id bid,b.grade 
from A a  
left join B b  
on a.id = b.id  
union  
select a.id aid,a.age,b.id bid,b.grade 
from A a  
right join B b  
on a.id = b.id

结果如下:union过后，重复的记录会合并。
union结果
应用场景:
union
该场景下得到的是满足某一条件的公共记录，和独有的记录

2.6.full join + is null（mysql不支持，但是可以用（left join + is null） union （right join+isnull代替）

select a.id aid,a.age,b.id bid,b.grade 
from A a  
left join B b  
on a.id = b.id  
where b.id is null  
union  
select a.id aid,a.age,b.id bid,b.grade 
from A a  
right join B b  
on a.id = b.id  
where a.id is null

结果如下:
union空
应用场景:
union
该场景下得到的是A，B中不满足某一条件的记录之和

注:上面共有其中七（2^3-1）种应用场景，还有一种是全空白，那就是什么都不查，七种情形包含了实际应用所有可能的场景

3.交叉连接(cross join)

3.1.笛卡儿积

实际应用中还有这样一种情形，想得到A，B记录的排列组合，即笛卡儿积，这个就不好用集合和元素来表示了。需要用到cross join：

select a.id aid,a.name, a.age,b.id bid,b.grade 
from A a  
cross join B b

cross join 的多种实现：

SELECT * FROM A CROSS JOIN B  //使用关键字 
SELECT * FROM A JOIN B   	//不加限定条件
SELECT * FROM A,B		//使用逗号分隔

结果为被连接的两个数据表的乘积，因此当有WHERE, ON或USING条件的时候一般不建议使用，因为当数据表项目太多的时候，会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN
笛卡儿积结果

3.2.有条件的笛卡儿积

select a.id aid,a.name,a.age,b.id bid,b.name 
from A a  
cross join B b  
where a.id = b.id

这种情况下实际上实现了内连接的效果

注意

上面仍然存在遗漏，那就是mysql对sql语句的容错问题，即在sql语句不完全符合书写建议的情况，mysql会允许这种情况，尽可能地解释它:
3.1 一般cross join后面加上where条件，但是用cross join+on也是被解释为cross join+where；
3.2 一般内连接都需要加上on限定条件，如上面场景2.1；如果不加会被解释为交叉连接；
3.3 如果连接表格使用的是逗号，会被解释为交叉连接；