Using WHERE in SQL

问题:

I do not understand the difference between two coding given below. First one uses WHERE in LEFT JOIN. The second code one uses AND. I do not understand what is the output I will get from each codings?

SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID 
WHERE oh.SalesOrderNumber IS NULL 
ORDER BY c.CustomerID;


SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID  
AND oh.SalesOrderNumber IS NULL 
ORDER BY c.CustomerID;

回答1:

In short, the first query applies a filter to the joined tables after they are joined, whereas the second query applies it to SalesOrderHeader before it is joined to Customer. The distinction is important.

Suppose your Customer table has certain CustomerID's that do not exist in your SalesOrderHeader table. For example, consider the following two tables

Customer Table:
+ --------- + -------- + ---------- +
| Firstname | Lastname | CustomerID |
+ --------- + -------- + ---------- +
| Bob       | Dylan    | 1          | 
| Donald    | Trump    | 2          |
| Me Myself | and I    | 14         |
| Guy       | Gisbon   | 86         |
| Megan     | Meganson | 87         |
+ --------- + -------- + ---------- +


SalesOrderHeader Table:
+ ---------------- + ----------- +
| SalesOrderNumber |  CustomerID |
+ ---------------- + ----------- +
| 1681351          | 1           | 
| 1354894          | 86          |
| 1354900          | 13          |
| 1351666          | 86          |
+ ---------------- + ----------- +

When you perform the left outer join

SELECT c.FirstName, c.LastName, oh.SalesOrderNumber
FROM SalesLT.Customer AS c
LEFT OUTER JOIN SalesLT.SalesOrderHeader AS oh
    ON c.CustomerID = oh.CustomerID 

you will see all the records from the Customer table (because it is the left table), but any unmatched records from the SalesOrderHeader table will appear as nulls. So in our example, you would get

Resulting Joined Table
+ --------- + -------- + ---------------- +
| Firstname | Lastname | SalesOrderNumber |
+ --------- + -------- + ---------------- +
| Bob       | Dylan    | 1681351          | 
| Donald    | Trump    | null             |
| Guy       | Gisbon   | 1354894          |
| Guy       | Gisbon   | 1351666          |
| Megan     | Meganson | null             |
+ --------- + -------- + ---------------- +

Now if you apply the where clause

WHERE oh.SalesOrderNumber IS NULL

you are selecting a subset of the result table where the SalesOrderNumber is null. Here, you would get

Filter in where clause
+ --------- + -------- + ---------------- +
| Firstname | Lastname | SalesOrderNumber |
+ --------- + -------- + ---------------- +
| Donald    | Trump    | null             |
| Megan     | Meganson | null             |
+ --------- + -------- + ---------------- +

Now let's look at the 2nd query, where the null filter is included in the join condition. SQL will look for null SalesOrderNumbers from the SalesOrderHeader table. In our example, we don't have any null records, so you are essentially joining your Customer Table to an empty SalesOrderHeader table. The result would look like

Filter in join condition
+ --------- + -------- + ---------------- +
| Firstname | Lastname | SalesOrderNumber |
+ --------- + -------- + ---------------- +
| Bob       | Dylan    | null             | 
| Donald    | Trump    | null             |
| Guy       | Gisbon   | null             |
| Megan     | Meganson | null             |
+ --------- + -------- + ---------------- +

This distinction is only important for outer joins. If you're using an inner join, putting the filter in the join condition or in the where clause results in the same thing.