SQL Full Join

Posted on by Ian

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

The SQL FULL JOIN (or FULL OUTER JOIN) returns all rows, as long as there’s matching data in one of the tables.

It’s like having both a left right join in one join.

Syntax

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

Using the FULL JOIN syntax:

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

Using the FULL OUTER JOIN syntax:

SELECT *
FROM Table1 FULL 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 Full Join Query

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

SELECT 
    p.PetName,
    pt.PetType
FROM Pets p
FULL 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)

In this example, we get a PetType value that doesn’t correspond with a PetName. This is because there are no rabbits as pets. But the full 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 is the same result that we would have got if we’d used a right join, because the PetTypes table is to the right of the JOIN keyword. This wouldn’t have happened with a left join, because PetTypes table isn’t on the left of the JOIN keyword. If we wanted to recreate it with a left join, we would have to switch the ordering of the tables, so that the PetTypes table was on the left of the JOIN keyword.

Here’s what happens if we switch the table order in our query when using a full join.

SELECT 
    p.PetName,
    pt.PetType
FROM PetTypes pt
FULL JOIN Pets p
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)

We get exactly the same result. This is because the full join returns all rows, as long as there’s matching data in one of the tables. As mentioned, it’s like having a left and right join in one join.

Full Join on 3 Tables

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

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

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 |
| NULL      | Rabbit    |                |
+-----------+-----------+----------------+
(10 rows affected)

This time we have a pet owner that doesn’t have a pet, as well as a pet type that’s not assigned to a pet.

If we shuffle the ordering of the tables around, we get the same outcome, although the rows are listed in a different order.

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

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    |                |
| NULL      | NULL      | Woody Eastwood |
+-----------+-----------+----------------+
(10 rows affected)

And if we shuffle them once again, we still get the same result.

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

Result:

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

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.