Products Above Any Sold Price — Subqueries in WHERE in SQL

The problem

Brightlane's pricing team wants to identify catalogue products whose list price is higher than at least one unit price at which an item has been sold.

Write a query to return the name and price of every qualifying product.

Assumptions:

The products table contains every product in the catalogue.
The order_items table records each line item with a unit_price.
A product qualifies if its price exceeds even a single value in the order_items.unit_price set.

Output:

One row per qualifying product, with columns name and 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
FROM
  products
WHERE
  price > ANY (
    SELECT
      unit_price
    FROM
      order_items
  )

The shape

> ANY (subquery) is true when the comparison holds for at least one value in the subquery's result. So a product's price qualifies the moment it's greater than even the cheapest unit_price ever recorded in order_items. Effectively, the test reduces to "is this price above the minimum sold price."

Clause by clause

SELECT name, price FROM products reads every catalogue product. The filter trims this to the ones whose list price clears the bar set by ANY.
WHERE price > ANY (SELECT unit_price FROM order_items) is the comparison. PostgreSQL runs the inner query, collects every unit_price value across the order_items table, and then for each outer product asks: is this price greater than at least one of those values? If yes, the row passes; if no — the product's price is less than or equal to every recorded unit price — the row drops.
Mystery Bundle at 29.99 passes because at least one item in order_items sold for less than 29.99. The catalogue products that don't appear are the ones priced at or below the lowest unit price ever recorded.

Why this and not `IN`

IN only expresses equality membership. ANY accepts any comparison operator — >, <, >=, <=, <>, = — which is its reason to exist. = ANY (subquery) is the same thing as IN (subquery); the other operators are what give ANY reach that IN doesn't have. This problem needs >, so IN isn't an option.

The same answer is reachable through a scalar subquery: WHERE price > (SELECT MIN(unit_price) FROM order_items). Many analysts find that form more readable because the threshold is named explicitly.

You practiced > ANY against a subquery. The recurring rule: op ANY (subquery) is true when the comparison holds for at least one value — = ANY is equivalent to IN, but the comparison form (>, <, <>) is where ANY reaches beyond what IN can express.

Return the name and price of every qualifying product

The shape

Clause by clause

Why this and not `IN`

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 IN

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

Why this and not `IN`