Understanding Outer Joins in SQL

Question

Understanding Outer Joins in SQL

Steve Jones - SSC Editor

SSC Guru

Points: 735513
More actions
November 19, 2019 at 12:00 am

#3697798

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

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

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

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 More actions · Answer 2

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 SSC-Addicted Points: 445 More actions · Answer 3

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 SSC-Addicted Points: 445 More actions · Answer 4

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.