Lesson 36 of 61

5 mins read

SQL ANY & ALL: Quantified Comparison Subqueries

In Plain English

The `ANY` (or `SOME`) and `ALL` operators combine comparison operators (`=`, `!=`, `<`, `<=`, `>`, `>=`) with subqueries. `> ANY (subquery)` means greater than the SMALLEST value in the subquery; `> ALL (subquery)` means greater than the LARGEST value in the subquery.

The Mental Model

"`> ANY` is being taller than at least one player on a basketball team (taller than the shortest player); `> ALL` is being taller than every single player on the entire team (taller than the tallest player)."

Deep Dive: How It Works

`ANY` (or `SOME`): Returns TRUE if the comparison condition holds for at least ONE row returned by the subquery.

`ALL`: Returns TRUE only if the comparison condition holds for EVERY single row returned by the subquery.

Equivalences: `= ANY (...)` is completely identical to `IN (...)`; `!= ALL (...)` is completely identical to `NOT IN (...)`.

Syntax Blueprint

-- Greater than ANY (greater than the minimum):
SELECT product_name, price
FROM products
WHERE price > ANY (SELECT price FROM competitor_products);

-- Greater than ALL (greater than the maximum):
SELECT product_name, price
FROM products
WHERE price > ALL (SELECT price FROM competitor_products);

Combine comparison operators with ANY or ALL to test against multi-row subquery results.

Core Rules to Remember

Quantified Comparisons: Enables relational comparisons against entire subquery result sets.

= ANY Equals IN: `= ANY` and `IN` are interchangeable in standard SQL.

Dialect Emulation: In SQLite (which omits ANY/ALL syntax), easily emulate with MIN() or MAX() subqueries.

Common Beginner Traps & How to Fix Them

Mistake:Using `> ALL` when the subquery returns an empty set (0 rows).

Why it happens: In mathematical predicate logic, `ALL` against an empty set vacously evaluates to TRUE for all rows.

How to fix: Ensure subquery non-emptiness when writing business constraints.

Pro Tip: In SQLite and MySQL, rewrite `price > ALL (SELECT price FROM ...)` as `price > (SELECT MAX(price) FROM ...)` for maximum portability.

Live Interactive Example

Hit Run Code to see it live

Comparing Products Against Competitor Extremes

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Higher than all selected items over $400, and higher than any matched items over $50.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Filter Above Maximum Subquery Value

Select `name`, `score` from `players` where `score > (SELECT MAX(score) FROM rookies)`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.