How do you approach SQL: Customers Without Orders (LEFT JOIN ... IS NULL)?

LEFT JOIN keeps every customer; missing orders show up as NULL on the order side.

What are common mistakes on SQL: Customers Without Orders (LEFT JOIN ... IS NULL)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

Sitelet https://gitgood.dev/coding/sql-left-join-no-orders

gitGood.dev

← Back to problems

SQL: Customers Without Orders (LEFT JOIN ... IS NULL)

EasyDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution.

Given:

Customers(id, name)
Orders(id, customer_id, total)

Return the names of customers who have never placed an order, sorted alphabetically. The classic SQL pattern is LEFT JOIN ... WHERE o.id IS NULL (the "anti-join").

For the auto-grader: implement customersWithoutOrders(customers, orders).

Example 1

Input: customers = [{id:1,name:"Alice"},{id:2,name:"Bob"}], orders = [{id:10,customer_id:1,total:5}]

Output: ["Bob"]

Constraints

- 0 <= customers.length, orders.length <= 10^4

SQL: Customers Without Orders (LEFT JOIN ... IS NULL): approach, complexity and pitfalls

An easy sql problem, graded against 7 test cases (5 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 3-step hint ladder

LEFT JOIN keeps every customer; missing orders show up as NULL on the order side.
Filter to rows where the joined order id is NULL - those are the customers with no orders.
An equivalent pattern is `WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id)`.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT c.name

FROM Customers c

LEFT JOIN Orders o ON o.customer_id = c.id

WHERE o.id IS NULL

ORDER BY c.name;

```

`LEFT JOIN` keeps every row from `Customers`. Customers with no matching order get `NULL` columns from `Orders`. Filtering `o.id IS NULL` isolates those rows. Equivalents: `NOT EXISTS`, `NOT IN` (beware of NULL trap with NOT IN).

Performance note: `NOT EXISTS` is usually the most reliably-optimized form on modern engines (Postgres, SQL Server). `NOT IN` returns no rows if the subquery contains a NULL.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.