SQL LEFT JOIN: Keeping All Rows from the Left Table

Lesson 5.3 · Reading time: ~7 min · Practice tasks ↓

The SQL LEFT JOIN returns every row of the left table and the matching rows of the right table; where there is no match, the columns of the right table are NULL. In this lesson you'll learn the LEFT JOIN syntax, how to find rows without a match, and the WHERE mistake that silently turns a LEFT JOIN into an INNER JOIN.

INNER JOIN drops rows without a match. Often those are exactly the rows you need: films with no copies in stock, customers with no orders this month, products that were never sold. LEFT JOIN keeps them.

What LEFT JOIN Returns

• Every row of the left table (the one after FROM) appears in the result at least once.
• If a row has matches in the right table, it is combined with each of them, as in INNER JOIN.
• If a row has no match, it appears once, and all columns of the right table are NULL.

LEFT JOIN and LEFT OUTER JOIN are the same; the word OUTER is optional.

Basic Syntax

SELECT left_table.column1, right_table.column2
FROM left_table
LEFT JOIN right_table ON left_table.key_column = right_table.key_column;

Films and Their Copies

Sakila has 1000 films, but not all of them are in stock. With LEFT JOIN, films without copies stay in the result:

SELECT f.film_id, f.title, i.inventory_id, i.store_id
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
ORDER BY i.inventory_id IS NULL DESC, f.title
LIMIT 10;

Result: films without copies come first; their inventory_id and store_id are NULL.

With INNER JOIN the same query would lose these 42 films.

Finding Rows Without a Match

To find only the rows without a match, add a WHERE condition that checks a right-table column for NULL. Check the right table's key column: it is NULL only when no row was matched.

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
WHERE i.inventory_id IS NULL
ORDER BY f.title;

Result: the 42 films that have no copies in any store.

This pattern is called an anti-join. It answers "what is missing" questions. Another example, copies that have never been rented:

SELECT i.inventory_id, f.title, i.store_id
FROM inventory AS i
INNER JOIN film AS f ON f.film_id = i.film_id
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE r.rental_id IS NULL;

Result: one copy that has never been rented.

The WHERE Trap

A filter on the right table placed in WHERE removes the rows where that table's columns are NULL, so the LEFT JOIN behaves like an INNER JOIN. Compare two queries: both should list all customers with their payments made on or after August 23, 2005.

-- Wrong: customers without such payments disappear
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
WHERE p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

-- Right: the condition is part of the join
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
                      AND p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

Result: the first query returns only customers who paid in that period. The second keeps every one of the 599 customers; those without late-August payments have NULL in amount and payment_date.

The rule: conditions on the right table that should limit the matches go into ON. Conditions on the left table, which should remove rows from the result, go into WHERE. The only right-table condition that belongs in WHERE is IS NULL for an anti-join.

Table Order Matters

In INNER JOIN, swapping the tables doesn't change the result. In LEFT JOIN it does: the table after FROM is the one whose rows are all kept. film LEFT JOIN inventory keeps all films; inventory LEFT JOIN film keeps all copies, and every copy has a film, so the films without copies are lost.

Key Takeaways:

• LEFT JOIN keeps every row of the left table; unmatched rows get NULL in the right table's columns.
• LEFT JOIN and LEFT OUTER JOIN are the same.
• LEFT JOIN ... WHERE right_key IS NULL finds rows without a match (an anti-join).
• A filter on the right table in WHERE turns a LEFT JOIN into an INNER JOIN; put it into ON.
• The table after FROM is the one whose rows are kept.

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN also returns the rows of the left table without a match, filling the right table's columns with NULL.

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. OUTER is optional, and both forms return the same result in every database.

Why does my LEFT JOIN work like an INNER JOIN?

Most likely the WHERE clause has a condition on a column of the right table. For unmatched rows that column is NULL, the condition is not true, and the rows are removed. Move the condition into the ON clause.