Picking the Right JOIN

SQLJOINMidSeniorUpdated Nov 10, 2026

For a report, return every customer along with their order total. Customers who have placed no orders should still appear (with total = 0).

Schema

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

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total       NUMERIC NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

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

LEFT JOIN keeps every customer and pads missing orders with NULL, which COALESCE turns into 0. INNER JOIN (a) drops customers with no orders. RIGHT JOIN (c) keeps every order: fine here since orders.customer_id is NOT NULL, but it does not guarantee customers with no orders are kept. FULL OUTER (d) is overkill and would also keep orphan orders if customer_id were nullable.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

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

Option (c)

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

Option (d)

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