Why LEFT JOIN doesn't bring all records from the LEFT table?

I want to discuss a problem I am sure many of us encountered at least once in our life. I myself made this common mistake at least two times before I learned it by heart and now can easily recognize and point others to it. Let's consider this typical life scenario - bring all customers and their order records. If the customer doesn't have any order, let's bring this customer anyway.

USE AdventureWorksLT
GO
SELECT
C.CompanyName,
OH.*
FROM SalesLT.Customer C
LEFT JOIN SalesLT.SalesOrderHeader OH
ON C.CustomerID = OH.CustomerID

SELECT @@RowCount

/*
---------
440
*/

We get 440 records in return. Now, suppose we only want to see expensive orders, e.g. orders that are greater than $5000. We re-write this query as

USE AdventureWorksLT
GO

SELECT
C.CompanyName,
OH.*
FROM SalesLT.Customer C
LEFT JOIN SalesLT.SalesOrderHeader OH
ON C.CustomerID = OH.CustomerID
WHERE OH.TotalDue > 5000

select @@RowCount

/*
---------
15
*/

And we only get 15 records! What happened? Where are the customers that don't have orders or have less expensive orders? By adding a WHERE condition on the OrderHeader table we transformed this query into an INNER JOIN. In order to keep this query as a LEFT JOIN, we need to move this condition from WHERE clause into JOIN clause like this

Read more: Beyond Relational

Posted via email from .NET Info