Default Status for Orderless Customers — NULL Handling in Joins in SQL

The problem

Brightlane's logistics team is preparing a per-order status export covering every customer in the catalog. Customers with no orders must still appear in the export with a placeholder status.

Write a query to return each customer alongside the status of each order they have placed, or a placeholder for customers with no order history.

Assumptions:

The customers table has one row per customer with a name.
The orders table has one row per order, linked to a customer by customer_id and carrying a status.
Customers who have placed orders contribute one row per order. Customers with no orders contribute a single row with 'No Orders' as the status.

Output:

One row per customer-order pairing, plus one row per customer with no orders, with columns name and order_status.

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,
  COALESCE(o.status, 'No Orders') AS order_status
FROM
  customers c
  LEFT JOIN orders o ON c.id = o.customer_id

The shape

The LEFT JOIN keeps every customer in the export, and COALESCE(o.status, 'No Orders') substitutes the placeholder string on exactly the rows where the join had no order to attach. Every output row carries a displayable status, and the no-order-history customers carry the placeholder the logistics team specified.

Clause by clause

SELECT c.name, COALESCE(o.status, 'No Orders') AS order_status returns each customer's name and the status to display. COALESCE checks its arguments left to right: a recorded o.status wins; when o.status is missing (which only happens on the unmatched rows the LEFT JOIN introduced), the literal 'No Orders' is returned instead.
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id pairs each customer with their orders. Customers with orders contribute one row per order; customers with no orders contribute a single row with every o.* column missing.

The trap

The COALESCE substitution only lands on the rows where o.status is missing because of the LEFT JOIN. If the orders table itself stored a missing status for some real order, COALESCE would rewrite that to 'No Orders' too, which would label a real-but-statusless order as having no order at all. On this data every real order has a recorded status, so the substitution lands only on the unmatched rows. When that guarantee is not in the data, the right-side column to test has to be one the source table never stores as missing — a primary key like o.id, for instance.

You practiced LEFT JOIN with COALESCE on the right-side column — preserve every left record and substitute a display value where the right side is missing.

Return each customer alongside the status of each order they have placed, or a placeholder for customers with no order history

The shape

Clause by clause

The trap

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