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 ?

Cory

To sum a and b, you can do a+b or b+a. Which one is better? – Retroaction

I think a modern RDBMS can transform one to the other and pick the one which performs better so it shouldn't matter. – Carnot

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.

Depurate

That's a better example than my addition one in the comments. For some reason, I was drawing a blank on a simple operator that wasn't symmetric :-) – Retroaction

Yes. I think the preference for LEFT probably comes from the natural inclination to start writing a query with the most "important" tables, which will often contain full information, i.e. have no rows missing, and add tables which may or may not contain related information to that. – Mer

Not the same, the result columns are in a different order. – Gorlovka

Answer 2

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

Prosector

+1 and I suggest you just pick a preference and stick with it. The end result should be the same if you are consistent in your outer joins. I would also suggest you use left outer joins if you are undecided, just because most others do so. – Kelikeligot

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

Fachanan

Yes, but I think the question the OP is asking is whether A LEFT OUTER JOIN B is the same as B RIGHT OUTER JOIN A (note the tables are swapped as well as the join direction.) – Mer

I just wanted to write how they work because then one can understand that they are not different if one use them as in the question and it is more useful information. – Fachanan

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.

Korwun

Answer 5

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

Both are not the same.

Dashtikavir

in both the queries we get all rows from Table_1.. i.e. the same result.. BTW it should be .. "whether" :) – Cory

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.

Ricci