Brightlane's pricing team maintains a reference price list in category 999. A product is classified as premium if its price exceeds every item in that reference list.
Write a query to return the name and price of every premium product.
Assumptions:
- The
productstable contains every product in the catalogue. - Category
999is currently empty — no products havecategory_id = 999, so the reference price list is an empty set. - The result will contain every product in the catalogue. (When the reference list later has prices in it, the same query naturally returns only the products that beat all of them.)
Output:
- One row per qualifying product, with columns
nameandprice. The result will contain all 63 products in the catalogue.
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 > ALL (
SELECT
price
FROM
products
WHERE
category_id = 999
)The shape
> ALL is true only when the comparison holds for every value in the subquery's result. When the subquery returns no values, "every value" is vacuously satisfied. There's nothing to fail against, so the test returns true for every outer row. Category 999 is empty, so the reference list is empty, so every product in the catalogue clears the bar.
Clause by clause
SELECT name, price FROM productsreads every product. The filter keeps only the ones whosepricebeats every value in the reference list.SELECT price FROM products WHERE category_id = 999is the inner subquery. It collects everypricefrom products tagged to category 999. With no products in that category, the subquery returns the empty set.WHERE price > ALL (...)is the outer test. For each product, PostgreSQL asks: is thispricegreater than every value in the inner set? When the inner set is empty, there is no value the outer price can fail against, so the test returns true for every row. All 63 catalogue products come through.
Why this and not > (SELECT MAX(price) ...)
The natural first instinct is to write the test as WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 999). It reads more directly: a premium product is one whose price exceeds the highest reference price. On a populated reference list, the two forms return the same products.
They diverge when the reference list is empty. MAX over an empty set returns NULL, and price > NULL is NULL for every row, so the scalar-subquery form would silently return zero rows. > ALL returns true for every row in the same situation. Same business question, opposite answers on empty input.
The trap
The trap is reading "every product passes" as a query bug. It isn't. The empty subquery is the reason, and > ALL is defined to return true on the vacuous case. The behaviour is useful when the reference set might genuinely be empty in production. It's surprising when the empty set was unintentional — a typo on category_id, a misnamed filter, an upstream load that didn't run.
The mirror image: > ANY against an empty subquery returns false for every row. When an ALL filter returns the whole table or an ANY filter returns nothing, check the inner subquery first.
You practiced > ALL against an empty subquery. Useful to know when a reference set might genuinely be empty in production; surprising when it's empty by accident.
