Orders Excluding Delivered and Cancelled — BETWEEN, IN, and LIKE in SQL

The problem

Brightlane's operations team is reviewing the active order pipeline and wants to exclude anything that has already reached a terminal state.

Write a query to return the ID, status, and total amount for every order that is neither delivered nor cancelled.

Assumptions:

The orders table contains every order Brightlane has processed.
Terminal statuses are delivered and cancelled; every other value of status represents an order still in the active pipeline.

Output:

One row per non-terminal order, with columns id, status, and total_amount.

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,
  status,
  total_amount
FROM
  orders
WHERE
  status NOT IN ('delivered', 'cancelled')

The shape

NOT IN ('delivered', 'cancelled') inverts the membership test — the active pipeline is everything except the two terminal statuses, so the filter names what to exclude and lets every other status through.

Clause by clause

SELECT id, status, total_amount returns the three columns the operations review needs: the order identifier, the current status so the team can see where each order sits in the pipeline, and the dollar amount that quantifies the active queue.
FROM orders reads the order table.
WHERE status NOT IN ('delivered', 'cancelled') keeps only the rows whose status is neither delivered nor cancelled. The result is the inverse of the membership test — pending orders pass, shipped orders pass, anything else that isn't terminal passes.

The trap

If a NOT IN list ever contains a NULL, the whole condition evaluates to unknown for every row and the result set comes back empty. The list here is hard-coded strings only, so the trap doesn't fire, but the rule is: keep NULL out of any NOT IN list.

You practiced inverting a membership test with NOT IN. status NOT IN ('delivered', 'cancelled') is the compact form of "none of these values" — the recurring shape behind every exclusion filter against a known list of terminal or excluded states.

Return the ID, status, and total amount for every order that is neither `delivered` nor `cancelled`

The shape

Clause by clause

The trap

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