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

Question

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 ?

Answer 1

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.

Answer 2

Those are equal, just a matter of preference and readability. I assume its the same tables?

Answer 3

Left Outer Join

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate)

Right Outer Join

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

http://en.wikipedia.org/wiki/Join_%28SQL%29

Answer 4

The designers of the SQL language rightly felt that enforcing left to right precedence of joins would be an unnecessary constraint on the language (sadly, they didn’t feel the same about column ordering!)

There does seems to be a strong preference for LEFT OUTER here on Stackoverflow, to the extent where folk will change the whole join just to be able to use LEFT (we had one here just yesterday).

Say you had originally written in your query Table_2 INNER JOIN Table_1 before you realized you actually need an outer join preserving all rows from Table_1. It would be a lot simpler to just change INNER to RIGHT OUTER than to change the whole join to be able to use LEFT OUTER. Simple is good here because it is less invasive and therefore less risk of the query's intent being changed inadvertently.

To use another similar example, consider the relational operator semi join; being part of the relational algebra, a technology cannot be considered to be relationally complete without it. Although Standard SQL does have a semi join predicate MATCH, it is not widely implemented. However, most SQL products support various workarounds. The most common approach seen on Stackoverflow seems to be to use INNER JOIN withDISTINCT in the SELECT clause and omitting attributes from the joined table. This is closely followed by using WHERE table_1.ID IN (SELECT ID FROM Table_2). Next most popular is WHERE EXISTS (SELECT * FROM Table_2 WHERE table_1.ID = table_1.ID).

The point is, all above are semi joins that are very commonly found in the wild. Although my personal preference is to use EXISTS (although curiously it is the one closer to the relational calculus), I still need to be able to identify the others as semi joins; interestingly, the most popular approach (INNER JOIN plus DISTINCT plus non-projection) can be the hardest one to identify!

Refactoring code for the sole purpose of fitting one's personal style is rightly frowned upon: cost of unnecessary effort, increase risk, implication for source control, etc. Learning to recognise and respect others preferences is an important skill: If you find yourself refactoring once just to be able to understand it you will be putting yourself at a disadvantage.

Of course, relationally speaking, the 'correct' answer is to avoid outer joins entirely. There is no such thing as null in the relational model and outer joins are expressly designed to produce nulls.

Answer 5

It depends on our need — whether we need all columns from Left table or Right table.

Both are not the same.

Answer 6

The answer that "LEFT OUTER JOIN and RIGHT OUTER JOIN are exactly the same" isn't at all true. Order of operations is that the JOIN select is executed first and then the FROM select is executed last.

So if you have a very specific selection from one table from which you want all qualifying records and a very broad selection from another from which you want merely matching records, a right outer join may perform significantly better than a left outer join.

In fact, a right outer join may run in mere seconds when a left outer join would otherwise give you an error like ORA-01652 in a database like Oracle.