图解mysql中的join,5分钟彻底明白join到底怎么回事

假如我们有A,B两张表,表结构分别如下

A表:(学生信息信息表)

a_id(学生ID)	a_name(学生姓名)
1	张三
2	李四
3	王五

B表:(学生迟到记录表) 

b_id(学生ID)	b_log(迟到记录)
2	周三迟到
3	周二迟到
2	周一迟到
4	周五迟到

当需要对这两个表做关联查询(Join)时,很多同学弄不清楚到底改使用哪一种join方式,mysql支持3种join方式,left join,right join,inner join,其实应该还有一种全连接(all join),但是mysql没有提供这种语法,需要使用union方式实现.

我们想象一下A,B两张表都是两个set集合,假如join的条件时on A.a_id = B.b_id,join实际上就是把两个集合求交集,那我们看一下两个表可以求出的交集是什么.

A,B共有

a_id	a_name	b_id	b_log
2	李四	2	周三迟到
2	李四	2	周一迟到
3	王五	3	周二迟到

这个交集其实就是A和B的笛卡尔集的中间表付合on条件的数据记录(A.a_id=B.b_id),我们暂且把这一部分称为A和B在on A.id=B.id的共同部分.

有了共同部分,我们再来看看非共同部分,A的非共同部分值A独有但是B没有,同理,B的非共同部分就是B独有但是A没有.一样画个表格:

       A独有

a_id	a_name	b_id	b_log
1	张三	null	null

       B独有

a_id	a_name	b_id	b_log
null	null	4	周五迟到

好了,现在我们理解了这3个部分,我们来画一个图,来表示A join B 按照 on A.a_id=B.b_id的条件下得到的这3部分数据

join之后得到的数据其实就是3分部的总和,现在理解了join总的含义,我们就来分析一下各种不同的join所得到的数据具体是哪些.

1. left join :

A left join B(等价于B right join A) 得到的是A独有部分加上共有部分,画个简图 

A  right join B (等价于B left join A)得到的是B独有的部分加上共有的部分,同样画个简图.

A inner join B (等价于B inner join A)得到的只有共有的部分,画个简图.

我们知道,这个积合分为3部分,得到其中任意1个或者多个部分应该有1(取3部分)+3(取2部分)+3(取1分布)=7种方式,而mysql只给我们提供了3种方式,其实另外4种方式可以通过这3种方式计算出来

 

 上面这种可以通过:  select * from A left join B on A.a_id=B.b_id where B.b_id = null

上面这种可以通过:  select * from A right join B on A.a_id=B.b_id where A.a_id = null 

上面这种可以通过: select * from (select * from A left join B union select * from A right join B);注意union 语句是会帮我们自动去重的

 

 上面这种可以通过:  select * from (select * from A left join B union select * from A right join B) where A.a_id=null or B.b_id=null;

现在大家理解了吗?如果你觉得这篇文章对你有帮助的话,欢迎点赞评论.