Part 5: Mastering the ALL Operator | SQL Challenges & Capstone
SQL Set Operations & Advanced Filtering Practice · Part 5 · Lecture Notes
Mastering the ALL Operator
How to compare values against every subquery result where the condition must hold for all — the strictest threshold analysis in SQL.
Part 5 of 683% Complete
ALL
> ALL / < ALL
Strict Threshold
Zero Rows Valid
8 Practice Questions
Learning Objectives
Use ALL to compare a column value against every subquery result, where the condition must be true for all values
Understand that > ALL equals > MAX and < ALL equals < MIN of the subquery
Interpret zero rows from ALL as a valid analytical result — no rows satisfy the strict condition
Distinguish ALL (every value must match) from ANY (at least one must match)
Click any section header to open or close it
ALL compares a value against every value returned by a subquery. The condition is TRUE only if the comparison holds for every single value in the subquery result. If even one subquery value fails the comparison, the row is excluded. ALL is the strictest subquery operator — it requires total satisfaction across the entire comparison group.
In industry, analysts use ALL to apply strict business comparisons, identify top-performing records that exceed the entire comparison group, evaluate operational thresholds where no exceptions are allowed, compare against complete grouped business values, and analyze extreme performance against all benchmarks.
The Basic Structure
The structure is identical to ANY — column operator ALL (subquery). The critical difference is in the logic: ALL requires the condition to be true for every value in the subquery, not just one.
Basic ALL Syntax
SELECT column_name
FROM table_name
WHERE column_name operator ALL (
SELECT column_name
FROM table_name
);
> ALL means “greater than the maximum value in the subquery.” < ALL means “less than the minimum value in the subquery.” != ALL is equivalent to NOT IN. Because ALL requires matching every value, it is often more restrictive than expected — especially on datasets with overlapping value ranges where no row satisfies the strict condition.
ALL Often Returns No Rows
On real business datasets, ALL frequently returns zero rows because the strict condition is rarely satisfied. If the subquery contains values that bracket the outer table values from both sides, nothing passes. This is expected behaviour — the query ran correctly, the data simply has no values that beat every comparison value.
Products Priced Above ALL Education Products
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price > ALL (
SELECT unit_price
FROM products
WHERE category = ‘Education’
);
> ALL across two tables requires a value to exceed the maximum in the comparison group. Transaction amounts > ALL subscription fees means “greater than every subscription fee” — only transactions exceeding the highest fee qualify. On the course dataset, some of these queries return no rows because no value exceeds every comparison value.
> ALL Equals > MAX
WHERE amount > ALL (SELECT monthly_fee FROM subscriptions) produces the same result as WHERE amount > (SELECT MAX(monthly_fee) FROM subscriptions). ALL evaluates against every value; > ALL is satisfied only by values above the maximum. Both approaches are correct; ALL is more general.
Transactions Greater Than ALL Subscription Fees
SELECTROUND(amount, 2) AS amount
FROM transactions
WHERE amount > ALL (
SELECT monthly_fee
FROM subscriptions
);
ALL queries that return zero rows have not failed — they have correctly found that no rows satisfy the strict condition. This is an important analytical insight in itself: it tells you that no transaction amount exceeds every order line total, or that no subscription fee is below every product price. Empty results from ALL are valid and meaningful business answers.
Empty ALL Results Are Still Analytical Value
If line_total > ALL (transaction amounts) returns no rows, the analysis concludes: no order line total exceeds every transaction amount. This insight — that the datasets have overlapping ranges — is itself a business finding that may drive further investigation.
Subscriptions Less Than ALL Product Prices
SELECTROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee < ALL (
SELECT unit_price
FROM products
);
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message. Some queries may return zero rows — this is expected and analytically meaningful.
Practice 1Retrieve products with unit_price greater than ALL Education product prices rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve products with unit_price greater than ALL Education product prices rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price > ALL (
SELECT unit_price
FROM products
WHERE category = ‘Education’
);
Practice 2Retrieve transaction amounts greater than ALL subscription monthly_fee values rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve transaction amounts greater than ALL subscription monthly_fee values rounded to 2 decimal places.
SQL Answer
SELECTROUND(amount, 2) AS amount
FROM transactions
WHERE amount > ALL (
SELECT monthly_fee
FROM subscriptions
);
Practice 3Retrieve subscription monthly_fee values smaller than ALL product unit_price values rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve subscription monthly_fee values smaller than ALL product unit_price values rounded to 2 decimal places.
SQL Answer
SELECTROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee < ALL (
SELECT unit_price
FROM products
);
Practice 4Retrieve order_items line_total values greater than ALL transaction amounts rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve order_items line_total values greater than ALL transaction amounts rounded to 2 decimal places.
SQL Answer
SELECTROUND(line_total, 2) AS line_total
FROM order_items
WHERE line_total > ALL (
SELECT amount
FROM transactions
);
— Note: No rows returned because no line_total value exceeds every transaction amount.
Practice 5Retrieve products with unit_price less than ALL transaction amounts rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve products with unit_price less than ALL transaction amounts rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price < ALL (
SELECT amount
FROM transactions
);
— Note: No rows returned because no unit_price is less than every transaction amount.
Practice 6Retrieve transaction amounts greater than ALL order_items line_total values rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve transaction amounts greater than ALL order_items line_total values rounded to 2 decimal places.
SQL Answer
SELECTROUND(amount, 2) AS amount
FROM transactions
WHERE amount > ALL (
SELECT line_total
FROM order_items
);
— Note: No rows returned because no transaction amount exceeds every line_total value.
Practice 7Retrieve subscription monthly_fee values greater than ALL Education product prices rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve subscription monthly_fee values greater than ALL Education product prices rounded to 2 decimal places.
SQL Answer
SELECTROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee > ALL (
SELECT unit_price
FROM products
WHERE category = ‘Education’
);
Practice 8Retrieve products with unit_price greater than ALL subscription monthly_fee values rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve products with unit_price greater than ALL subscription monthly_fee values rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price > ALL (
SELECT monthly_fee
FROM subscriptions
);
Knowledge Check
Five questions — select the best answer for each.
1. What does unit_price > ALL (subquery) mean?
Aunit_price is greater than at least one value in the subquery
Bunit_price is greater than every single value returned by the subquery
Cunit_price equals all values in the subquery
Dunit_price is greater than the average of the subquery values
Correct. > ALL requires the column value to exceed every value in the subquery. It is satisfied only by values above the maximum. If even one subquery value is higher, the row is excluded.
Not quite. > ALL means greater than every value in the subquery — it is satisfied only if the column beats the maximum.
2. What is unit_price > ALL (subquery) equivalent to?
Aunit_price > MIN (subquery result)
Bunit_price > MAX (subquery result)
Cunit_price > AVG (subquery result)
Dunit_price > SUM (subquery result)
Correct. > ALL requires the value to exceed every subquery value, which means it must exceed the maximum. It is logically equivalent to > MAX(subquery).
Not quite. > ALL means above every value, which means above the maximum. > ALL is equivalent to > MAX(subquery).
3. An ALL query returns zero rows. What does this indicate?
AThe query has a syntax error
BThe subquery returned no values
CNo rows in the outer table satisfy the strict condition against all subquery values
DThe operator is incompatible with the data types
Correct. Zero rows from ALL is a valid and meaningful result — it means no outer table value satisfies the condition against every subquery value. This is expected on datasets with overlapping value ranges.
Not quite. Zero rows from ALL is valid — it means no row satisfies the condition against every subquery value. The query ran correctly.
4. How does ALL differ from ANY in a WHERE clause?
AANY requires the condition to match all values; ALL requires at least one match
BALL requires the condition to be true for every subquery value; ANY requires at least one match
CThey produce identical results when used with >
DALL works only on numeric columns; ANY works on text columns too
Correct. ALL is the strict version — every subquery value must be beaten. ANY is the flexible version — at least one must be beaten. > ALL beats the maximum; > ANY beats the minimum.
Not quite. ALL requires matching every subquery value (strict). ANY requires matching at least one (flexible).
5. Which condition returns subscription fees that are below every product price?
AWHERE monthly_fee < ANY (SELECT unit_price FROM products)
BWHERE monthly_fee > ALL (SELECT unit_price FROM products)
CWHERE monthly_fee < ALL (SELECT unit_price FROM products)
DWHERE monthly_fee < EXISTS (SELECT unit_price FROM products)
Correct. < ALL means less than every product price — less than the minimum. This is the strictest possible lower bound check. On the course dataset this returns zero rows because no fee is below all product prices.
Not quite. < ALL means less than every product price (less than the minimum). This is the correct structure.
Part 5 Summary
The key ideas from this lecture — keep these in mind as you move forward.
ALL helps analysts apply strict comparisons by evaluating business records against every value returned from a subquery. > ALL = above the maximum. < ALL = below the minimum. Zero rows is a valid result indicating no row satisfies the strict condition.
