SQL – Which is Better: Left Outer Join or Right Outer Join?

joinsql

We can get the same result in both these ways..

Table_1 LEFT OUTER JOIN Table_2

Table_2 RIGHT OUTER JOIN Table_1

If we can get the same result why to use right outer join ? Which one is better ?

Best Answer

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.

Related Solutions

SQL Server LEFT JOIN vs LEFT OUTER JOIN – Differences Explained

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

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    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?.

Best Answer

Related Solutions

SQL Server LEFT JOIN vs LEFT OUTER JOIN – Differences Explained

Related Question