SQL — Any

What is ANY?

ANY returns true if the comparison is true for at least one value in the subquery result. It's the opposite of ALL.

Basic Syntax

SELECT column_name
FROM table_name
WHERE column_name > ANY (subquery);

Examples

Find salaries higher than at least one intern

SELECT first_name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM interns
);

Find products cheaper than at least one premium product

SELECT product_name, price
FROM products
WHERE price < ANY (
  SELECT price
  FROM products
  WHERE category = 'Premium'
);

ANY vs ALL

-- ANY: true if condition holds for at least one row
WHERE salary > ANY (SELECT salary FROM interns);
-- "salary is higher than at least one intern's salary"

-- ALL: true if condition holds for every row
WHERE salary > ALL (SELECT salary FROM interns);
-- "salary is higher than every intern's salary"

ANY with Different Operators

Operator	Meaning
> ANY	Greater than at least one value
< ANY	Less than at least one value
= ANY	Equal to at least one value (same as IN)

ANY = IN

= with ANY is equivalent to IN:

-- These are the same:
WHERE department_id = ANY (1, 2, 3)
WHERE department_id IN (1, 2, 3)

Practical Example

-- Find employees who earn more than at least one person in marketing
SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > ANY (
  SELECT salary
  FROM employees
  WHERE department = 'Marketing'
)
AND e.department != 'Marketing';

NULL Behavior

If the subquery returns NULL, comparisons with NULL return UNKNOWN, which is treated as FALSE. Filter NULLs for predictable results:

WHERE salary > ANY (
  SELECT salary FROM employees WHERE salary IS NOT NULL
);

Performance

Most databases optimize ANY similarly to IN or EXISTS. For complex subqueries, EXISTS can sometimes be faster:

-- Equivalent pattern using EXISTS
WHERE EXISTS (
  SELECT 1 FROM employees e2
  WHERE e2.department = 'Marketing'
  AND e2.salary < e1.salary
);

Practice

1. Find all students who scored higher than at least one student in Class B
2. Find products that cost the same as any product in the "Sale" category
3. Rewrite an ANY query using EXISTS