Part 4: Mastering the ANY Operator | SQL Challenges & Capstone
SQL Set Operations & Advanced Filtering Practice · Part 4 · Lecture Notes
Mastering the ANY Operator
How to compare values against multiple subquery results where the condition must hold for at least one — flexible threshold analysis across business datasets.
Part 4 of 666% Complete
ANY
> ANY / < ANY
Flexible Threshold
Cross-Table Comparison
8 Practice Questions
Learning Objectives
Use ANY to compare a column value against multiple subquery results, where the condition must be true for at least one
Understand that > ANY is a minimum threshold, < ANY is a maximum threshold, and = ANY is equivalent to IN
Apply ANY for cross-table value comparisons — transactions vs subscription fees, products vs transaction amounts
Distinguish ANY (at least one match) from ALL (every value must match)
Click any section header to open or close it
ANY compares a value against a set of values returned by a subquery. The condition is TRUE if the comparison holds for at least one value in the subquery result. It works with comparison operators: > ANY means “greater than at least one”, < ANY means “less than at least one”, = ANY means “equal to at least one”.
In industry, analysts use ANY to compare business thresholds, evaluate operational performance against grouped values, analyze flexible conditions, identify high-performing records relative to a comparison group, and apply multi-value comparisons without writing complex OR chains.
The Basic Structure
The outer query column goes before the operator. ANY goes after the operator. The subquery in parentheses returns the comparison values. The outer row is included if the comparison is true for at least one of the subquery values.
Basic ANY Syntax
SELECT column_name
FROM table_name
WHERE column_name operator ANY (
SELECT column_name
FROM table_name
);
The power of ANY is its flexibility: a value only needs to satisfy the condition against one member of the comparison group. > ANY means “greater than the smallest value in the group.” < ANY means “less than the largest value in the group.” = ANY is equivalent to IN. Understanding this makes ANY conditions intuitive to read and predict.
> ANY Is the Minimum Threshold
unit_price > ANY (Education prices) returns any product priced above the lowest Education price — not above all of them. If Education prices are $29.99 and $39.99, a product at $35 satisfies > ANY because it beats $29.99. Compare this to > ALL, which would require beating $39.99 too.
Products Priced Above at Least One Education Product
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price > ANY (
SELECT unit_price
FROM products
WHERE category = ‘Education’
);
ANY is most analytically useful when comparing values from two different business tables. Transaction amounts versus subscription fees. Product prices versus transaction amounts. Subscription fees versus product prices. These cross-table comparisons answer business questions like “which transactions are larger than at least one subscription tier?”
ANY Returns the Flexible Match
WHERE amount > ANY (SELECT monthly_fee FROM subscriptions) returns transactions that are larger than the smallest subscription fee. On a dataset where the smallest fee is $19.99, all transactions above $19.99 qualify — which is likely most of them.
Transactions Greater Than at Least One Subscription Fee
SELECTROUND(amount, 2) AS amount
FROM transactions
WHERE amount > ANY (
SELECT monthly_fee
FROM subscriptions
);
Subscription Fees Less Than at Least One Product Price
SELECTROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee < ANY (
SELECT unit_price
FROM products
);
ANY provides the most inclusive threshold check — if a value beats even the weakest member of the comparison group, it qualifies. This is useful for identifying records that exceed any operational baseline, such as order line totals larger than any transaction amount, or product prices higher than any subscription fee.
ANY vs ALL vs EXISTS
ANY: beats at least one — the most inclusive test. ALL: beats every single one — the strictest test. EXISTS: checks if the related group exists at all — no value comparison. Choose based on how strict the business threshold needs to be.
Order Totals Greater Than at Least One Transaction
SELECTROUND(line_total, 2) AS line_total
FROM order_items
WHERE line_total > ANY (
SELECT amount
FROM transactions
);
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.
Practice 1Retrieve products with unit_price greater than ANY Education product price rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve products with unit_price greater than ANY Education product price rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price > ANY (
SELECT unit_price
FROM products
WHERE category = ‘Education’
);
Practice 2Retrieve transaction amounts greater than ANY subscription monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve transaction amounts greater than ANY subscription monthly_fee rounded to 2 decimal places.
SQL Answer
SELECTROUND(amount, 2) AS amount
FROM transactions
WHERE amount > ANY (
SELECT monthly_fee
FROM subscriptions
);
Practice 3Retrieve subscription monthly_fee values smaller than ANY product unit_price rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve subscription monthly_fee values smaller than ANY product unit_price rounded to 2 decimal places.
SQL Answer
SELECTROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee < ANY (
SELECT unit_price
FROM products
);
Practice 4Retrieve order_items line_total values greater than ANY transaction amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve order_items line_total values greater than ANY transaction amount rounded to 2 decimal places.
SQL Answer
SELECTROUND(line_total, 2) AS line_total
FROM order_items
WHERE line_total > ANY (
SELECT amount
FROM transactions
);
Practice 5Retrieve products with unit_price less than ANY transaction amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve products with unit_price less than ANY transaction amount rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price < ANY (
SELECT amount
FROM transactions
);
Practice 6Retrieve transaction amounts greater than ANY order_items line_total rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve transaction amounts greater than ANY order_items line_total rounded to 2 decimal places.
SQL Answer
SELECTROUND(amount, 2) AS amount
FROM transactions
WHERE amount > ANY (
SELECT line_total
FROM order_items
);
Practice 7Retrieve subscription monthly_fee values greater than ANY Education product price rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve subscription monthly_fee values greater than ANY Education product price rounded to 2 decimal places.
SQL Answer
SELECTROUND(monthly_fee, 2) AS monthly_fee
FROM subscriptions
WHERE monthly_fee > ANY (
SELECT unit_price
FROM products
WHERE category = ‘Education’
);
Practice 8Retrieve products with unit_price greater than ANY subscription monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve products with unit_price greater than ANY subscription monthly_fee rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price
FROM products
WHERE unit_price > ANY (
SELECT monthly_fee
FROM subscriptions
);
Knowledge Check
Five questions — select the best answer for each.
1. What does unit_price > ANY (subquery) mean?
Aunit_price is greater than all values returned by the subquery
Bunit_price is greater than at least one value returned by the subquery
Cunit_price equals any value returned by the subquery
Dunit_price is greater than the average of the subquery values
Correct. > ANY returns TRUE if the column value is greater than at least one value in the subquery result — it is a minimum threshold check. Only the smallest subquery value needs to be beaten.
Not quite. > ANY means greater than at least one subquery value — the minimum threshold. > ALL would require beating every value.
2. Which condition is equivalent to col = ANY (subquery)?
Acol > ALL (subquery)
Bcol IN (subquery)
CEXISTS (SELECT * FROM table WHERE col = subquery)
Dcol BETWEEN MIN and MAX of subquery
Correct. = ANY checks whether the column equals at least one value in the subquery — which is exactly what IN does. They produce identical results.
Not quite. = ANY and IN produce identical results — both return TRUE when the column matches any value in the list or subquery.
3. If Education product prices are $29.99 and $39.99, which products does unit_price > ANY (Education prices) return?
AOnly products priced above $39.99
BProducts priced above $29.99 (the minimum)
CNo products — the product cannot be in its own subquery
DOnly products priced exactly between $29.99 and $39.99
Correct. > ANY requires the value to beat at least one subquery value. The minimum Education price is $29.99. Any product above $29.99 satisfies the condition. This includes products priced at $35, $39.99, and above.
Not quite. > ANY beats the minimum. If the lowest Education price is $29.99, any product above $29.99 qualifies.
4. How does ANY differ from ALL in a WHERE clause?
AANY requires the condition to be true for all subquery values; ALL requires it for at least one
BANY requires the condition to be true for at least one subquery value; ALL requires it for every value
CANY and ALL produce identical results
DANY works only with = ; ALL works only with > and <
Correct. ANY is the flexible condition — at least one match. ALL is the strict condition — every value must match. > ANY beats the minimum; > ALL beats the maximum.
Not quite. ANY = at least one match (flexible). ALL = every value must match (strict).
5. An analyst writes WHERE amount > ANY (SELECT monthly_fee FROM subscriptions). If monthly_fees are $19.99 and $49.99, which amounts qualify?
AOnly amounts above $49.99
BOnly amounts between $19.99 and $49.99
CAmounts above $19.99 — the minimum fee
DNo amounts qualify because ANY requires matching all values
Correct. > ANY requires beating at least one fee. The minimum fee is $19.99. Any transaction amount above $19.99 satisfies the condition. Amounts above $49.99 also qualify because they beat both fees.
Not quite. > ANY beats the minimum. The minimum fee is $19.99, so any amount above $19.99 qualifies.
Part 4 Summary
The key ideas from this lecture — keep these in mind as you move forward.
ANY helps analysts compare business values against multiple returned records using flexible conditional logic. The condition is TRUE if it holds for at least one subquery value. > ANY = above the minimum. < ANY = below the maximum. = ANY is equivalent to IN.
ANY> ANY< ANY= ANYFlexible ThresholdMinimum ComparisonSubqueryCross-Tablevs ALL
