SQL Right Join

Posted on by Ian

This article provides an overview of the RIGHT JOIN in SQL, as well as some basic examples.

Also known as the RIGHT OUTER JOIN, the RIGHT JOIN returns rows that have data in the right table (right of the JOIN keyword), even if there’s no matching rows in the left table.

Syntax

You specify a right join in the FROM clause. You can use either the RIGHT JOIN or RIGHT OUTER JOIN syntax.

Using the RIGHT JOIN syntax:

SELECT *
FROM Table1 RIGHT JOIN Table2 
ON Table1.Column = Table2.Column;

Using the RIGHT OUTER JOIN syntax:

SELECT *
FROM Table1 RIGHT OUTER JOIN Table2 
ON Table1.Column = Table2.Column;

Both of these do exactly the same thing. It’s just that the OUTER keyword is optional.

Examples

Here are some examples to demonstrate.

Sample Data

First, here are the tables we’ll use for the examples.

The PetTypes table:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
| 2           | Cat       |
| 3           | Dog       |
| 4           | Rabbit    |
+-------------+-----------+
(4 rows affected)

The Pets table:

+---------+-------------+-----------+-----------+------------+
| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |
|---------+-------------+-----------+-----------+------------|
| 1       | 2           | 3         | Fluffy    | 2020-11-20 |
| 2       | 3           | 3         | Fetch     | 2019-08-16 |
| 3       | 2           | 2         | Scratch   | 2018-10-01 |
| 4       | 3           | 3         | Wag       | 2020-03-15 |
| 5       | 1           | 1         | Tweet     | 2020-11-28 |
| 6       | 3           | 4         | Fluffy    | 2020-09-17 |
| 7       | 3           | 2         | Bark      | NULL       |
| 8       | 2           | 4         | Meow      | NULL       |
+---------+-------------+-----------+-----------+------------+
(8 rows affected)

The Owners table:

+-----------+-------------+------------+----------------+-------------------+
| OwnerId   | FirstName   | LastName   | Phone          | Email             |
|-----------+-------------+------------+----------------+-------------------|
| 1         | Homer       | Connery    | (308) 555-0100 | [email protected] |
| 2         | Bart        | Pitt       | (231) 465-3497 | [email protected]  |
| 3         | Nancy       | Simpson    | (489) 591-0408 | NULL              |
| 4         | Boris       | Trump      | (349) 611-8908 | NULL              |
| 5         | Woody       | Eastwood   | (308) 555-0112 | [email protected] |
+-----------+-------------+------------+----------------+-------------------+

Note that:

  • The PetTypeId column of the Pets table is a foreign key of the PetTypeId of the PetTypes table (which is the primary key of that table).
  • The OwnerId column of the Pets table is a foreign key of the OwnerId column of the Owners table.

The Right Join Query

Here’s an example of performing a right join against two of those tables.

SELECT 
    p.PetName,
    pt.PetType
FROM Pets p
RIGHT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId;

Result:

+-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Tweet     | Bird      |
| Fluffy    | Cat       |
| Scratch   | Cat       |
| Meow      | Cat       |
| Fetch     | Dog       |
| Wag       | Dog       |
| Fluffy    | Dog       |
| Bark      | Dog       |
| NULL      | Rabbit    |
+-----------+-----------+
(9 rows affected)

The right join causes us to get a PetType value that doesn’t correspond with a PetName. Specifically, there are no rabbits as pets. But the right join causes Rabbit to be returned, even though there’s no pet in the Pets table of that type. This results in a NULL value in the PetName column against Rabbit.

This only happened because Rabbit was in the right table (i.e. right of the RIGHT JOIN keywords).

Here’s what happens if we switch the table order in our query.

SELECT 
    p.PetName,
    pt.PetType
FROM PetTypes pt
RIGHT JOIN Pets p
ON p.PetTypeId = pt.PetTypeId;

Result:

+-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Fluffy    | Cat       |
| Fetch     | Dog       |
| Scratch   | Cat       |
| Wag       | Dog       |
| Tweet     | Bird      |
| Fluffy    | Dog       |
| Bark      | Dog       |
| Meow      | Cat       |
+-----------+-----------+
(8 rows affected)

This time Rabbits wasn’t returned. That’s because its table (PetTypes) was on the left side of the join.

We would need to change it to a left join or a full join if we wanted Rabbits to be returned using this table order.

Right Join on 3 Tables

Here’s an example of performing a right join on all three tables.

SELECT 
    p.PetName,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Pets p RIGHT JOIN PetTypes pt 
    ON p.PetTypeId = pt.PetTypeId
RIGHT JOIN Owners o 
    ON p.OwnerId = o.OwnerId;

Result:

+-----------+-----------+----------------+
| PetName   | PetType   | PetOwner       |
|-----------+-----------+----------------|
| Tweet     | Bird      | Homer Connery  |
| Scratch   | Cat       | Bart Pitt      |
| Bark      | Dog       | Bart Pitt      |
| Fluffy    | Cat       | Nancy Simpson  |
| Fetch     | Dog       | Nancy Simpson  |
| Wag       | Dog       | Nancy Simpson  |
| Fluffy    | Dog       | Boris Trump    |
| Meow      | Cat       | Boris Trump    |
| NULL      | NULL      | Woody Eastwood |
+-----------+-----------+----------------+
(9 rows affected)

This time we have a pet owner that doesn’t have a pet.

We could again shuffle the ordering of the tables around, and we’d get a different outcome.

SELECT 
    p.PetName,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Pets p RIGHT JOIN Owners o 
    ON p.OwnerId = o.OwnerId
RIGHT JOIN PetTypes pt 
    ON p.PetTypeId = pt.PetTypeId;

Result:

+-----------+-----------+---------------+
| PetName   | PetType   | PetOwner      |
|-----------+-----------+---------------|
| Tweet     | Bird      | Homer Connery |
| Fluffy    | Cat       | Nancy Simpson |
| Scratch   | Cat       | Bart Pitt     |
| Meow      | Cat       | Boris Trump   |
| Fetch     | Dog       | Nancy Simpson |
| Wag       | Dog       | Nancy Simpson |
| Fluffy    | Dog       | Boris Trump   |
| Bark      | Dog       | Bart Pitt     |
| NULL      | Rabbit    |               |
+-----------+-----------+---------------+
(9 rows affected)

This time we got the extra pet type (Rabbit), but not the extra owner.

If you’re wondering why the last PetOwner is not NULL (like last the PetName is), it’s because it’s a result of a string concatenation. I used the T-SQL CONCAT() function to concatenate the owner’s first and last names.