Difference between Condition in Where and Condition in Join

Question

Hi All

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.

Answer 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.

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

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)

Answer 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.

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.

Answer 3

A great explanation:

Ask the Experts: Terry Purcell on Outer Joins