浅谈left join的一个sql问题

目录

 

背景

写sql

看一个例子先

问题剖析

总结

---

背景

通过比较某张表本月跟上月的差异数据，获取新增的数据有哪些，看背景这是一个比较简单的场景，也就是通过where过滤获取这个月的数据，然后再获取上个月的数据，将两个集合left join关联（左表应为本月数据的集合），然后判断关联表为null的数据，即可获取本月新增数据。ok，分析结束，开始动手编写sql。

写sql

第一次写出来的sql如上，自关联之后，在where条件后分别用当前期间和上月期间作为条件过滤查询，结果出来的结果只有17273条，而当前表2021-02期间数据记录为20090条，结果明显看起来不对，但如此简单sql，瞬间懵了，没看出哪有问题。继续编写第二个sql如下：

出来的结果竟然更离谱，印象中left join后面可以加多个and条件，但结果竟然为52077条，更懵逼了，继续改造sql看结果：

这回出来的结果是正常的了，20090条。先获取2021-02期间的数据作为一个临时结果集，再查询2021-01期间的数据作为另一个临时结果集，然后将这两个数据left join之后就拿到我们想要的结果了，那么为何呢？一脸懵逼，只感觉三个sql似乎都是对的，但结果差异太大，难道此前写了好多自己没觉察到的bug了？

看一个例子先

表1：product

表2：product_details

left join关联查询如下：

select * from product a
left join  product_details b
on a.id = b.id

结果如下：

在left join on后边加一个非关联条件如下：

SELECT * FROM product LEFT JOIN product_details
ON product.id = product_details.id
AND product.amount=200;

结果如下：

不是对left join的四条数据的结果集直接再用product.amount=200条件进行过滤，而是将不满足条件的关联表数据全部置为了null。

在where后面加product.amount=200如下：

SELECT * FROM product LEFT JOIN product_details
ON product.id = product_details.id
WHERE product.amount=200;

结果如下：

会将left join返回的四条数据的结果集，再次通过where条件进行过滤，然后返回最终满足条件的一条数据。

问题剖析

基于以上例子，相信问题原因就比较明了了，首先先讲上述第一个图的sql，会先将两张同样的表自关联后（这里跟是不是同一张表，没有太大关系），拿到一个关联后的结果集，然后在where条件里分别再对这个结果集先进行t1.period='2021-02'的过滤，再对t2.period='2021-01'的过滤，最终得到的结果其实是t1和t2表的交集，而left join后的结果应该是左表未能匹配到的右表的数据，应该会以t2.period=null返回结果集，所以这种过滤的结果不对。

再对第二个图进行剖析，直接在left join on后添加多个条件，其实并不是直接对结果集进行过滤，而是将不满足left join on后面所有条件的关联表数据都返回null，由此第二个截图的sql表达的意思就变成了整表自关联，然后分别判断t1.period='2021-02'和t2.period='2021-01'，关联后未满足这两个条件的关联表返回null。

最后第三个图为什么是对的，相信大家就比较清楚了吧，先返回要关联的表数据，然后再进行left join才是关联查询的正确姿势。

总结

希望做个记录，在这之前竟然从没注意left join这几种不同的写法竟然表达的含义不一致，想想写了这么多年sql，这些基本竟然没弄懂，虚惊啊。。回溯了一些自己写的代码的sql，竟然都没问题，估计是从实际场景出发，然后每次写了sql去认真验证了结果的吧。。。