Brightlane's operations team wants a list of every customer alongside their cancelled orders. Every customer must appear, including those without any cancelled order on record.
Write a query to return each customer alongside any cancelled order they have placed.
Assumptions:
- The
customerstable has one row per customer with aname. - The
orderstable has one row per order, linked to a customer bycustomer_idand carrying astatus. A cancelled order hasstatus = 'cancelled'. - Customers with one or more cancelled orders contribute one row per cancelled order. Customers with no cancelled orders (including customers with no orders at all) contribute a single row with a missing
cancelled_order_id.
Output:
- One row per customer-cancelled-order pairing, plus one row per customer with no cancelled orders, with columns
nameandcancelled_order_id.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
c.name,
o.id AS cancelled_order_id
FROM
customers c
LEFT JOIN orders o ON c.id = o.customer_id
AND o.status = 'cancelled'The shape
Moving status = 'cancelled' from the WHERE clause into the ON clause is what makes the LEFT JOIN actually preserve every customer. The condition becomes part of the matching rule rather than a post-join filter, so a customer with no cancelled order keeps their seat in the result with a missing cancelled_order_id.
Clause by clause
SELECT c.name, o.id AS cancelled_order_idreturns each customer's name alongside the ID of the cancelled order attached on that row. When the join attached nothing,o.idis missing — which is exactly how the report signals "no cancelled order on record" for that customer.FROM customers c LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'cancelled'pairs each customer with their cancelled orders only. TheONclause now has two conjoined conditions: the customer-to-order link, and the cancelled-status restriction. The join attaches an order only when both are true; otherwise the customer still appears, with everyo.* column missing.
Why this and not WHERE o.status = 'cancelled'
A WHERE filter on a right-side column silently converts the LEFT JOIN back into an inner join. Customers with no orders at all would have every o.* column missing, including o.status, and NULL = 'cancelled' is not true — so those customers fail the filter and disappear. Customers with only non-cancelled orders are also dropped, because every one of their attached rows has o.status set to something other than 'cancelled'. The WHERE would leave only customers who had at least one cancelled order, which is not the population the operations team asked for.
The trap
The two placements look equivalent until you trace what happens to the unmatched rows. The ON condition runs during the matching step, so a failed match leaves a customer with a missing right-side and the row survives. The WHERE runs after the join produces its output, by which point the unmatched rows already exist and the missing-value comparison eliminates them. Same predicate, different timing, different result set.
You practiced moving a right-side condition into the ON clause — every left record stays, matching right rows attach, and non-matches yield a missing value rather than dropping the left row.
