SQL ALL
True only if the comparison holds against every value the subquery returns.
What & Why
> ALL (subquery) is true only if the value is greater than every value returned — equivalent to comparing against the largest value in that list. It's the strict counterpart to ANY.
See How It Works
BUSINESS QUESTION
Marketing wants campaigns whose spend exceeds every email campaign spend.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
SELECT
id,
name,
spend
FROM marketing.campaigns
WHERE spend > ALL (
SELECT spend
FROM marketing.campaigns
WHERE channel = 'email'
)
ORDER BY spend DESC, id;RESULT — spend greater than every email benchmark
| id | name | spend |
|---|---|---|
| 4 | Enterprise Search | 60000.00 |
| 1 | Spring Launch | 55000.00 |
| 3 | Finance Retargeting | 50000.00 |
With one email campaign, ALL compares each row with the same 45,000 benchmark.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario