SQL ANY and ALL Operators

The ANY and ALL operators are used with a WHERE or HAVING clause to compare a value to a set of values returned by a subquery.

SQL ANY Operator

The ANY operator returns true if any of the subquery values meet the condition.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

Example: ANY Operator

SELECT product_name
FROM products
WHERE price > ANY (SELECT price FROM products WHERE category_id = 1);

SQL ALL Operator

The ALL operator returns true if all the subquery values meet the condition.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

Example: ALL Operator

SELECT product_name
FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 2);

Sample Tables

Consider the following products table:

products:
| product_id | product_name | category_id | price |
|------------|--------------|-------------|-------|
| 1          | Product 1    | 1           | 10    |
| 2          | Product 2    | 1           | 15    |
| 3          | Product 3    | 2           | 20    |
| 4          | Product 4    | 2           | 25    |

Result of ANY Operator

| product_name |
|--------------|
| Product 2    |
| Product 3    |
| Product 4    |

The result shows products with a price greater than any product in category 1.

Result of ALL Operator

| product_name |
|--------------|
| Product 4    |

The result shows products with a price greater than all products in category 2.