Beginner LevelSQL & MySQL
How do ANY, SOME, and ALL operators compare values against subquery lists?
❓ Interview Prompt: Explain `ANY`, `SOME`, and `ALL` comparison operators, subquery list evaluations, and boolean equivalents (`> ANY` vs `> ALL`).
Subquery comparison operators compare a scalar value against a list of subquery values:
•
'> ANY (subquery)': Returns true if the scalar value is greater than at least one value in the subquery list (Equivalent to '> MIN()').
•
'> ALL (subquery)': Returns true if the scalar value is greater than every value in the subquery list (Equivalent to '> MAX()').
•
Note: 'SOME' is a synonym for 'ANY'.
Code Implementation Example
sql
-- Find products priced higher than ALL products in Category 5
SELECT id, title, price
FROM products
WHERE price > ALL (
SELECT price FROM products WHERE category_id = 5
);💡Comparing scalar values against subquery lists using ALL.
🎯 Interview Key Takeaway
Use > ANY to check if a value exceeds the minimum subquery item, and > ALL to check if it exceeds the maximum.
#SQL#ANY#ALL#Subquery#Beginner
Back to all SQL & MySQL Questions→