SQL LEFT JOIN Explained: Keep Customers with No Orders

Sometimes the missing activity is the point of a report. You may want every customer, including people who have never ordered. SQL LEFT JOIN preserves those customers, but a misplaced filter or the wrong count can quietly change the answer.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Create a no-order customer without changing the database

All three customers in the original lab have orders. For this lesson, a CTE adds a fictional fourth customer to the query input. Copy the WITH section each time you run a query that uses all_customers. It lasts for one statement only.

WITH all_customers AS (
  SELECT customer_id, name, city FROM customers
  UNION ALL
  SELECT 4, 'Dalia', 'Kisumu'
)
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM all_customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

You get five rows: two for Amina, one each for Brian and Chao, and one for Dalia. Dalia's order_id and amount are NULL because no order matched. LEFT JOIN keeps every row from the left input and fills missing right-side values with NULL.

Count matching orders, not preserved rows

A common mistake is using COUNT(*) when the goal is a count of orders. Dalia still has one result row, so COUNT(*) would say 1. Count a non-NULL order key instead:

WITH all_customers AS (
  SELECT customer_id, name, city FROM customers
  UNION ALL
  SELECT 4, 'Dalia', 'Kisumu'
)
SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_amount
FROM all_customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

The counts and totals are Amina 2 and 1800, Brian 1 and 800, Chao 1 and 1500, Dalia 0 and 0. COALESCE supplies a display total of zero for a customer with no matched amounts. It does not change the stored data.

Put a matching rule in the right place

Suppose the report must list every customer and show only orders worth at least 1000. Add AND o.amount >= 1000 to the ON condition. The customers remain, while the set of qualifying matches becomes smaller.

If you put WHERE o.amount >= 1000 after the join instead, Brian's and Dalia's rows disappear. Their right-side amount is NULL in the filtered join result, and the WHERE comparison is not true. The output no longer preserves every customer.

This is not a rule that WHERE is always wrong after LEFT JOIN. A condition on c.city, for example, can intentionally limit which customers you want. The important question is whether your filter should remove a customer or only decide which orders count as matches.

Find customers with no matches

After a LEFT JOIN, WHERE o.order_id IS NULL identifies unmatched customers in this example. order_id is a non-NULL primary key in stored orders, so its absence reliably signals that no order row matched. Avoid testing an optional right-side field that could be NULL even on a real matched row.

Try it yourself

Use all four customers. Count orders worth at least 1000 for each customer, keeping customers with zero qualifying orders. Return customer_id, name and qualifying_order_count. Predict Brian's count before running the query.

WITH all_customers AS (
  SELECT customer_id, name, city FROM customers
  UNION ALL
  SELECT 4, 'Dalia', 'Kisumu'
)
SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS qualifying_order_count
FROM all_customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
 AND o.amount >= 1000
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.