The Power of ANY and ALL

Beyond Simple Comparisons

I needed to find orders with an amount greater than *any* order from the VIP customer list. `> ANY (subquery)` solved it elegantly.

The Syntax

-- Find products more expensive than ANY product in the 'Budget' category

SELECT * FROM products

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

-- Find products more expensive than ALL products in the 'Budget' category

SELECT * FROM products

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

Pro Tip

`= ANY` is equivalent to `IN`. `<> ALL` is equivalent to `NOT IN`.

*Day 54: Nested Subqueries (Multi-Level).*