98

What is the difference in results between:

  1. RIGHT JOIN and RIGHT OUTER JOIN
  2. LEFT JOIN and LEFT OUTER JOIN ?

Can you please explain it through some examples?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Puru
  • 8,913
  • 26
  • 70
  • 91
  • Re abuses of Venn diagrams for inner vs outer join (including on this page) see my comment on the duplicate question. – philipxy Apr 29 '19 at 02:07

4 Answers4

181

There is no difference between RIGHT JOIN and RIGHT OUTER JOIN. Both are the same. That means that LEFT JOIN and LEFT OUTER JOIN are the same.

Visual Representation of SQL Joins

Timotej Leginus
  • 304
  • 3
  • 18
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • 2
    This is interesting. (n00b alert) Why would you need both right and left joins then? Why not just reverse the positions of your main & joined tables in your query and make do with one join direction? – Drew Jul 06 '10 at 07:28
  • 2
    Because you sometimes join more than 2 tables together and it just gives you more flexibility in how you choose to write out the query. – Lèse majesté Jul 06 '10 at 10:08
  • 1
    When you look at this image for a while and think about this, the word "join" starts to look silly and make no sense. – Arve Systad Jul 07 '10 at 11:33
  • This is a bit confusing - it gives the impression that `A LEFT JOIN B` is the same as `A`. Is there any difference between the excluding joins and `EXCEPT (SELECT A.* FROM A INNER JOIN B ON A.Key = B.Key)`? – BlueRaja - Danny Pflughoeft Jul 19 '10 at 17:11
  • 1
    It is true you can get away without the right join as it can always be expressed as a left join if you reverse the tables. However, sometimes you have a complicated query written and find you need an outer join and it is sometimes simpler to use the right join rather than reorder the tables in a complicated join. – HLGEM Jul 21 '10 at 18:26
  • 1
    what about `CROSS JOIN`? – RPM1984 Jan 19 '11 at 07:01
  • 6
    I removed the image; according to the CodeProject license, `The Articles discussing the Work published in any form by the author may not be distributed or republished without the Author's consent.` Unless you have the author's permission to use this image, you cannot add it to your question. –  Mar 25 '11 at 18:59
  • Sorry guys I don't get this, why would we have two completely different joins being equal completely? Why then the need for the different terms? – Sizons Nov 07 '16 at 06:58
  • 1
    @Sizons Did you read the answer? It's 2 cases of 2 names for the same thing. It is not a case of "two completely different joins being equal completely"--which is a (redundant) contradiction in terms, so how could it be a case of that? (Rhetorical.) The names predate the programming language, "outer" is optional, clearly to allow both calling the things by their correct names & calling them by shorter names. – philipxy May 07 '19 at 08:33
32

Nice explanation of SQL Join:

enter image description here

Reference: link

Mahesh
  • 1,063
  • 1
  • 12
  • 29
25

Here's a very nice Visual Explanation of joins generally by our very own Jeff Atwood. A right outer join is the same as a right join, and left join and left outer join are also the same.

Leonard
  • 13,269
  • 9
  • 45
  • 72
  • He repudiates that post in its own comments. See my comment on the question (& that post) re Venn diagram abuse. – philipxy May 07 '19 at 08:13
  • I can't find your comment. I'm not sure 'repudiates' is the word I would choose. It's clear that Venn diagram-type explanations don't cover several important cases though. – Leonard May 10 '19 at 01:11
  • Excuse me, I don't see the comment by me I expected among the 208 there. But what I expected it said is what other comments of mine say, see my comment on the question here. – philipxy May 10 '19 at 04:10
  • You don't seem to have paid attention to what my comments actually say, which is that people don't pay attention to what Venn diagram posts actually say. Eg: The circles say tableA & tableB, but under the most charitable interpretation they contain row values that are in Table A left join tableB & tableA right join tableB. Jeff's comment (#33) includes "anything that results in more rows than you originally started with does absolutely breaks the whole venn diagram concept"--that is repudiation. Moreover he's wronger than he thinks, because they break down when there aren't more rows also. – philipxy May 10 '19 at 04:12
14

What is result difference between:

RIGHT JOIN and RIGHT OUTER JOIN

No difference.

LEFT JOIN and LEFT OUTER JOIN ?

No difference.

Simply put, the OUTER keyword is optional. You can include it or omit it without affecting the resultset.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138