Join 3 Tables in SQL

Posted on by Ian

In SQL, you can join three tables or more by adding another join after the first one.

You can also run nested joins by specifying one join as the join condition for another.

Syntax

The most common way of joining three tables goes something like this:

SELECT *
FROM Table1 
INNER JOIN Table2
    ON Condition
INNER JOIN Table3
    ON Condition;

This uses an inner join, but you can specify your desired join type as with any other join. You can also combine join types if required (example below).

You can also use nested joins by specifying one join as the join condition for another join. Like this:

SELECT *
FROM Table1 
JOIN (Table2
            JOIN Table3
            ON Condition)
ON Condition;

Sample Data – The 3 Tables

Suppose we have the following three tables.

The Customers table:

+--------------+----------------+----------------+----------------+
| CustomerId   | CustomerName   | PostalCityId   | PhoneNumber    |
|--------------+----------------+----------------+----------------|
| 1            | Homer McKenzie | 19586          | (308) 555-0100 |
| 2            | Marge Pratt    | 33475          | (406) 555-0100 |
| 3            | Vlad Bernanke  | NULL           | (480) 555-0100 |
| 4            | Bart Pitt      | 21692          | (316) 555-0100 |
| 5            | Lisa McQueen   | 12748          | (212) 555-0100 |
| 6            | Steve Simpson  | 17054          | (701) 555-0100 |
| 7            | Vinn Allen     | 12152          | (423) 555-0100 |
| 8            | Veejay Smith   | 3673           | (303) 555-0100 |
| 9            | Kasey Chin     | 23805          | (201) 555-0100 |
| 10           | Borat Lee      | 37403          | (701) 555-0100 |
+--------------+----------------+----------------+----------------+
(10 rows affected)

The Cities table:

+----------+----------------+-------------------+--------------+
| CityId   | CityName       | StateProvinceId   | Population   |
|----------+----------------+-------------------+--------------|
| 3673     | Bow Mar        | 6                 | 866          |
| 12152    | Frankewing     | 44                | NULL         |
| 12748    | Gasport        | 33                | 1248         |
| 21692    | Medicine Lodge | 17                | 2009         |
| 26483    | Peeples Valley | 3                 | 428          |
| 33475    | Sylvanite      | 27                | 103          |
| 17054    | Jessie         | 35                | 25           |
| 19586    | Lisco          | 28                | NULL         |
| 37403    | Wimbledon      | 35                | 216          |
+----------+----------------+-------------------+--------------+
(9 rows affected)

The StateProvinces table:

+-------------------+---------------------+---------------------+-------------+--------------+
| StateProvinceId   | StateProvinceCode   | StateProvinceName   | CountryId   | Population   |
|-------------------+---------------------+---------------------+-------------+--------------|
| 3                 | AZ                  | Arizona             | 230         | 6891688      |
| 6                 | CO                  | Colorado            | 230         | 5698265      |
| 17                | KS                  | Kansas              | 230         | 2893957      |
| 28                | NE                  | Nebraska            | 230         | 1943256      |
| 31                | NJ                  | New Jersey          | 230         | 8899339      |
| 33                | NY                  | New York            | 230         | 20437172     |
| 35                | ND                  | North Dakota        | 230         | 723393       |
| 44                | TN                  | Tennessee           | 230         | 6495978      |
+-------------------+---------------------+---------------------+-------------+--------------+
(8 rows affected)

Example 1 – Inner Join 3 Tables

The most popular join type is the inner join, so we’ll start with that.

Here’s an example of joining the above three tables with two inner joins.

SELECT
    s.StateProvinceName,
    ci.CityName,
    cu.CustomerName
FROM StateProvinces s
INNER JOIN Cities AS ci
ON ci.StateProvinceID = s.StateProvinceID
INNER JOIN Customers cu  
ON cu.PostalCityId = ci.CityId;

Result:

+---------------------+----------------+----------------+
| StateProvinceName   | CityName       | CustomerName   |
|---------------------+----------------+----------------|
| Nebraska            | Lisco          | Homer McKenzie |
| Kansas              | Medicine Lodge | Bart Pitt      |
| New York            | Gasport        | Lisa McQueen   |
| North Dakota        | Jessie         | Steve Simpson  |
| Tennessee           | Frankewing     | Vinn Allen     |
| Colorado            | Bow Mar        | Veejay Smith   |
| North Dakota        | Wimbledon      | Borat Lee      |
+---------------------+----------------+----------------+
(7 rows affected)

Example 2 – Combining Join Types

You can combine join types when joining three or more tables.

Here’s an example of combining an inner join with a left join.

SELECT
    s.StateProvinceName,
    ci.CityName,
    cu.CustomerName
FROM StateProvinces s
INNER JOIN Cities AS ci
ON ci.StateProvinceID = s.StateProvinceID
LEFT JOIN Customers cu  
ON cu.PostalCityId = ci.CityId;

Result:

---------------------+----------------+----------------+
| StateProvinceName   | CityName       | CustomerName   |
|---------------------+----------------+----------------|
| Colorado            | Bow Mar        | Veejay Smith   |
| Tennessee           | Frankewing     | Vinn Allen     |
| New York            | Gasport        | Lisa McQueen   |
| Kansas              | Medicine Lodge | Bart Pitt      |
| Arizona             | Peeples Valley | NULL           |
| North Dakota        | Jessie         | Steve Simpson  |
| Nebraska            | Lisco          | Homer McKenzie |
| North Dakota        | Wimbledon      | Borat Lee      |
+---------------------+----------------+----------------+
(8 rows affected)

In this case, we have a city (Peeples Valley) that doesn’t yet have any customers.

The reason we can now see that information is because the left join returns rows that have data in the left table, even if there’s no matching rows in the left table.

The previous example that combined two inner joins didn’t return this row, because inner joins discard unmatched rows from both tables. It only returns rows when there is at least one row in both tables that match the join condition.

New Sample Data – 3 Different Tables

For the remaining examples, we’ll use the following tables.

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.

Example 3 – Left Join 3 Tables

Let’s do a three-table join using two left joins.

Here’s an example of running two left joins against those tables.

SELECT 
    p.PetName,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Owners o LEFT JOIN Pets p
    ON p.OwnerId = o.OwnerId
LEFT 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 |
+-----------+-----------+----------------+
(9 rows affected)

Here, we have a pet owner that doesn’t have a pet. We can verify that by looking at the Pets.OwnerId column, and seeing that there’s no value that corresponds with Woody Eastwood’s OwnerId in the Owners table.

Example 4 – Right Join 3 Tables

The right join is the opposite of the left join. Here’s an example using the same three tables.

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 an extra pet type (Rabbit), but not the extra owner. This is because right joins return rows that have data in the right table, even if there’s no matching rows in the left table.

By the way, the reason the last PetOwner is not NULL (like last the PetName is) is 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.

Example 5 – Full Join 3 Tables

The full join is like having a left and right join in one. It returns all rows, as long as there’s matching data in one of the 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 get a combination of the results that we got in the previous two examples.

Example 6 – Nested Joins

As mentioned, you can also do nested joins.

Here’s an example of a nested join.

SELECT 
    p.PetName,
    pt.PetType,
    CONCAT(o.FirstName, ' ', o.LastName) AS PetOwner
FROM Owners o 
LEFT JOIN    (Pets p
        LEFT JOIN PetTypes pt
        ON p.PetTypeId = pt.PetTypeId)
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)