SQLMaxx
Sign in Start free
N019-M3 Tier 2 · Core SQL · medium ecommerce · Brightlane

Return the names of every customer who appears in the customer table but has no matching order

Part of FULL OUTER JOIN in SQL

The problem

Brightlane's sales team wants to identify customers who have no orders on record.

Write a query to return the names of every customer who appears in the customer table but has no matching order.

Assumptions:

  • The customers table contains every customer Brightlane has on file.
  • The orders table contains every order Brightlane has processed.
  • A customer with no orders has no row in orders carrying their id as customer_id.

Output:

  • One row per customer with no orders, with a single column customer_name.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  c.name AS customer_name
FROM
  customers c
  FULL OUTER JOIN orders o ON c.id = o.customer_id
WHERE
  o.id IS NULL

The shape

WHERE o.id IS NULL after a FULL OUTER JOIN keeps only the customers whose id matched no row in orders. The orders side comes back fully NULL-padded on those rows, and the filter latches onto that pattern. Eight customers — Omar Jensen, Mark Hayes, Nina Irwin, and five more — have no order history on file.

Clause by clause

  • SELECT c.name AS customer_name returns just the customer name. Everything else the join carries is scaffolding for the filter.
  • FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id runs the reconciliation. Customers who have placed orders pair up with each of their orders; customers with no orders are still kept, with the orders side NULL-padded; any order whose customer_id doesn't resolve is kept too, with the customers side NULL-padded.
  • WHERE o.id IS NULL runs after the join and keeps only the rows where the orders side came back empty. Matched rows have a real o.id and drop out. Orphan-order rows have a real o.id too (and a NULL customer name) and drop out. What's left is one row per customer with no orders — exactly the sales team's no-order list.

Why filter on o.id and not on o.customer_id

Reaching for WHERE o.customer_id IS NULL looks similar but tests the wrong column. An orphan order — an order row whose customer_id happened to be NULL in the source data — would slip through that filter even though it isn't a customer at all. Filtering on o.id is safer because id is the orders primary key; it's NULL only on rows the outer join padded in, never on real order rows. The general rule: the anti-join's IS NULL filter belongs on a column that can only be NULL because the join didn't match.

You practiced an anti-join from the customers side. The same answer comes out of a LEFT JOIN anchored on customers (taught in N018) or a FULL OUTER JOIN followed by an IS NULL filter on the orders side — different scaffolds, identical result.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.