SQL Left Join

by

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

The LEFT JOIN, or LEFT OUTER JOIN, returns rows that have data in the left table (left of the JOIN keyword), even if there’s no matching rows in the right table.

Syntax

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

Using the LEFT JOIN syntax:

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

Using the LEFT OUTER JOIN syntax:

SELECT *
FROM Table1 LEFT 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 Left Join Query

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

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

The left join causes us to get a PetType value that doesn’t correspond with a PetName. There are no rabbits as pets. But the left 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 left table (i.e. left of the LEFT JOIN keywords). OK, my formatting makes it more “above” than “left”, but you get the picture.

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

SELECT 
    p.PetName,
    pt.PetType
FROM Pets p
LEFT JOIN PetTypes pt
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 right side of the join.

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

Left Join on 3 Tables

Here’s an example of performing a left join on all three 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)

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 PetTypes pt LEFT JOIN Pets p
    ON p.PetTypeId = pt.PetTypeId
LEFT 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    |               |
+-----------+-----------+---------------+
(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.