LEFT JOIN and finding gaps

Why it matters

Some of the most valuable questions are about what is missing: customers who never ordered, products that never sold, students not enrolled in anything, servers with no recent backup. An inner join cannot answer them, because it throws away unmatched rows. A LEFT JOIN keeps them, and a simple IS NULL test then finds the gaps.

LEFT JOIN keeps every row from the left table

Using the same tables as the previous lesson:

customers: Ana (1), Ben (2), Chen (3). orders: 101 and 102 for Ana, 103 for Ben.

SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Every customer appears. Where there is a match, you get the order columns. Where there is none, the order columns are filled with NULL. "Left" refers to the table written first (before LEFT JOIN); that is the table whose rows are all kept.

Finding rows with no match

Because unmatched rows have NULL in the right table's columns, filter for that:

SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

This pattern is sometimes called an anti-join. Test a column that can never be NULL in a real match, such as the right table's primary key (o.id). If you tested a nullable column, such as an optional o.discount_code, real orders without a code would be counted as "no order".

An equivalent form uses NOT EXISTS, which some people find easier to read:

SELECT c.name, c.city
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Avoid WHERE c.id NOT IN (SELECT customer_id FROM orders) when customer_id can be NULL: a single NULL in the list makes NOT IN return no rows at all, the trap from the NULLs lesson.

Counting including zeros

LEFT JOIN with GROUP BY gives totals that include customers with nothing:

SELECT c.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Chen gets 0 orders and 0 revenue. Two details matter:

• Use COUNT(o.id), not COUNT(*). COUNT(*) counts Chen's single row of NULLs as 1.
• SUM of only NULLs is NULL, so wrap it in COALESCE(..., 0).

WHERE vs ON in a LEFT JOIN

Where you put a filter on the right table changes the result:

-- Filter in WHERE: customers without a 2024 order vanish
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';

The WHERE runs after the join, and Chen's NULL date is not >= '2024-01-01', so Chen is dropped. The query has quietly become an inner join.

-- Filter in ON: every customer kept, only 2024 orders attached
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.order_date >= '2024-01-01';

Rule of thumb: conditions on the right table go in ON when you want to keep all left rows. Conditions on the left table go in WHERE.

Other outer joins

• RIGHT JOIN keeps every row from the right table. It is a LEFT JOIN with the tables swapped, so most people just reorder and use LEFT JOIN.
• FULL OUTER JOIN keeps unmatched rows from both sides. It is useful when reconciling two lists, such as comparing an export with a database table.

SQLite added RIGHT and FULL OUTER JOIN in version 3.39 (2022); older SQLite versions and MySQL do not have FULL OUTER JOIN.

Worked example: products that never sold

SELECT p.id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

A merchandising team might discount these, and a database administrator might check whether they are test data that should be cleaned up.

Common mistakes

• Testing a nullable column for IS NULL in the anti-join. Test the right table's key.
• Filtering the right table in WHERE, which turns the left join back into an inner join.
• COUNT(*) with a left join, which counts unmatched rows as 1.
• NOT IN with a list that may contain NULL.
• Putting the tables in the wrong order. The table you want to keep in full comes first.

Quick recap

• LEFT JOIN keeps every left row; unmatched right columns are NULL.
• LEFT JOIN ... WHERE right.key IS NULL finds rows with no match.
• Count with COUNT(right.key) and wrap sums in COALESCE to include zeros.
• Filters on the right table belong in ON if you want to keep all left rows.

Try it

Open the Do tab and find the customers who have never placed an order.