November 19, 2019 at 12:00 am
Comments posted to this topic are about the item Understanding Outer Joins in SQL
November 19, 2019 at 1:24 pm
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.
-- Itzik Ben-Gan 2001
November 19, 2019 at 7:33 pm
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.
November 19, 2019 at 10:46 pm
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy