SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Outer Join – What am I missing (SQL Spackle)


Outer Join – What am I missing (SQL Spackle)

Author
Message
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3990 Visits: 1005
Comments posted to this topic are about the item Outer Join – What am I missing (SQL Spackle)
deroby
deroby
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 314
Hi there,

Thx for the article about what often turns out to be a brain-breaker for a lot of people starting out with SQL.. but... (there's always a but =)
Although the LEFT OUTER JOIN ... WHERE <somefield> IS NULL gets the job done I tend to discourage its use for three reasons
1. It isn't very readable for people not into SQL
2. I've seen it 'fail' when people use a <somefield> that turns out to be NULL-able
3. Not applicable in your example, but the JOIN might cause doubles/triples/etc in the results depending on the data-structure... which they then 'fix' again by using DISTINCT etc... ( "ahh, the horror" =)

The (IMHO) much easier to understand (and code) method is by using WHERE NOT EXISTS() which pretty much circumvents all three issues. Ironically it usually ends up with the same query plan anyway. Or, (as is the case here!) sometimes even comes up with a better one. But most of all, I think it is a lot easier to read which (on the long run) makes it a more favourable option to me.


/*List of employees and courses they have not completed*/
SELECT Employee.EmployeeId
,Employee.FirstName
,Employee.LastName
,Course.CourseId
,Course.CourseName
FROM #Employee AS Employee
CROSS JOIN #Course AS Course
WHERE NOT EXISTS ( SELECT *
FROM #CourseCompleted AS CourseCompleted
WHERE CourseCompleted.EmployeeId = Employee.EmployeeId
AND CourseCompleted.CourseId = Course.CourseId )
ORDER BY Employee.EmployeeId



My 2 cents.
Roby



Paul McKay
Paul McKay
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 64
Grasshopper beat me to it :-)
brymen
brymen
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 123
I have on my clipboard the identical query posted by deroby (I hope I'm pronouncing that correctly.) LEFT OUTER JOIN ... WHERE <somefield> IS NULL is a good step in learning how to solve these types I queries. Although shouldn't that step be followed by learning about WHERE NOT EXISTS?

To answer the Patrick's question "What am I missing?", you are missing the opportunity to show others how to properly use WHERE NOT EXISTS!

If the article ended with deroby's post, It would be a 5 star article. It has been my experience that WHERE NOT EXISTS always meets or beats a LEFT OUTER JOIN ... WHERE <somefield> IS NULL query.

Patrick, I do really like your test data generation queries. I learn something new everyday.
Andy DBA
Andy DBA
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 775
brymen (11/12/2013)
...If the article ended with deroby's post, It would be a 5 star article. It has been my experience that WHERE NOT EXISTS always meets or beats a LEFT OUTER JOIN ... WHERE <somefield> IS NULL query...

I agree. It took me a while to come around on this one, because it seems like you might be creating a hidden loop with WHERE NOT EXISTS, but often the query plan is identical to LEFT OUTER JOIN. In cases where there could be many matches (eg. employees could take a course many times), WHERE NOT EXISTS is faster because the server can stop "looking" when it finds the first match, but my understanding is that LEFT OUTER JOIN retrieves all matches first and then applies the "WHERE <somefield> IS NULL" to filter them out.

Does anyone know if this has always been true, or was there a time in the dark ages of MS SQL server when WHERE NOT EXISTS was slower than a LEFT OUTER JOIN?



wagner crivelini
wagner crivelini
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 282
thx for sharing, Patrick

I always complain how hard it is to find practical uses of the CROSS JOIN and you showed us a nice example.
Patrick Cahill
Patrick Cahill
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3990 Visits: 1005

The (IMHO) much easier to understand (and code) method is by using WHERE NOT EXISTS() which pretty much circumvents all three issues. Ironically it usually ends up with the same query plan anyway. Or, (as is the case here!) sometimes even comes up with a better one. But most of all, I think it is a lot easier to read which (on the long run) makes it a more favourable option to me.


I agree that using WHERE NOT EXISTS is easier to read, the purpose of the article was to show how to get the results using the outer join. But I do agree that I could have shown other solutions and it would have made the article better.
thisisfutile
thisisfutile
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 Visits: 1005
wagner crivelini (11/12/2013)
thx for sharing, Patrick

I always complain how hard it is to find practical uses of the CROSS JOIN and you showed us a nice example.


I agree. A fine example of Cross Join usage.
thisisfutile
thisisfutile
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 Visits: 1005
the purpose of the article was to show how to get the results using the outer join


I think it was a good choice as it made the example easy to understand because it kept it linear. Adding another element (optimization) would have just made it more confusing and then you would have had people complaining that you didn't post any optimization proof (screenshots from the optimizer).
deroby
deroby
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 314
I'm starting to feel bad here ...

Patrick Cahill (11/12/2013)

I agree that using WHERE NOT EXISTS is easier to read, the purpose of the article was to show how to get the results using the outer join. But I do agree that I could have shown other solutions and it would have made the article better.


I most certainly didn't want to downplay your explanation & examples; I (and obviously everyone else here) liked them a lot, but given the likely-hood someone might land on this page from a search for 'outer join & what's missing' I felt like a note about WHERE NOT EXISTS() was in order. (**)

(**: mostly because people often don't look further than the first thing 'that works'; worse, I've been having plenty of fights here at work about this exact subject where the argument often seems to be : but LEFT OUTER JOIN ... WHERE <field> IS NULL looks more technical so it HAS to be better... )

But I digress =) Like I said, thx for a fine article! I was merely trying to complement it.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search