join的on和where的区别

select * from a left join b on a.xx=b.xx and condition1 where condition2;

1.先进行on然后进行where

2.on是join的条件，取a和b两张表的数据组合成中间表c，on是对a和b进行过滤

where是对中间表c进行过滤。

3.同一个condition放在on和where中是否有性能差距？

一般来说，能先过滤数据就先过滤数据，那么放在on中性能更好一点。不过

需要你实际explain，不过非特殊情况（1.非逻辑特殊，即condition摆放在on和where会导致结果不同 2.非业务特殊,需要特别进行查询优化），一般把非on a.xx=b.xx的condition放在where后面就好了。给SQL服务一点信心，相信它能够自己优化查询，它能在不影响结果的情况下尽早进行condition的过滤。

4.不考虑性能，我任意一个查询都能把除了a.xx=b.xx的其他condition都放在where后面吗？

不行，有些condition放在on和where会导致结果不一致。

回到第二点，on除了join两张表，它的condition是对a和b进行数据过滤，而where是对c进行过滤。

只要不是对同一个东西进行判断，那么就一定存在数据前后不相等的情况。即使a join b相当于c，但是，不是同一个对象就不是同一个对象。

看这个sql

SELECT count(*) FROM c_fact cf LEFT JOIN c_processing_detail cpd on cf.id = cpd.fact_id  AND cpd.domain_id ='TB'
WHERE  cf.domain_id ='TB' 
AND cf.is_latest  
AND cpd.id IS NULL ;

SELECT count(*) FROM c_fact cf LEFT JOIN c_processing_detail cpd on cf.id = cpd.fact_id
WHERE  cf.domain_id ='TB' 
AND cf.is_latest  
AND cpd.id IS NULL 
AND cpd.domain_id ='TB';

这里就有很大的区别。第一个sql是有数据的，第二个sql是没有数据的。

我on的时候过滤b表的数据来和你进行连接，连接结束之后对c进行cpd.id的判断。

我on的时候不进行数据过滤，on之后同时判断cpd.id is null && cpd.domain_id='TB', 一个数据id都为null了，其他列还能有数据？

所以，不要把冲突的condition全部放在where中。

5.最重要的一点

on 和 where最大的区别在于，如果你的join a.xx=b.xx不是一对一的，那么原本的a表100条件数据，b表的100条件数据，join后的c表数据不一定是小于100条的（完全一对一的join，任何join都是<=max(a.size, b.size)）.

这样一来，如果你的on condition过滤了非一对一的数据，结果就肯定相差很大。