Does anyone use Right Outer Joins?

Question

I use INNER JOIN and LEFT OUTER JOINs all the time. However, I never seem to need RIGHT OUTER JOINs, ever.

I've seen plenty of nasty auto-generated SQL that uses right joins, but to me, that code is impossible to get my head around. I always need to rewrite it using inner and left joins to make heads or tails of it.

Does anyone actually write queries using Right joins?

Answer 1

To give one example where a RIGHT JOIN may be useful.

Suppose that there are three tables for People, Pets, and Pet Accessories. People may optionally have pets and these pets may optionally have accessories

CREATE TABLE Persons   (      PersonName VARCHAR(10) PRIMARY KEY   );  INSERT INTO Persons VALUES      ('Alice'),             ('Bob'),             ('Charles');  CREATE TABLE Pets   (      PetName    VARCHAR(10) PRIMARY KEY,      PersonName VARCHAR(10)   );  INSERT INTO Pets VALUES      ('Rover',              'Alice'),             ('Lassie',              'Alice'),             ('Fifi',              'Charles');  CREATE TABLE PetAccessories   (      AccessoryName VARCHAR(10) PRIMARY KEY,      PetName       VARCHAR(10)   );  INSERT INTO PetAccessories VALUES      ('Ball', 'Rover'),             ('Bone', 'Rover'),             ('Mouse','Fifi'); 

If the requirement is to get a result listing all people irrespective of whether or not they own a pet and information about any pets they own that also have accessories.

This doesn't work (Excludes Bob)

SELECT P.PersonName,        Pt.PetName,        Pa.AccessoryName FROM   Persons P        LEFT JOIN Pets Pt          ON P.PersonName = Pt.PersonName        INNER JOIN PetAccessories Pa          ON Pt.PetName = Pa.PetName;  

This doesn't work (Includes Lassie)

SELECT P.PersonName,        Pt.PetName,        Pa.AccessoryName FROM   Persons P        LEFT JOIN Pets Pt          ON P.PersonName = Pt.PersonName        LEFT JOIN PetAccessories Pa          ON Pt.PetName = Pa.PetName;  

This does work (but the syntax is much less commonly understood as it requires two ON clauses in succession to achieve the desired logical join order)

SELECT P.PersonName,        Pt.PetName,        Pa.AccessoryName FROM   Persons P        LEFT JOIN Pets Pt                   INNER JOIN PetAccessories Pa                     ON Pt.PetName = Pa.PetName          ON P.PersonName = Pt.PersonName; 

All in all probably easiest to use a RIGHT JOIN

SELECT P.PersonName,        Pt.PetName,        Pa.AccessoryName FROM   Pets Pt        JOIN PetAccessories Pa          ON Pt.PetName = Pa.PetName        RIGHT JOIN Persons P          ON P.PersonName = Pt.PersonName; 

Though if determined to avoid this another option would be to introduce a derived table that can be left joined to

SELECT P.PersonName,        T.PetName,        T.AccessoryName FROM   Persons P        LEFT JOIN (SELECT Pt.PetName,                          Pa.AccessoryName,                          Pt.PersonName                   FROM   Pets Pt                          JOIN PetAccessories Pa                            ON Pt.PetName = Pa.PetName) T          ON T.PersonName = P.PersonName;  

SQL Fiddles: MySQL, PostgreSQL, SQL Server