iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

SQL ANY and ALL

By · Reviewed & last updated · Part of SQL Tutorial

ANY and ALL let you compare a value against an entire subquery result without writing an explicit loop.

The forms

FormMeans
x > ANY (SELECT y FROM t)True if x is greater than at least one value.
x > ALL (SELECT y FROM t)True if x is greater than every value (i.e. greater than MAX).
x = ANY (...)Same as x IN (...).
x <> ALL (...)Same as x NOT IN (...).

Example — pricier than everything out of stock

SQL
SELECT name, price
FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE stock = 0
);

Example — at least as cheap as something on sale

SQL
SELECT name, price
FROM products
WHERE price <= ANY (
  SELECT price FROM products WHERE on_sale = 1
);

SOME = ANY

The keyword SOME is a synonym for ANY in standard SQL. Most engines support it, but ANY is more common in practice.

Tip: When the subquery returns a single aggregate (MIN/MAX), the same condition can be written more clearly: WHERE price > (SELECT MAX(price) FROM …).

Example

Example
SELECT name FROM products
WHERE price > ALL (SELECT price FROM products WHERE stock = 0);
Try it Yourself »

Exercise

Match products priced higher than every out-of-stock product.

WHERE price > (SELECT price FROM products WHERE stock = 0)

Test yourself

Q1. x = ANY (subquery) is equivalent to…
Q2. x > ALL (…) is equivalent to…
Q3. SOME is a synonym for…

Discussion

Loading…