Mysql 难题：ON 与 WHERE 中的条件
以下两个查询不会返回相同的结果。为什么 ？
注意：我发现这个问题是一个Mysql难题，我没有关于这个问题的更多数据？
SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id
AND table2.val < 5
SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id
WHERE table2.val < 5
The following two queries do not return the same result. Why ?
Note : I found this question as a Mysql puzzle, I don't have more data on this question?
SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id
AND table2.val < 5
SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id
WHERE table2.val < 5
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
当条件不满足时，左外连接将返回行（如果选择了 table2 中的空列），而 WHERE 会过滤掉它们。
The left outer join will return rows (with null columns from table2 if they were selected) when the condition isn't met, whereas the WHERE filters them out.
第一个查询将返回表 1 中的所有行。 LEFT JOIN 始终返回左表中的所有行，无论您在连接条件中写入什么内容（但是，如果存在多个匹配项，它可能会重复行，但由于您正在连接名为
id的字段，它很可能是唯一键，因此不会重复）。第二个查询将仅返回表 1 中的行，其中表 2 中的相应行具有
val 值。 5.。实际上，您可以在此处编写INNER JOIN而不是LEFT JOIN，因为由于WHERE<，连接失败的行无论如何都不会出现在结果集中。 /代码> 子句。The first query will return all rows from table 1. A LEFT JOIN always returns all rows from the left table regardless of what you write in the join condition (it can however duplicate rows if there are multiple matches, but since you are joining on a field called
id, it is most likely a unique key, so there will be no duplicates).The second query will only return those rows from table 1 where the corresponding row in table2 has
val < 5. You could in fact have writtenINNER JOINhere instead ofLEFT JOINbecause the rows where the join fails will not be in the result set anyway due to theWHEREclause.第一个查询仅在
table2.val <; 时才加入5.。第二个甚至过滤 table1 行。如果您使用 INNER JOIN 代替，应该给出相同的结果。First query only joins if
table2.val < 5. Second filters even table1 rows. Identical result should be given if you use INNER JOIN instead.尝试以“编译器”的方式思考查看括号...
此示例在条件
table2.table1_id = table1.id 时执行 2 个表的 LEFT JOIN AND table2.val < 5 是正确的。此示例在条件
table2.table1_id = table1.id适用时执行 LEFT JOIN，然后获取table1 LEFT JOIN table2 ON ( table2.table1_id = table1.id ) 结果的行WHERE 条件table2.val < 5为真Trying to think as a "compiler" look the parenthesis...
This example does the LEFT JOIN of 2 tables when the condition
table2.table1_id = table1.id AND table2.val < 5is true.This example do the LEFT JOIN when the condition
table2.table1_id = table1.idapplies and then get the rows of the result oftable1 LEFT JOIN table2 ON ( table2.table1_id = table1.id )WHERE the conditiontable2.val < 5is true