When to use right join or full outer join

I work with DB / SQL almost on a daily basis and the more I work with sql, the more I’m the opinion that there is no reason to use a right join or a full outer join.

Let’s assume we have two tables: table1 and table2. Either I want to receive additional information for the rows in table1 so I can use an inner join on table2 and if I want to keep the original rows if there is no match, I use the left join then:

In case I have to add additional information to table 2, I can do the same and left join table 2 to table on. So I do not see a reason why I should ever use a right join. Is there any use case where you can not use a left join for a right join?

I also wondered if I would ever need a full outer join. Why would you join two tables and keep the rows that do not match of BOTH tables? We you could also achieve this by using two left joins.

Answer

Why would you join two tables and keep the rows that do not match of BOTH tables?

The full join has cases where it is useful.One of them is comparing two tables for differences like XOR between tables:

 SELECT * 
 FROM t1
 FULL JOIN t2
   ON t1.id = t2.id
 WHERE t1.id IS NULL 
    OR t2.id IS NULL;

Example:

t1.id ... t2.id
1         NULL
NULL      2

---

you could also achieve this by using two left joins.

Yes you could:

SELECT t1.*, t2.*
FROM t1
LEFT JOIN t2
  ON t1.id = t2.id
WHERE t2.id IS NULL
UNION ALL
SELECT t1.*, t2.*
FROM t2
LEFT JOIN t1
  ON t1.id = t2.id
WHERE t1.id IS NULL;

Some SQL dialects does not support FULL OUTER JOIN and we emulate it that way. Related: How to do a FULL OUTER JOIN in MySQL?

---

On the other hand RIGHT JOIN is useful when you have to join more than 2 tables:

SELECT *
FROM t1
JOIN t2 
  ...
RIGHT JOIN  t3
 ...

Of course you could argue that you could rewrite it to correspodning form either by changing join order or using subqueries(inline views). From developer perspective it is always good to have tools(even if you don’t have to use them)