连接处的条件和连接处的条件之间的差异

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.

谢谢。

4 个解决方案

#1

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.

第一个允许顺序为NULL，因为它是左连接。第二个没有，因为它检查o的值。订单类型后加入。

The equivalent would be (assuming OrderType can't be NULL)

等价的是(假设OrderType不能为空)

SELECT
    o.*
FROM
    Customer c
LEFT JOIN 
    [Order] o ON o.CustomerID = c.CustomerID
WHERE
    c.Country = 'USA'
AND
    (o.OrderType = 'Cash' OR o.OrderType IS NULL)

#2

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.

在第一种情况下，您可能会得到更少的记录，因为如果一个客户的订单被Join中的现金条件过滤掉，那么只有一个客户会通过WHERE条件。

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.

确保您确实需要左连接，如果需要，那么请确保在这种情况下需要这两个语义中的哪一个。

#3

A great explanation:

一个伟大的解释:

Ask the Experts: Terry Purcell on Outer Joins

问问专家们:特里·珀塞尔(Terry Purcell)的《外部连接》(Outer join)

#4

In the first example, filter condition is first applied to filter orders of order type cash and then joined with customer table.

在第一个示例中，首先将筛选条件应用于筛选订单类型现金的订单，然后与customer表联接。

In the second example, two tables are joined and then filtered condition is applied. Hence the result will be different.

在第二个示例中，连接两个表，然后应用过滤条件。因此结果将是不同的。

#1