Keep Rows With No Match Using LEFT JOIN

You joined customers to orders and some customers disappeared. An INNER JOIN keeps only rows that match on both sides. To keep a customer who has no orders, use LEFT JOIN. This page shows the difference, the "who has nothing" query that LEFT JOIN makes possible, and the one WHERE mistake that undoes it.

Step 1: two small tables

CREATE TABLE customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL
);
CREATE TABLE orders (
  order_id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers (customer_id),
  status      text NOT NULL
);
INSERT INTO customers (name) VALUES ('Ana Reyes'), ('Ben Cruz'), ('Dan Lim');
INSERT INTO orders (customer_id, status) VALUES (1, 'paid'), (1, 'new'), (2, 'shipped');

CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3

Step 2: see the rows an INNER JOIN drops

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

   name    | order_id
-----------+----------
 Ana Reyes |        1
 Ana Reyes |        2
 Ben Cruz  |        3
(3 rows)

Dan Lim is missing. He has no orders, so there was nothing to match. Now keep him with LEFT JOIN.

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

   name    | order_id
-----------+----------
 Ana Reyes |        1
 Ana Reyes |        2
 Ben Cruz  |        3
 Dan Lim   |
(4 rows)

Every customer from the left table is there. Where no order matched, order_id is NULL, which psql shows as blank.

Step 3: find the rows with no match

The NULL is the clue. Keep only the rows where the right side is empty.

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;

  name
---------
 Dan Lim
(1 row)

Test a column that can never be NULL when a match exists, like the right table's primary key. If you test a column that can be NULL for real rows, you will hide true matches.

Step 4: count with zeros

To show every customer with a count, count a column from the right table. It skips the NULLs, so unmatched customers get 0.

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.customer_id, c.name
ORDER BY c.name;

   name    | orders
-----------+--------
 Ana Reyes |      2
 Ben Cruz  |      1
 Dan Lim   |      0
(3 rows)

count(*) here would give Dan 1, because the NULL-filled row is still a row. Use count(o.order_id).

Step 5: the WHERE that cancels the LEFT JOIN

Filtering on the right table in WHERE removes the NULL rows again.

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.name;

   name    | order_id
-----------+----------
 Ana Reyes |        1
(1 row)

Only Ana is left, so it is an inner join again. To keep every customer and show only paid orders, put the condition in ON.

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.status = 'paid'
ORDER BY c.name;

   name    | order_id
-----------+----------
 Ana Reyes |        1
 Ben Cruz  |
 Dan Lim   |
(3 rows)

Related course: SQL for Beginners.