LEFT JOIN, Self Joins and Finding Missing Data

An inner join drops rows that have no match. Often those are exactly the rows you are looking for.

LEFT JOIN

LEFT JOIN returns every row from the left table. Where there is no match on the right, the right-hand columns are NULL.

SELECT c.name,
       o.order_id,
       o.order_date
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.name;

Every customer appears, including the one who has never ordered.

Finding rows with no match

Filter for NULL in a column from the right table:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

This “left join, then keep the nulls” pattern answers questions such as which customers never bought and which products never sold.

Counting with a LEFT JOIN

To include customers with zero orders in a count, count a column from the right table, not *:

SELECT c.name,
       COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY orders DESC;

COUNT(*) would return 1 for a customer with no orders, because the row still exists.

A filter can undo a LEFT JOIN

A condition on the right table in WHERE removes the NULL rows and turns the query back into an inner join. Put the condition in the ON clause to keep all left rows.

SELECT c.name,
       COUNT(o.order_id) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.status = 'delivered'
GROUP BY c.name
ORDER BY c.name;

Move o.status = 'delivered' to a WHERE clause and run it again: customers with no delivered orders disappear.

Other join types

Join	Returns
INNER JOIN	Only matching rows
LEFT JOIN	All rows from the left table
RIGHT JOIN	All rows from the right table
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Every combination of rows

A right join is a left join with the tables swapped, so most people write only left joins. A cross join is useful for generating combinations, such as every product for every city:

SELECT DISTINCT c.city, p.category
FROM customers AS c
CROSS JOIN products AS p
WHERE c.city IS NOT NULL
ORDER BY c.city, p.category;

Self join

A table can be joined to itself by giving it two aliases. This finds pairs of customers in the same city:

SELECT a.name AS customer_1,
       b.name AS customer_2,
       a.city
FROM customers AS a
JOIN customers AS b
  ON a.city = b.city
 AND a.customer_id < b.customer_id;

The condition a.customer_id < b.customer_id stops a customer being paired with themselves and stops each pair appearing twice.

Practice

Find the products that have never been ordered.