Blank Out Pending Status — COALESCE and NULLIF in SQL

The problem

Brightlane's fulfillment team is exporting order data to a third-party logistics platform that has no concept of a pending stage. Pending orders should appear with no recorded status in the export so the external system ignores them.

Write a query to return each order's ID and its export status.

Assumptions:

The orders table has one row per order with an id and a status.
Orders that have not entered the shipping pipeline have status = 'pending'.
An order with status = 'pending' should appear with a missing status in the export; all other orders should show their recorded status.

Output:

One row per order, with columns id and 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
  id,
  NULLIF(status, 'pending') AS status
FROM
  orders

The shape

NULLIF(status, 'pending') returns the recorded status for every order except the pending ones, which it rewrites to NULL. The export then carries a real status for orders past the pending stage and an empty status for the rest, which is what the logistics platform's ignore-the-missing-ones logic depends on.

Clause by clause

SELECT id, NULLIF(status, 'pending') AS status returns each order's ID and its export status. NULLIF takes exactly two arguments: if they are equal, it returns NULL; otherwise it returns the first argument unchanged. So 'pending' becomes NULL, and 'shipped', 'delivered', and the other recorded statuses pass through.
FROM orders reads every order. The pending orders stay in the result, since those are exactly the rows whose status needs to become missing.

Why this and not `CASE WHEN status = 'pending' THEN NULL ELSE status END`

Both forms produce the same export. NULLIF(status, 'pending') says the intent in one expression: this stored value is a sentinel that should be missing downstream. The CASE version says the same thing in three clauses. Reach for NULLIF whenever the question is exactly "convert this specific value to NULL."

You practiced NULLIF(column, sentinel) — convert a stored placeholder back to missing so a downstream consumer that ignores missing values skips it.

Return each order's ID and its export status

The shape

Clause by clause

Why this and not `CASE WHEN status = 'pending' THEN NULL ELSE status END`

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

The shape

Clause by clause

Why this and not CASE WHEN status = 'pending' THEN NULL ELSE status END

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

Why this and not `CASE WHEN status = 'pending' THEN NULL ELSE status END`