Products with No Category — IS NULL in SQL

The problem

Brightlane's clearance team is running a 10% discount event on products that have not yet been assigned to a product category.

Write a query to return each uncategorized product's name, its original price, and its clearance price.

Assumptions:

The products table contains every item in Brightlane's catalog.
The category_id column links each product to its category; products awaiting classification have category_id set to NULL.
The clearance price is the original price multiplied by 0.9 (a 10% reduction).

Output:

One row per uncategorized product, with columns name, price, and clearance_price.

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
  name,
  price,
  price * 0.9 AS clearance_price
FROM
  products
WHERE
  category_id IS NULL

The shape

WHERE decides which rows survive; SELECT decides what each surviving row looks like. The clearance event needs uncategorized products (filter on category_id IS NULL) with the 10% discount applied (compute price * 0.9 inline).

Clause by clause

SELECT name, price, price * 0.9 AS clearance_price returns three columns per surviving row: the product's name, its current price, and the discounted price computed on the fly. price * 0.9 runs once per row, so the Mystery Bundle at 29.99 lands at 26.991 and Gift Card $50 lands at 45. AS clearance_price labels the derived column so the team reads the result as a price list.
FROM products reads the catalog.
WHERE category_id IS NULL keeps only products whose category link is absent — the items awaiting classification.

Why this and not compute first, then filter

WHERE runs before SELECT, so price * 0.9 only ever evaluates against the three rows that passed the IS NULL check. The categorized products never get the multiplication done on them; they're already gone by the time the SELECT list runs.

This ordering is also why an alias defined in SELECT can't be referenced from WHERE — the alias doesn't exist yet when WHERE runs.

The trap

WHERE category_id = NULL returns zero rows, the SELECT list never runs against anything, and the clearance event launches with no products on it. = against NULL evaluates to unknown, never true. IS NULL is the only operator that detects absence.

You practiced filtering on IS NULL while computing a derived column in the same query. Filter-and-compute is the everyday shape — WHERE decides which rows pass through, SELECT decides what each row looks like.

Return each uncategorized product's name, its original price, and its clearance price

The shape

Clause by clause

Why this and not compute first, then filter

The trap

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