101

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?

Improve this question
1
  • Re abuses of Venn diagrams for inner vs outer join (including on this page) see my comment on the duplicate question. Commented Apr 29, 2019 at 2:07

4 Answers 4

Reset to default
185

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

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

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?
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.
When you look at this image for a while and think about this, the word "join" starts to look silly and make no sense.
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.
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.
|
35

Nice explanation of SQL Join:

enter image description here

Reference: link

Improve this answer

1 Comment

doesn't have names
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.

Improve this answer

4 Comments

He repudiates that post in its own comments. See my comment on the question (& that post) re Venn diagram abuse.
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.
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.
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.
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.

Improve this answer

1 Comment

Where is this documented?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.