Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Outer Join – What am I missing (SQL Spackle) Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 11:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:10 AM
Points: 2,738, Visits: 685
Comments posted to this topic are about the item Outer Join – What am I missing (SQL Spackle)
Post #1513334
Posted Tuesday, November 12, 2013 3:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:33 AM
Points: 24, Visits: 180
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



Post #1513382
Posted Tuesday, November 12, 2013 5:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:29 AM
Points: 75, Visits: 60
Grasshopper beat me to it
Post #1513421
Posted Tuesday, November 12, 2013 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 11, Visits: 47
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.
Post #1513427
Posted Tuesday, November 12, 2013 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 8:29 AM
Points: 147, Visits: 527
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?



Post #1513480
Posted Tuesday, November 12, 2013 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:46 PM
Points: 29, Visits: 211
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.
Post #1513552
Posted Tuesday, November 12, 2013 10:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:10 AM
Points: 2,738, Visits: 685

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.
Post #1513562
Posted Tuesday, November 12, 2013 11:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:39 AM
Points: 73, Visits: 669
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.
Post #1513567
Posted Tuesday, November 12, 2013 11:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 6:39 AM
Points: 73, Visits: 669
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).
Post #1513569
Posted Tuesday, November 12, 2013 1:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:33 AM
Points: 24, Visits: 180
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.



Post #1513624
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse