Comments posted to this topic are about the item Understanding Outer Joins in SQL
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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
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.
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.
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 4 (of 4 total)