Brightlane's promotions team is sweeping products into a clearance event. The sweep covers two groups: products priced below $30, and products that have not yet been assigned to a category.
Write a query to return the name and price of every product in the sweep.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - A product that has not been assigned to a category has
category_idset toNULL. - A product qualifies for the sweep if it satisfies either condition; products that satisfy both still appear once.
Output:
- One row per qualifying product, with columns
nameandprice.
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
name,
price
FROM
products
WHERE
price < 30
OR category_id IS NULLThe shape
A price comparison and an IS NULL test joined by OR — the sweep grabs anything cheap and anything uncategorised. Each row is evaluated once, so a product that satisfies both conditions still appears once.
Clause by clause
SELECT name, pricereturns the two columns the promotions team needs to staff the clearance event.FROM productsreads the catalogue.WHERE price < 30 OR category_id IS NULLis the qualifying rule.price < 30keeps rows priced under $30 — items like theKids' T-Shirtat19.99and theYoga Matat29.99.category_id IS NULLkeeps rows that haven't been assigned to a category yet, which is whyGift Card $50andGift Card $100show up despite being well above $30. TheORaccepts a row when at least one side is true.
Why OR and not two separate queries
A single WHERE with an OR does the union of the two groups in one pass. Running two queries and stitching the results together would produce the same logical set, but it would require deduplication afterwards — any product that's both cheap and uncategorised would appear in both result sets. WHERE evaluates each row exactly once and includes it in the output exactly once, so the union is built in naturally with no duplicate handling needed.
Why IS NULL and not category_id = NULL
= NULL returns unknown for every row, even the ones where category_id actually is NULL. WHERE keeps only rows where the condition evaluates to true, so the unknown rows are silently dropped. The query would still run, but the uncategorised products would never reach the sweep. IS NULL is the only operator that tests for the absence of a value and returns true or false.
You practiced combining a comparison and an IS NULL test with OR. OR is the recurring shape when a row qualifies for membership in either of two groups; duplicates from rows that satisfy both conditions are not produced because WHERE evaluates each row exactly once.
