When to use right join or full outer join

Question

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.

**Right outer joins** are completely superfluous in my opinion. You can always write the same query with left outer joins and, wow, sudenly the query becomes readable :-) My personal recommendation is to **never** use right outer joins. — Thorsten Kettner, Mar 21 '20 at 10:59
**Full outer joins** are very rarely needed. But in those rare cases where they *are* the appropriate method, they should be used. Emulating them with left outer joins in both directions and `UNION` to combine the results are kind of awkward and by far less readable and maintainable. — Thorsten Kettner, Mar 21 '20 at 11:02
Does this answer your question? [What is the purpose (or use case) for an outer join in SQL?](https://stackoverflow.com/questions/1101343/what-is-the-purpose-or-use-case-for-an-outer-join-in-sql) — philipxy, Mar 25 '20 at 12:43

Lukasz Szozda · Accepted Answer · 2020-03-21T08:49:44.583

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)

Great. Thanks four your comments. So we can indeed use normal joins and left joins only. Wish someone told me that years ago ;) — osanger, Mar 21 '20 at 09:24

When to use right join or full outer join

1 Answers1