join筛选条件位置不同的区别

首先创建两张表a和b

概述： 本文将针对left join, inner join以及full join时筛选条件所在Join的前中后不同位置分别做剖析

一、left join

right join逻辑相反，不做示例

 1、对主表进行过滤筛选

1、在链接前对A进行筛选

select *
from
(
  select *
  from A
  where A.name = 'A'
)t
left join B
on t.id = B.id;

 

链接前的子查询过滤，可以直接将不满足A表条件的进行过滤，然后再链接

2、在链接时对A进行筛选!!!

select *
from A
left join B
on A.id = B.id and A.name = 'A';

 

而on后对A表的过滤只是链接条件而已，链接不上b表字段就为Null，左连接的目的就是保证左表的完整性，尽管23行不满足对A的过滤条件，但A表23行依然完整

3、在链接后对A进行筛选

select *
from A
left join B
on A.id = B.id
where A.name = 'A';

而链接后的整体过滤，会将所有结果全部查出，最后再过滤掉不满足A表过滤条件的，该结果和链接前过滤结果一致

而当我们查询链接前和链接后的过滤执行计划时发现，这两种执行计划完全一致，都是先对A表数据过滤，再进行join，也就是说，此处过滤会做谓词下推

注：对A表过滤，同时根据A表过滤的情况，对B表过滤，再join链接；如果不是谓词下推，则应当先Join链接，再对A表和B表数据进行过滤

4、总结

当我们需要对主表数据进行过滤时，可以使用子查询过滤（join前），也可以在最外层过滤（join后），推荐在最外层过滤，因为会做谓词下推，而且看起来层次少，可读性强；

但如果我们要保证主表完整性，则在on里过滤（join时），此时过滤条件并不是真正的过滤，只是链接条件而已。

2、对从表进行过滤筛选

1、在链接前对B进行筛选

select *
from A
left join
(
  select *
  from B
  where B.name = 'A'
)t
on A.id = t.id;

链接前的子查询过滤，可以直接将不满足B表条件的进行过滤，然后再链接，在最初始B表数据就已经只剩1行，因此后面的链接自然链接不上，链接不上就补null

2、在链接时对B进行筛选

select *
from A
left join B
on A.id = B.id and B.name = 'A';

而on后对B表的过滤只是链接条件而已，链接不上b表字段就为Null，为了保证左表的完整性，其余行补null

而当我们查询链接前和链接时的过滤执行计划时发现，这两种执行计划完全一致，都是先对B表数据过滤，再进行join，也就是说，此处的on看似是链接条件，其实也做了谓词下推，也提前对B进行了过滤

注：先对B表进行过滤，再进行join链接；如果不是谓词下推，应该先进行join链接，再进行对B表的过滤

3、在链接后对B进行筛选!!!

select *
from A
left join B
on A.id = B.id
where B.name = 'A';

而链接后的整体过滤，会将所有结果全部查出，最后再过滤掉不满足B表过滤条件的，虽然链接时右表数据也是完整的，但最后的where过滤，会直接把不满足B条件的过滤，同时也破坏了A表的完整性

4、总结

当我们需要对从表数据进行过滤时，可以在最外层过滤（join后），但要注意此时主表完整性也难以保证；

但如果我们要保证主表完整性，则可以使用子查询过滤（join前），也可以在on里过滤（join时），推荐在on里过滤，因为会做谓词下推，而且看起来层次少，可读性强；

二、inner join

因为没有主表概念，因此只单独拿对其中一个表的过滤来示例，另一个表的道理相同

1、在链接前对A进行筛选

select *
from
(
  select *
  from A
  where A.name = 'A'
)t
join B
on t.id = B.id;

2、在链接时对A进行筛选

select *
from A
join B
on A.id = B.id and A.name = 'A';

3、在链接后对A进行筛选

select *
from A
join B
on A.id = B.id
where A.name = 'A';

4、总结

三种情况结果完全一致，因为不管是在前中后，都没有所谓的主表，都没有必要保证某张表的完整性，因此不满足条件的直接链接不上就好了，而在链接前筛选，则提前把链接不上的过滤，链接后筛选则在最后把链接不上的过滤，因此三者结果一致

三、full join

因为两者都是主表，因此只单独拿对其中一个表的过滤来示例，另一个表的道理相同

1、在链接前对A进行筛选

select *
from
(
  select *
  from A
  where A.name = 'A'
)t
full join B
on t.id = B.id;

2、在链接时对A进行筛选

select *
from A
full join B
on A.id = B.id and A.name = 'A';

3、在链接后对A进行筛选！！！

select *
from A
full join B
on A.id = B.id
where A.name = 'A';

4、总结

由于mysql不支持full join用法，因此无法做测试，但要知道 full join等同于A left join B，再A right join B，最后再Union去重，所以说此时不管是对A还是B来说都是主表，另一张表对主表来说都是从表；

因此两张表都要保证数据完整性，参考left join时对从表进行筛选，所以说，不管是join前还是join中对任何一张表的过滤都是一致的结果，左右表的数据必定是完整的，但join后的过滤不一样，他会破坏左表或右表的完整性。

四、结论

1、left join对主表过滤，join前后一致数据量变少，中保持主表完整性；

2、left join对从表过滤，join前中一致保持主表完整性，后数据量变少；

3、inner join对任一表过滤，join前中后结果都一致；

4、full join对任一表过滤，join前中一致保持两张表完整性，后数据量变少；