Why does this statement only work with WHERE?

Question

I have a Customers table and an order table. Not all customers placed an order, so not all customer IDs are in the order table. I want my result to display ONLY the values ​​from the table of customers who did not place the order, so the orderID column should be displayed 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 initially does not:

 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 

Instead, all values ​​in the Customers table are displayed, but ALL of them have zero for their orderID

I do not think that I really understand the difference, because I feel that it would be advisable for them to return the same.

Answer 1

This is the nature of the outer join (in this case, the left join). The left join takes your main table (Customers), matches it according to the criteria for the combined table (Orders). For each row in the Clients that does not have a match, unlike an internal join, it does not delete the row. Instead, it adds all the fields from Orders, but puts zero in them.

Take a look at this example:

  Table a table b
 ┌──────┬────── ┐───────┬──────┐
 │field1│field2│ │field3│field4│
 ├──────┼────── ┤───────┼──────┤
 │A │1 │ │1 │One │
 │B │2 │ │3 │Three │
 │C │3 │ └──────┴───────┘
 └──────┴──────┘

Internal join of tables (between field 2 and field 3):

  ┌──────┬─────────────────────┐
 │field1│field2│field3│field4│
 ├──────┼─────────────────────┤
 │A │1 │1 │One │
 │C │3 │3 │Three │
 └──────┴─────────────────────┘

But the outer join of the tables should give you every record, and if there is no match, put zeros instead.

  ┌──────┬─────────────────────┐
 │field1│field2│field3│field4│
 ├──────┼─────────────────────┤
 │A │1 │1 │One │
 │B │2 │NULL │NULL │⬅︎ No match
 │C │3 │3 │Three │
 └──────┴─────────────────────┘

Now, what happens if there are no matches in table 2? For example, if you added an impossible condition to an ON clause? Then all records as a result will look like "No match"

  ┌──────┬─────────────────────┐
 │field1│field2│field3│field4│
 ├──────┼─────────────────────┤
 │A │1 │NULL │NULL │⬅︎ No match (because of impossible condition)
 │B │2 │NULL │NULL │⬅︎ No match (because of impossible condition)
 │C │3 │NULL │NULL │⬅︎ No match (because of impossible condition)
 └──────┴─────────────────────┘

So it doesn’t matter if there were any matches, because in Table 2 there was no record with this ID, or if there was no match, because you added an impossible condition. The result of the outer join is that the fields that were supposed to appear from Table 2 will be replaced with zeros. Because it is the definition of external connection.

---

Now in the tables of the real world:

You actually have no entries in the Order whose OrderID is NULL (unless you designed it very badly). Therefore, if you put this condition in the ON clause, it will not find records matching your criteria.

In this case, since this is an external (left) connection, you get all the records of the original Clients, and since there were no matches, each of them has fields from Orders all null.

In the case where you put a condition in WHERE , you really made good use of this left join behavior. You matched each customer with his order. If there was a match - fine, you received the actual order ID. But in cases there was no match - the ones you are looking for - it adds a zero order identifier.

Then the where clause tells you that it only gives you records of where this happened. That is, entries that do not have an appropriate order in the Order.

Answer 2

It is important to understand that these two issues are very different. First request:

 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 who do not have matching orders (or o.OrderId is null). It does this because the left outer join stores all the rows in the first table. If there is no match, then all the columns from the second table are NULL , and the where clause will select these columns.

Second request:

 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 of all customers, and also receives order information, where OrderId is NULL if such records exist. There is no Customers filtering since the left outer join ensures that all rows from the first table are in the result set.

I would be surprised if a field called OrderId used NULL values. But every query is valid SQL, and every one does something useful. However, only one of them does what you intend.

Answer 3

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

Meanwhile, the use of the WHERE applies to the entire set of records, regardless of the type of JOIN. Try replacing the LEFT OUTER JOIN with an INNER JOIN and you will get the same results for both SELECTs.

In your second SELECT, you get NULL values ​​for o.OrderID , because you indicated that it should be NULL in your state AND o.OrderID IS NULL . Such an entry does not exist in the Orders table, and therefore a NULL value means that no entry meets the ON c.CustomerID = o.CustomerID AND o.OrderID IS NULL criteria ON c.CustomerID = o.CustomerID AND o.OrderID IS NULL .

Answer 4

This is simply not how SQL is designed. From http://dev.mysql.com/doc/refman/5.0/en/join.html

The condition_expr clause used with ON is any conditional expression of the form that can be used in the WHERE clause. Generally, you should use ON for conditions that determine how to join the tables, and WHERE to limit the number of rows in the result set.

Answer 5

The first is the POST join of the tables. The second is part of the association.

So, in English.

First:

  Get CustomerID, Name, and OrderID from Customers Link to Orders where CustomerID matches Show where link failed 

Secondly:

 Get CustomerID, Name, and OrderID from Customers Link to Orders where CustomerID matches and Order ID is null 

All links do not work in the second, leaving nothing to filter your results.

Answer 6

For the second query, your join condition is false for each row. If there is a CustomerId in the Order table, then it will also have an OrderId. After each row is excluded, the "left" part of the connection simply returns all excluded rows from the left table (Customer). In this case, they return all of them.

For the first query, it is only false in the lines you need, so the left join simply restores those lines. And then the where clause can take out strings you don't need.

Answer 7

Create table ## Client (Id nvarchar (5)) Create table ## Order (Order_No int, Id nvarchar (5))

Insert client values ​​in ## ('A'), ('B'), ('C')

Insert in ## Order Values ​​(1, 'A'), (2, 'B'), (3, 'B')

Select c. * from ## customer c left join ## Order o to c.Id = o.id, where o.Order_No is null

Select c.id, o.order_no from ## customer c left join ## Order o to c.Id = o.id and o.Order_No is null

Select c.id, o.order_no from ## customer c left join ## Order o to c.Id = o.id and o.Order_No is not null

try it yourself, you will see the difference where the offer provides a filter

Answer 8

LEFT OUTER JOIN saves unsurpassed rows from the left (first) table, connecting them to the NULL row in the form of the right (second) table. Take a concrete example. Let's say the Customers table has {c1,"n1"}, {c2,"n2"} and Orders have {c1, o1} Case 1: On the left, the outer join will result in:

 {c1,"n1", o1}, {c2,"n2", null} 

Now you know the difference where and "And." Where you will find out where you have a null orderid, for AND there is no case when the client identifier matches and the order identifier is null