LEFT JOIN and NULL: finding what is missing

Keep every row of a table with LEFT JOIN, spot absences with IS NULL and replace them with COALESCE.

8 min · 3 questions

The INNER JOIN of the previous lesson hides customers with no orders. Yet the most frequent business question is precisely: who is missing? Inactive customers, products never sold, invoices with no payment... Run this script in sqliteonline.com or sqlite3: it adds a fourth customer with no orders.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Alice', 'Lyon'),
  (2, 'Karim', 'Paris'),
  (3, 'Mina', 'Nantes'),
  (4, 'Paulo', 'Lille');

INSERT INTO orders (id, customer_id, amount)
VALUES
  (1, 1, 49.90),
  (2, 1, 15.00),
  (3, 2, 120.50);

LEFT JOIN keeps all the rows of the left-hand table (the one named before the keyword), even without a match on the right. When the match is missing, the columns of the right-hand table are filled with NULL.

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

To isolate what is missing, filter on these NULL values: this is the anti-join pattern, an essential reflex in data analysis. The tested column must come from the right-hand table and must never be NULL naturally, hence the choice of its primary key o.id.

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

One last tool: COALESCE(a, b) returns a if it is not NULL, otherwise b. Combined with a LEFT JOIN and an aggregation, it turns absences into usable zeros in a report or an invoice.

SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total_spent DESC;

Knowledge check

Make sure you remember the key points of this lesson.

1. Which combination lists the customers who have never ordered?
   • INNER JOIN then WHERE o.id IS NULL
   • LEFT JOIN then WHERE o.id IS NULL
   • LEFT JOIN then WHERE o.id = NULL
2. What does the condition amount = NULL return?
   • True for the rows where amount is NULL
   • Never true: the comparison stays undefined
   • A syntax error
3. What does COALESCE(SUM(o.amount), 0) return for a customer with no orders?
   • 0
   • NULL
   • The customer is excluded from the result
   • An aggregation error