Does "Right Outer Join" have any useful purpose?

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

In SQL Server one edge case where I have found right joins useful is when used in conjunction with join hints.

The following queries have the same semantics but differ in which table is used as the build input for the hash table (it would be more efficient to build the hash table from the smaller input than the larger one which the right join syntax achieves)

SELECT #Large.X
FROM #Small 
RIGHT HASH JOIN #Large ON #Small.X = #Large.X
WHERE #Small.X IS NULL

SELECT #Large.X
FROM #Large  
LEFT HASH JOIN #Small ON #Small.X = #Large.X
WHERE #Small.X IS NULL 

Aside from that (product specific) edge case there are other general examples 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

Answer 2

It depends on what side of the join you put each table.

If you want to return all rows from the left table, even if there are no matches in the right table... you use left join.

If you want to return all rows from the right table, even if there are no matches in the left table, you use right join.

Interestingly enough, I rarely used right joins.

Answer 3

You usually use RIGHT OUTER JOINS to find orphan items in other tables.

Answer 4

No, I don't for the simple reason I can accomplish everything with inner or left joins.

Answer 5

I only use left, but let me say they are really the same depending how how you order things. I worked with some people that only used right, becasue they built queries from the inside out and liked to keep their main items at the bottom thus in their minds it made sense to only use right.

I.e.

Got main thing here

need more junk

More Junk right outer join Main Stuff

I prefer to do main stuff then junk... So left outer works for me.

So whatever floats your boat.

Answer 6

The only time I use a Right outer join is when I am working on an existing query and need to change it (normally from an inner). I could reverse the join and make it a left and probably be ok, but I try and reduce the amount of things I change when I modify code.

Answer 7

Our standard practice here is to write everything in terms of LEFT JOINs if possible. We've occasionally used FULL OUTER JOINs if we've needed to, but never RIGHT JOINs.

Answer 8

You can accomplish the same thing using LEFT or RIGHT joins. Generally most people think in terms of a LEFT join probably because we read from left to right. It really comes down to being consistent. Your team should focus on using either LEFT or RIGHT joins, not both, as they are essentially the same exact thing, written differently.

Answer 9

Rarely, as stated you can usually reorder and use a left join. Also I naturally tend to order the data, so that left joins work for getting the data I require. I think the same can be said of full outer and cross joins, most people tend to stay away from them.