mysql的7种join(图码并茂,清晰易懂!)

1.总括

mysql种共有7种join(
但实际上只有4种主要的:左外连接,右外连接,内连接,全外连接`
以下7种不过在此基础上有一些变动:

2.具体实现

(1)内连接(inner join 可简写为join)

从图可知:就是交集,也就是两张表的共同数据;
用sql语句表示为:

select * from a [inner] join b on a.key = b.key

(2)左外连接

从集合上看就是A,B的交集加上A的私有

从集合上看就是A,B的交集加上A的私有
用sql语句表示:

select * from a left join b on a.key = b.key

(3)右外连接

从集合上看就是A,B的交集加上B的私有
用sql语句表示:

select * from a right join b on a.key = b.key

(4)全外连接

从集合上看:就是A和B的共有+A的私有+B的私有 (AB全有)
对应的sql为:

select * from a full outer join b on a.key = b.key;

注意:上面的sql只在Oracle中适用,MYSQL中不支持,因此在MYSQL中想要实现全外连接,得用联合查询(union)实现
什么是联合查询(union)?
简单来说就是把两个sql语句的结果取并集(也就是去重复值)
sql语句如下:

select * from a left join b on a.key = b.key 
union
select * from a right join b on a.key = b.key

剩下的3种只需要在上面4中join的基础上加where条件即可

(5)A的独有

相当于在(2)的基础上加上where条件:

select * from a left join b on a.key = b.key  where b.key is null

(6)B的独有

相当于在(3)的基础上加上where条件:

select * from a right join b on a.key = b.key  where a.key is null
或者
select * from b left join a on a.key = b.key  where a.key is null

(7)相当于A的独有+B的独有

相当于在(4)的基础上加上where条件:

select * from a full outer join b on a.key = b.key 
where a.key is null or b.key is null 

同样,此写法只在oracle中适用,在mysql中得用union

select  * from a  left join b on a.key = b.key where b.key is null
union
select  * from a right join b on a.key = b.key where a.key is null