Understanding Outer Joins in SQL

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720383

    Comments posted to this topic are about the item Understanding Outer Joins in SQL

  • Alan Burstein

    SSC Guru

    Points: 61087

     

    This is an inner join, and is an intersection of the data in the tables. This is shown in the image below.

    Careful here. An Inner Join and an Intersection are not the same thing. Unlike a JOIN, an Intersection returns a set, duplicates are removed. Joins return a multiset, duplicates are allowed. I struggled with this early on.

    One final note on performance. While outer joins can be very helpful and handy, they do come with a performance penalty of sorts. 

    It depends here. OUTER <> lesser performance. I've run into a number of situations where switching to an OUTER Join then filtering the NULLs improved performance.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Bob Gaydos

    Newbie

    Points: 4

    Good article – thank you, but the A Gotcha section only tells half the story.  Please see this older post for what can happen when one uses a condition in the WHERE clause which references a column in the right-hand table when the proper thing would have been to use that condition as a predicate of the LOJ.

    I have have found this is a more common error than the "AND rt.Id IS NULL" example in this article.

  • jonfrisbee

    Old Hand

    Points: 354

    Thanks, @alan. I agree this isn't quite an intersection, but for the purposes of explaining this to beginning SQL writers, I think the analogy makes sense. Getting caught up in mathematical sets isn't what I wanted to do.

    I have found this often to be a performance issue for large tables, which is why I put out the note. Not that it is, but because you will scan the entire table, it can be.

  • jonfrisbee

    Old Hand

    Points: 354

    Thanks, @bob-2, I'll update with a note that to be careful about which columns you include in the WHERE clause. I am assuming the editor will approve the change and update the article.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply