今天在修bug的时候发现了一个蛮有意思的sql
好了,我们现在用1分钟来看一下这句sql
SELECT a.id
FROM `item_measure_unit` a
LEFT JOIN `item` b
ON a.item_id = b.id
AND a.id IN (1, 20001, 68, 679);
ok,一分钟结束,乍一看好像没有什么问题,如果按照我们所想,是不是应该出现a表item_id=b表id的a的id呢?
如果你这么想,好了恭喜你,这篇文章你肯定有所收获。
实际上,这个sql并没有控制到a表的id在(1, 20001, 68, 679)当中。
为什么呢?
一、我们来一下分析 LEFT JOIN 和条件的行为:
1.执行顺序:
LEFT JOIN 在进行连接时,会保留左表(即 item_measure_unit 表)中的所有行,并为右表(即 item 表)中没有匹配的行填充 NULL。
ON 子句用于指定连接条件。注意,LEFT JOIN 不会过滤掉左表中的行,即使连接条件不满足,左表的行也会出现在结果集中。
2.为什么 a.id IN 条件没有生效:
AND a.id IN (1, 20001, 68, 679) 是放在 ON 子句中的,这意味着这个条件是连接条件的一部分,而不是一个过滤条件。这会影响连接的行为,但不会过滤 item_measure_unit 表中的行。
二、结果说明:
在这种情况下,SQL 查询返回所有左表 item_measure_unit 中的记录,甚至不满足连接条件的行也会被保留。连接条件失败时,右表列将填充 NULL。
三、那我们如何修正查询使 IN 条件生效
要让 IN 条件作用于结果集,可以把这个条件作为 WHERE 子句的一部分,而不是 ON 子句的一部分:
修正后的 SQL 查询:
SELECT a.id
FROM `item_measure_unit` a
LEFT JOIN `item` b
ON a.item_id = b.id
WHERE a.id IN (1, 20001, 68, 679);
这样就可以解决了。
总结
原因:条件在 LEFT JOIN 的 ON 子句中而不是 WHERE 子句中。
修正:迁移条件到 WHERE 子句。