Why can't we use left outer join by swapping the tables, instead of Right outer Join?

Question

few days back, I have faced the question in an interview as following. "what is Right outer join?" I answered, " Right outer join joins two tables and returns the matched records from both the tables as well as non matching rows from Table A". the interviewer simply laughed and asked again, "Why we use Right join rather we can swap the tables and use left join?". Then my answer was," yes, we can use". But, he argued that "then why the concept of two joins? (left and right)" . I really got confused with this question. Please help me on this and correct my understanding on joins.

Answer 1

LEFT [OUTER] JOIN and RIGHT [OUTER] JOIN are completely interchangeable if you rearrange the order of the tables as well.

In other words, the following four join clauses will produce the same resulting rows:

A LEFT  JOIN B ON A.X = B.Y
B RIGHT JOIN A ON A.X = B.Y
A LEFT  JOIN B ON B.Y = A.X -- switched A.X = B.Y around
B RIGHT JOIN A ON B.Y = A.X

There's absolutely no difference in the results.

This is a convenience to you as a programmer.

See also this question:

• Difference between left join and right join in SQL Server

This means that the answer to this question:

Why we use Right join rather we can swap the tables and use left join?

is this:

Because you wanted to use Right join instead of Left join. It may be more natural to write the SQL that way, or you just like the word RIGHT more than the word LEFT.

Note: If you mix LEFT and RIGHT joins in the same query, you might get some odd results, but you mention none of that.

---

Note, this is syntax. There might be a difference in execution performance if the database engine uses the order to pick indexes and similar. The end result, data-wise, should be the exact same, however. I have no knowledge of any such performance tricks though, so there may be none, but there very well may be.

There may also be a difference in the resulting order, if the execution plans differ because of table ordering. Ie. the database engine will pick one table as a master and do a hash join or similar for the other, which may return the rows in a different order. However, unless you specifically order the rows, two result sets containing the same rows are equivalent, even if they don't have the rows in the same order. I find this less likely than the chance of a performance difference since one of the tables will always potentially contribute more rows to the result than the other, so which to pick as a master should be the same either way.