ANY and ALL compare a value to a set of values returned by a subquery.

ANY Operator

-- Find products more expensive than ANY product in Electronics

SELECT * FROM products

WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');

ALL Operator

-- Find products more expensive than ALL products in Books

SELECT * FROM products

WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

ANY with = (Same as IN)

SELECT * FROM customers

WHERE country = ANY (SELECT country FROM suppliers);

ALL with != (Not in any)

SELECT * FROM products

WHERE category != ALL (SELECT category FROM discontinued_categories);

ANY/ALL with Aggregates

-- Find orders above average for any category

SELECT * FROM orders

WHERE total > ANY (

SELECT AVG(total) FROM orders GROUP BY category

);

Generate ANY/ALL Queries

AI2sql applies these operators when appropriate.