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