Why does this statement only work with a WHERE?

Question

I have a Customers table and an orders table. Not all customers placed an order, so not all customer IDs are on the orders table. I want my result to show ONLY the values from the customer table who did not place an order, so the orderID column should show as null. The following code works:

SELECT c.CustomerID, c.CustomerName, o.OrderID
  FROM Customers c
    LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID WHERE o.OrderID IS NULL

But the one I tried originally doesn't:

SELECT c.CustomerID, c.CustomerName, o.OrderID
  FROM Customers c
    LEFT OUTER JOIN Orders o
ON c.CustomerID = o.CustomerID AND o.OrderID IS NULL

This one instead shows all the values on the customers table but ALL of them have null on for their orderID

I don't think I really understand the difference, since I feel like it would make sense for them to both return the same thing.

Answer 1

It is important to pont out that the two queries are very different. The first query is:

SELECT c.CustomerID, c.CustomerName, o.OrderID
FROM Customers c LEFT OUTER JOIN
     Orders o
     ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;

It returns all customers that have no corresponding orders (or o.OrderId is null). It does that because the left outer join keeps all rows in the first table. If there is no match, then all columns from the second table are NULL and the where clause would choose these columns.

The second query:

SELECT c.CustomerID, c.CustomerName, o.OrderID
FROM Customers c LEFT OUTER JOIN
     Orders o
     ON c.CustomerID = o.CustomerID AND
        o.OrderID IS NULL;

finds all rows all customers and also gets order information where OrderId is null, if any such records exist. There is no filtering of Customers, because the left outer join guarantees that all rows from the first table are in the result set.

I would be surprised if a field called OrderId ever took on NULL values. But, each query is valid SQL and each does something useful. Only one of them, however, does what you intend.

Answer 2

Using LEFT OUTER JOIN you get all records from Customers table. So your AND o.OrderID IS NULL condition filters only records from Orders table but not the records from Customers. But what you need is to filter Customers table and it does not work because of the type of the JOIN.

Meanwhile using WHERE condition is applied to the entire recordset regardless on the type of JOIN. Try to replace LEFT OUTER JOIN with INNER JOIN and you get the same results for both SELECTs.

In your second SELECT you get NULL values for o.OrderID because you specified that it should be NULL in your condition AND o.OrderID IS NULL. No such record exists in Orders table and thus the NULL value means that no record matches criteria ON c.CustomerID = o.CustomerID AND o.OrderID IS NULL.