3

I am a novice, and was doing some research on JOINS in SQL. I was directed to this excellent thread (Difference between INNER and OUTER joins), but it does lead me to a question regarding some other research I'd been doing with joins.

My initial understanding was that Left Outer Join returns all the data from the left table, plus includes all the shared-set (the middle of the Venn, overlap, crossover, pick a term), and a Right Outer Join includes all the data from the righthand table, plus includes all the shared-set...

...but I just read a blog (found the link http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/ recommended by CodingHorror, http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) which says:

"A left outer join combines the ideas behind a left join and an outer join. Basically – if you use a left outer join you will get the records in the left table that DO NOT have a match in the right table."

....and now I'm all kinds of confused. I have little/no doubt of the braininess of the people who wrote the articles, but I myself am left all derpy about it. Does the Left/Right Outer Join actually include the shared data? Or does it include only the data that does NOT have a match? Why is this seemingly simple concept so confusing? Do aliens exist? Who shot JFK? Mommy?!

*A note here, the author of the aforementioned blog does include the disclaimer that she is not an all-knowing guru...so maybe she's wrong? Or I'm just not understanding...

Dawn Deschain
  • 156
  • 10
  • 1
    Sounds like a poorly worded article. If you want a definitive answer, why not create some test tables and queries and see what happens? Anyway, your initial understanding is correct, at least for every RDBMS I've ever used. – Tab Alleman May 19 '15 at 19:47
  • 1
    The second article is describing an anti semi join not an outer join. They are describing `FROM A LEFT OUTER JOIN B ON A.C = B.C WHERE B.C IS NULL` – Martin Smith May 19 '15 at 19:48
  • 1
    http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins – Alex May 19 '15 at 19:49
  • That is a fantastic idea, Tab, thank you - that would be a better way to cement my understanding. @Alex, I understand if you need to flag this as a dupe, it is very similar to some other articles, although not exactly the same. No problem - the other questions I'd looked at conflicted with the blogger's article, but I had wondered if I was misunderstanding something since her article was cited by a well-respected coder. Thank you and again, no worries if you flag/delete. Thank you both for your help! – Dawn Deschain May 19 '15 at 19:50
  • Oh my, I will look further into that, @Martin, thank you! – Dawn Deschain May 19 '15 at 19:51
  • 1
    I don't think I clicked through on that article last time. It is horribly wrong on a variety of issues. – Martin Smith Jun 24 '15 at 17:34

1 Answers1

3

The article from Ligaya Turmelle is wrong. There is no difference between LEFT JOIN and LEFT OUTER JOIN

I also don't think that Jeff is actually recommending the blog but rather crediting the idea of using Venn Diagrams to descirbe joins to the article. The first paragraph ends with

However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my testing. (emphisis added)

When you do get the part in Jeff's artcle where he discusses LEFT OUTER JOIN he notes

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • 1
    Nor is there any such thing as an outer join that is equivalent to a " NOT AND." of course. More wrong than right in that article. – Martin Smith Jun 24 '15 at 17:36