Where 中的条件和 Join 中的条件之间的区别
谁能向我解释为什么以下两个查询会产生不同的结果？
SELECT
o.*
FROM
Customer c
LEFT JOIN
[Order] o ON o.CustomerID = c.CustomerID AND o.OrderType = 'Cash'
WHERE
c.Country = 'USA'
SELECT
o.*
FROM
Customer c
LEFT JOIN
[Order] o ON o.CustomerID = c.CustomerID
WHERE
c.Country = 'USA'
AND
o.OrderType = 'Cash'
谢谢。
Can anyone please explain to me why the following two queries yield different results?
SELECT
o.*
FROM
Customer c
LEFT JOIN
[Order] o ON o.CustomerID = c.CustomerID AND o.OrderType = 'Cash'
WHERE
c.Country = 'USA'
SELECT
o.*
FROM
Customer c
LEFT JOIN
[Order] o ON o.CustomerID = c.CustomerID
WHERE
c.Country = 'USA'
AND
o.OrderType = 'Cash'
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
第一个允许顺序为 NULL，因为它是左连接。
第二个则不然，因为它在连接后检查 o.OrderType 的值。
等价的是（假设 OrderType 不能为 NULL）
The first one allows the order to be NULL, because it's a left join.
The second one doesn't, as it checks the value of o.OrderType after the join.
The equivalent would be (assuming OrderType can't be NULL)
这是左连接。
在第一种情况下，您可能会获得较少的记录，因为如果他的订单被 Join 中的 Cash 条件过滤掉，则只有一个客户会传递到 WHERE 条件。
在第二种情况下，更多的客户订单对将通过，并且在 WHERE 过滤器之后可能会留下更多的客户订单对。
确保您确实需要 LEFT JOIN，如果需要，请确定在这种情况下您需要这两种语义中的哪一种。
It's the LEFT JOIN.
In the first instance you may get less records, because only one customer will pass through to the WHERE condition if his orders are filtered out by the Cash condition in Join.
In the second instance, more customer-order pairs will pass, and more may be left after the WHERE filters.
Make sure you realy need the LEFT JOIN, and if so, then make sure which of these two semantics you need in this case.
一个很好的解释：
询问专家：Terry Purcell 关于外连接< /a>
A great explanation:
Ask the Experts: Terry Purcell on Outer Joins
在第一个示例中，首先应用过滤条件过滤订单类型为现金的订单，然后与客户表连接。
在第二个示例中，连接两个表，然后应用过滤条件。 因此结果会有所不同。
In the first example, filter condition is first applied to filter orders of order type cash and then joined with customer table.
In the second example, two tables are joined and then filtered condition is applied. Hence the result will be different.