SQLMaxx
Sign in Start free
N018-E1 Tier 2 · Core SQL · easy ecommerce · Brightlane

Return each customer's name, order ID, and order total. Order columns will be missing for customers who have placed no orders

Part of LEFT JOIN and RIGHT JOIN in SQL

The problem

Brightlane's customer success team needs a list of every registered customer alongside any orders they have placed. Customers who have not yet placed any orders must still appear in the list.

Write a query to return each customer's name, order ID, and order total. Order columns will be missing for customers who have placed no orders.

Assumptions:

  • The customers table contains every customer Brightlane has on file.
  • The orders table contains every order; customer_id on each order points to a customer.
  • Some customers have placed no orders. Those customers must still appear in the result, with the order columns missing.

Output:

  • One row per customer-order pair, plus one row per customer with no orders, with columns name, order_id, and total_amount. Order columns will be missing for customers with no orders.
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,
  o.id AS order_id,
  o.total_amount
FROM
  customers c
  LEFT JOIN orders o ON c.id = o.customer_id

The shape

customers is the anchor — every customer has to appear, with or without an order. A LEFT JOIN to orders keeps every left-side row and fills the order columns with NULL for the eight customers who have placed none.

Clause by clause

  • SELECT c.name, o.id AS order_id, o.total_amount returns the customer's name from the left table, the order ID aliased to a domain-readable header, and the order total. For customers with no orders, the two o.* columns come back as NULL.
  • FROM customers c is the left table — the anchor. One row minimum per customer, regardless of order history.
  • LEFT JOIN orders o ON c.id = o.customer_id pairs each customer with every order they have placed. Customers with multiple orders show up once per order; customers with no orders still appear once, with NULL on the right-table columns. That NULL is the load-bearing fact — it's how an unmatched left row signals "no match found."

Why this and not INNER JOIN

INNER JOIN would only return matched customer-order pairs. The eight customers with no orders — Omar Jensen, Mark Hayes, Nina Irwin, and the rest — would disappear silently. Customer success explicitly needs those eight in the list; they're the prospects for re-engagement. LEFT JOIN is the join that admits unmatched left rows, and the NULL in order_id is what makes them identifiable in the output.

You practiced a LEFT JOIN to preserve every row from the left table. The recurring shape: when the report's row set is anchored on the left table ("every customer") and the right table only enriches it, LEFT JOIN keeps the unmatched left rows and fills the right-table columns with NULL.

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.