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.
Advertisement
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)