Mysql join加多条件与where的区别

最近在项目中遇到一个问题，感觉有点意思，在解决问题及查阅了相关资料后，打算写篇文章给朋友们分享一下。

问题现象：

问题是很常见的空指针问题，后端查询数据库数据，遍历进行相关业务处理时报空指针。通过断点调试发现问题就出在查询回来的数据上，返回的List集合中有一条空数据导致的。

就很纳闷，怎么会返回空数据？

继续排查

发现sql执行完确实有一行空数据，先给兄弟们看一下sql（业务代码不便透露这里进行了简化，主要复现问题）：

SELECTo.order_id,o.order_no,o.detail_id,o.product_id,o.batch,o.comp_brand_id,o.supplier_id,o.buyer
FROMtr_production_purchase_contract_and_order contractOrderLEFT JOIN t_daily_purchase_order o ON contractOrder.order_id = o.order_id AND contractOrder.contract_id = '1585249657636917248' AND contractOrder.del_flag = '0'

大概意思就是关联搜索contract_id = '1585249657636917248’的数据，执行结果显示contractOrder.contract_id = ‘1585249657636917248’ AND contractOrder.del_flag = '0’条件没有生效。

为啥不生效？第一反应写错了，仔细检查没有问题。于是怀疑left join on后面加条件是不是有什么特殊的，查完资料，还确实非常特殊。join on后面加条件与where条件是不一样的。

先给结论：

• left join
  on后面条件筛选是对2张表生成的全连接（笛卡尔积）临时表进行的筛选，无论on后面的条件是否满足都会返回左表的所有数据，不符合条件的右表的值都为null

• right join
  同上，只不过是最终返回右表的所有数据，不符合条件的左表的值都为null

• inner join
  inner join有点不一样，它是两张表取交集，最终的结果是符合所有条件的值，所以on后面的条件可以生效

• where
  查询出来的结果最后都会再经过where条件进行过滤，满足条件才会返回

由此可见left join 的时候，on后面对左表的数据加筛选条件是没有用的。上面问题的原因也找到了因为只查询了右表的值，因为不满足条件，所以查询回来的数据为空。

为了验证这个观点，我们进行一下测试：
员工表

部门表

left join 单条件查询

select 
t.emp_id,
t.name,
t.age,
d.dept_id,
d.dept_name
from t_emp t
left join t_dept d on t.dept_id = d.dept_id

on 后面加条件

select 
t.emp_id,
t.name,
t.age,
d.dept_id,
d.dept_name
from t_emp t
left join t_dept d on t.dept_id = d.dept_id and t.emp_id = '4';

把on的所有条件作为匹配条件，不符合的右表都为null

where 后面加条件

select 
t.emp_id,
t.name,
t.age,
d.dept_id,
d.dept_name
from t_emp t
left join t_dept d on t.dept_id = d.dept_id where t.emp_id = '4';

匹配完再筛选，结果只有一条记录。

inner join 多条件查询

select 
t.emp_id,
t.name,
t.age,
d.dept_id,
d.dept_name
from t_emp t
inner join t_dept d on t.dept_id = d.dept_id where t.emp_id = '4';

总结：

在连表操作的时候，其实是先进行了2表的全连接（笛卡尔积，也就是所有能组合的情况a.rowCount*b.rowCount），然后根据on后面的条件进行筛选，最后如果是左连接或者右连接，再补全左表或者右表的数据。

个人理解不一定正确，欢迎指正

参考文章：
join on多个条件的理解