Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
T-SQL 6
SQL Server 5
#SQL Server 5
#TSQL 4
BRH 3

Archive · View All
October 2010 2
April 2012 1
October 2011 1
September 2011 1
May 2011 1
April 2010 1
March 2010 1

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

Mar 22 2010 11:20PM by Naomi   

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

USE AdventureWorksLT
GO

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

select @@RowCount 

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

Now we get back 440 records. The reason of such behavior is simple. JOIN evaluates first and then WHERE clause of the SQL statement. Please see this excellent blog for the logical order of different clauses in the SELECT statement Anatomy of a Query and another article by Plamen Ratchev on this exact issue Outer Joins

 So, the records joined correctly get filtered in the WHERE clause and we end up with the INNER JOIN equivalent. Now you know the reason and hopefully you'll be able to avoid this problem in your code.

 

Tags: T-SQL,


Naomi
31 · 6% · 1776
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Seriously? That's really basic.

    commented on Mar 30 2010 5:56AM
    Pierre
    452 · 0% · 87
  • Excellent article. You touched on it briefly when you referred to the "INNER JOIN equivalent," but it should be made clear that referencing a column from the right table in the WHERE clause does not literally change the LEFT JOIN to an INNER... it simply makes it appear to behave like one.

    Since NULL comparisons always return false, the query is behaving exactly as one would expect a LEFT JOIN with a WHERE clause to behave. We have written the query incorrectly in regards to our intent, but it hasn't formally become an INNER JOIN simply because the result sets look the same.

    This can be illustrated by changing the WHERE clause in your second query to...

    WHERE ISNULL(OH.TotalDue, 5001) > 5000

    If the JOIN is now literally an INNER, we should not see different results from your second example, as we are now INNER JOINing ON C.CustomerID = OH.CustomerID. But instead we get the same results as the third example, indicating that the join is still a LEFT; we've simply corrected the error in the WHERE clause.

    All that being said, this is still very poor practice and all db developers should know to avoid it. So I'm not disagreeing with you at all, just offering a technical clarification.

    commented on Mar 30 2010 7:53AM
    Anonymous
    248 · 1% · 178
  • Thanks, Ron, for the extra explanation.

    commented on Apr 2 2010 11:06AM
    Naomi
    31 · 6% · 1776

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]