How to Use ALL in SQL

The ALL operator compares a value to every value in a subquery result. It returns TRUE if the comparison is true for all values returned by the subquery.

Syntax:

expression operator ALL (subquery)

• operator can be: =, !=, <, >, <=, >=

• subquery returns a list of values.

How ALL Works:

• value > ALL (subquery) means value is greater than every value in the subquery.

• value < ALL (subquery) means value is less than every value in the subquery.

Example:

Suppose a table products with columns: product_id, price.

You want to find products priced higher than all products in category 1:

SELECT *FROM productsWHERE price > ALL (    SELECT price FROM products WHERE category_id = 1);

Important Notes:

• If the subquery returns no rows, ALL condition returns TRUE (since no rows violate the condition).

• To check if a value is equal to all values, use = ALL.

• ALL is often contrasted with ANY (or SOME), which checks if condition is true for at least one value.

If you want examples with a specific operator or real data, just ask!