left jon连接查询踩坑记
项目开发中经常会使用到多张表进行关联查询,比如left join关联查询。
如果有一张表A和一张表B,查询语句 SELECT a.*,b.name from A a left join B b
On a.id = b.id 表示的含义的就是取A表独有的数据和A表和和B表共有的数据。
如上图所示,主表是A表,用A表去左关联查询B表,正常情况下会返回A表
的所有数据,不管这些数据在B表中是否存在。
自己在写SQL语句的时候,突然发现查询条件的位置不一样,导致查询结果
出现很大的差异,让我很是疑惑。下面就来复现这个问题,首先创建两张表A和B.
表A插入3条数据,没有删除,is_deleted = 1 表示删除。
表B插入三条数据,删除其中两条数据。
自己查询的时候,想要查询两张表中没有删除的数据,于是写了第一个查询语句,结果如下,
自己一看这结果明显的不对,表A是主表,会取表中的所有数据,这种查询方式只取到一条数据。
然后换一种方式进行查询,如下
得到了自己想要的结果,A表的所有记录都需要,去关联B表查询的时候,如果匹配到就取B表
的结果,如果没匹配到就返回null。进一步改进的查询方式为,
为了更好的比较,自己又添加一个查询语句,把b表的是否删除的条件去掉,查询结果如下
自己很好奇为什么会出现这种状况呢?查询条件写的位置不一样,就会导致查询结果不一致。
然后去网上寻找问题的答案,最终找到这个问题的答案:
on条件是在生成临时表的查询条件,不管连接的条件如何,最终都会返回主表的所有数据。
Where条件则是过滤掉临时表的数据,即是对最终数据的过滤,如果条件不符合则就直接过滤掉。
因此
SELECT
a.*, b. NAME
FROM
table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE
a.is_deleted = 0
AND b.is_deleted = 0;
这种查询方式就把后面两条数据过滤掉,因为其不满足 b.is_deleted = 0 的条件。
自己在查询的时候,只是想使用B表的有效数据进行关联查询,过滤条件就不能直接写在
Where条件中,需要写在on后面或者是使用子查询过滤掉。
到此问题解决。以前写关联查询的时候,不需要考虑数据是否有效都是直接进行关联查询,
现在遇到问题,则需要特别小心,稍微不注意就会出现意想不到的错误,吸取这次的深刻教训。
参考菜鸟教程中的答案。
https://www.runoob.com/w3cnote/sql-different-on-and-where.html