When or Why Would You Use a Right Outer Join Instead of Left

When or why would you use a right outer join instead of left?

The only reason I can think of to use RIGHT OUTER JOIN is to try to make your SQL more self-documenting.

You might possibly want to use left joins for queries that have null rows in the dependent (many) side of one-to-many relationships and right joins on those queries that generate null rows in the independent side.

This can also occur in generated code or if a shop's coding requirements specify the order of declaration of tables in the FROM clause.

When to use right join or full outer join

Why would you join two tables and keep the rows that do not match of BOTH tables?

The full join has cases where it is useful.One of them is comparing two tables for differences like XOR between tables:

 SELECT * 
 FROM t1
 FULL JOIN t2
   ON t1.id = t2.id
 WHERE t1.id IS NULL 
    OR t2.id IS NULL;

Example:

t1.id ... t2.id
1         NULL
NULL      2

---

you could also achieve this by using two left joins.

Yes you could:

SELECT t1.*, t2.*
FROM t1
LEFT JOIN t2
  ON t1.id = t2.id
WHERE t2.id IS NULL
UNION ALL
SELECT t1.*, t2.*
FROM t2
LEFT JOIN t1
  ON t1.id = t2.id
WHERE t1.id IS NULL;

Some SQL dialects does not support FULL OUTER JOIN and we emulate it that way.
Related: How to do a FULL OUTER JOIN in MySQL?

---

On the other hand RIGHT JOIN is useful when you have to join more than 2 tables:

SELECT *
FROM t1
JOIN t2 
  ...
RIGHT JOIN  t3
 ...

Of course you could argue that you could rewrite it to correspodning form either by changing join order or using subqueries(inline views). From developer perspective it is always good to have tools(even if you don't have to use them)

Does Right Outer Join have any useful purpose?

It depends on what side of the join you put each table.

If you want to return all rows from the left table, even if there are no matches in the right table... you use left join.

If you want to return all rows from the right table, even if there are no matches in the left table, you use right join.

Interestingly enough, I rarely used right joins.

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

 ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [  ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL

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

Which is better.. left outer join or right outer join?

As others have pointed out already LEFT OUTER JOIN and RIGHT OUTER JOIN are exactly the same operation, except with their arguments reversed. Your question is like asking whether it is better to write a < b or b > a. They're the same - it's just a matter of preference.

Having said that, I find that most people are more familiar with LEFT JOIN and use it consistently in their SQL. Some people even find it quite difficult to read if there is suddenly a RIGHT JOIN in the middle of a query, and it causes them to have to stop and think what it means. So I'd suggest that given an equal choice between the two options, prefer to use LEFT JOIN. Being consistent will make it easier for other developers to understand your SQL.

How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?

Yes, it depends on the situation you are in.

Why use SQL JOIN?

Answer: Use the SQL JOIN whenever multiple tables must be accessed through an SQL SELECT statement and no results should be returned if there is not a match between the JOINed tables.

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

If we can interchange left or right join then what is the use of any specific type of join?

I think this is actually a good question. I have never heard a really good reason why right join exists. Maybe something from the very old days of SQL required it? It would be really cool is someone had an answer other than appearance.

I like the comment by Gordon.

1 - 1 is the same as 1 + (-1)

To expand on the idea, I have always felt it was for looks. I always use left joins and order the tables in my query to achieve the results I want but could easily use right joins or a mix.

For example

Select a.field, b.field
   From a
   left join b on a.id = b.aid

will return the same results as:

Select a.field, b.field
   From b
   right join a on a.id = b.aid

What it comes down to is that the first table is the left table and the next table is the right table. If you want all the records from a it has to be first in a left join and has to be second in a right join.

---

Related Topics