Can RIGHT OUTER could be written as a LEFT OUTER by just flipping the tables

Question

Can we use right join instead of left join and vice versa by just flipping the table position in the query.If so then why we need both joins?

Answer 1

Yes. Following query have same result:

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column1 = table2.column2


SELECT *
FROM table2
RIGHT JOIN table1 ON table1.column1 = table2.column2

Answer 2

If a developer wants all records from #t1 and only matching records from #t2, then he can write the query as Select * from #t1 left join #t2 or Select * from #t2 right join #t1

Both are same. They give same results.

First, there are more than just these 2 types of joins. There is also a FULL OUTER JOIN, in which case non-matching rows from both sides are included in the result. Additionally, an INNER JOIN (matching rows only) and CROSS JOIN (cartesian product) are available.

In a simple query like the examples in this thread, one can use LEFT or RIGHT and reverse the tables to return the desired results. But as additional tables are added to the query, it may be easier to add those with different join type rather than refactoring the entire query to achive the same semantics.

Answer 3

Yes, the only difference is the order in which you write the joined tables, you can rewrite LEFT into RIGHT and vice versa. So strictly speaking, we do not need to have both joins.

There is a discussion on why people use RIGHT joins.