Brightlane's regional team is reviewing the Canadian customer base alongside any purchases on record.
Write a query to return each Canadian customer's name alongside their order ID and order total. Canadian customers who have not yet placed any orders should still appear, with the order columns missing.
Assumptions:
- The
customerstable contains every customer Brightlane has on file; Canadian customers are identified bycountry = 'CA'. - The
orderstable contains every order Brightlane has processed. - The country condition applies to the customer record; the result is anchored on the customer side, so Canadian customers without any orders still appear.
Output:
- One row per Canadian customer-order pair, plus one row per Canadian customer with no orders, with columns
customer_name,order_id, andtotal_amount.
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 AS customer_name,
o.id AS order_id,
o.total_amount
FROM
customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE
c.country = 'CA'The shape
The WHERE c.country = 'CA' filter applies to the left (preserved) side of the join — the customers table. Because the predicate references a left-side column, the row-preservation guarantee of LEFT JOIN still holds: every Canadian customer survives the filter, whether they have orders or not.
Clause by clause
SELECT c.name AS customer_name, o.id AS order_id, o.total_amountreturns the customer's name aliased to a domain-readable header, the order ID, and the order total. The right-side columns will beNULLfor any Canadian customer with no orders.FROM customers c LEFT JOIN orders o ON c.id = o.customer_idis the standard preserve-the-customer-side shape. Every customer appears, with order columns filled when an order exists andNULLwhen none does.WHERE c.country = 'CA'narrows the result to Canadian customers. The filter runs against the left table, so it removes non-Canadian customers without affecting the join's preservation of unmatched Canadians. The regional team's row set is exactly the rows wherecountry = 'CA', plus their orders if any.
Why this and not ... WHERE c.country = 'CA' AND o.id IS NOT NULL
The WHERE clause as written keeps Canadian customers with no orders in the result — which is what the prompt asks for. Adding AND o.id IS NOT NULL would drop them, collapsing the report into "Canadian customers who have ordered at least once." That's a valid question, but it's not this question.
The trap
The trap with LEFT JOIN filters is putting a condition on the right table in WHERE. The fix here is that c.country lives on the left (preserved) table, so the filter is safe — it removes rows but doesn't touch the outer-join behavior. If the filter referenced a right-side column instead — say, WHERE o.status = 'delivered' — Canadian customers with no orders would silently disappear from the result. Their right-side columns are NULL, and NULL = 'delivered' evaluates to NULL, which WHERE treats as false. The LEFT JOIN would behave like an INNER JOIN and nobody would flag it. Conditions on the preserved side belong in WHERE; conditions on the unpreserved side usually belong in ON, not WHERE.
You practiced applying a condition on the preserved (left) side of a LEFT JOIN. The recurring shape any time the report's row set must include unmatched left rows.
