SQL ANY and ALL

Lesson 10.2 · IN with subqueries asked whether a value equals something in a list. ANY and ALL ask the same kind of question with any comparison: greater than, less than, not equal. Put one between the operator and the subquery:

select product_name, price
from products
where price > all (
  select price
  from products
  where category_id = 8
)

• price > all (...) is true when the price beats every value the subquery returns. Outdoor Decor (category 8) runs from 15.00 to 18.50, so it has to beat 18.50.
• price > any (...) is true when it beats at least one. Beating the cheapest, 15.00, is enough.

SOME is another spelling of ANY. Two pairs are worth knowing because you have already met them: = any (...) is the same as in (...), and <> all (...) is the same as not in (...).

Why not just use MAX or MIN?

Beating every price means beating the highest one, so price > (select max(price) ...) usually returns the same rows, and many people find it easier to read. The two part ways when the subquery returns no rows at all. There is no category 99:

select
  (select count(*) from products
   where price > all (select price from products where category_id = 99)) as with_all,
  (select count(*) from products
   where price > any (select price from products where category_id = 99)) as with_any,
  (select count(*) from products
   where price > (select max(price) from products where category_id = 99)) as with_max

That returns 24, 0, and 0. Every product beats all of nothing, because there is no price it fails to beat, so ALL over an empty list is true. No product beats any of nothing, so ANY is false. And max() of no rows is NULL, so that comparison keeps nothing. If a filter in your subquery can ever match nothing, decide which of those answers you want before picking the form.

One NULL in the list

ALL has the same weakness as NOT IN in Lesson 10.5 · NOT IN vs NOT EXISTS. Which orders were placed after every shipment so far? Seven orders haven't shipped, so their shipped_date is NULL:

select order_id, order_date, status
from orders
where order_date > all (
  select shipped_date
  from orders
)
order by order_id

It returns no rows. A date can't be shown to be later than an unknown date, so for every order the comparison is either false or NULL, never true. Filter the NULL out and the answer appears: orders 13 and 19, both placed after the last shipment on June 7.

select order_id, order_date, status
from orders
where order_date > all (
  select shipped_date
  from orders
  where shipped_date is not null
)
order by order_id

ANY is more forgiving: one comparison that comes out true is enough, whatever the NULL rows say. It only returns NULL when no comparison is true and at least one is unknown.

What DuckDB shows you

Select an ALL comparison as a column and DuckDB names it (NOT (price <= ANY(...))): it rewrites "greater than all" as "not less than or equal to any", which means the same thing. The subquery must return exactly one column; two is a Binder Error: Subquery returns 2 columns - expected 1. PostgreSQL, MySQL, SQL Server, and Oracle all support ANY, SOME, and ALL with a subquery. SQLite supports none of them, so use max(), min(), or EXISTS there.