MySQL ANY and ALL
Comparing values against multiple results from subqueries
🔢 What are ANY and ALL?
ANY and ALL operators compare a value to a set of values from a subquery. ANY returns TRUE if the condition is true for at least one value, while ALL requires the condition to be true for every value.
-- Find products cheaper than ANY product in category 5
SELECT product_name, price
FROM products
WHERE price < ANY (
SELECT price FROM products WHERE category_id = 5
);
Output:
| product_name | price |
|---|---|
| Mouse | 25.99 |
Key ANY and ALL Concepts
ANY Operator
True if condition matches at least one value
SELECT name FROM products
WHERE price > ANY (
SELECT price FROM products
WHERE category = 'Electronics'
);ALL Operator
True if condition matches all values
SELECT name FROM products
WHERE price > ALL (
SELECT price FROM products
WHERE category = 'Books'
);SOME Operator
Synonym for ANY operator
-- SOME works exactly like ANY
WHERE price < SOME (
SELECT price FROM products
);Comparison Operators
Use with =, !=, <, >, <=, >=
WHERE salary >= ALL (
SELECT salary
FROM employees
WHERE dept = 'Sales'
);🔹 Using ANY Operator
The ANY operator returns TRUE when the comparison is true for at least one value returned by the subquery. It's useful for finding records that meet a condition compared to any value in a set.
-- Find employees earning more than ANY employee in Sales
SELECT
employee_name,
salary,
department
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department = 'Sales'
);
Output:
| employee_name | salary | department |
|---|---|---|
| Alice Johnson | 75000 | Engineering |
🔹 Using ALL Operator
The ALL operator returns TRUE only when the comparison is true for every value returned by the subquery. This is perfect for finding records that exceed or meet all values in a comparison set.
-- Find products more expensive than ALL products in category 'Books'
SELECT
product_name,
price,
category
FROM products
WHERE price > ALL (
SELECT price
FROM products
WHERE category = 'Books'
);
Output:
| product_name | price | category |
|---|---|---|
| Laptop Pro | 1299.99 | Electronics |
🔹 ANY vs ALL Comparison
Understanding the difference between ANY and ALL is crucial. ANY needs just one match to return TRUE, while ALL requires every value to match. Think of ANY as "at least one" and ALL as "every single one".
-- ANY: Returns products cheaper than at least one item in Electronics
SELECT product_name, price
FROM products
WHERE price < ANY (
SELECT price FROM products WHERE category = 'Electronics'
);
-- ALL: Returns products cheaper than every item in Electronics
SELECT product_name, price
FROM products
WHERE price < ALL (
SELECT price FROM products WHERE category = 'Electronics'
);
🔹 Practical Examples
Here are real-world scenarios where ANY and ALL operators shine. Use them to find top performers, budget-friendly options, or items that meet strict criteria across multiple comparisons in your database queries.
-- Find students who scored higher than ANY student in Class A
SELECT student_name, score
FROM students
WHERE score > ANY (
SELECT score FROM students WHERE class = 'A'
);
-- Find products cheaper than ALL competitor prices
SELECT product_name, our_price
FROM our_products
WHERE our_price < ALL (
SELECT price FROM competitor_products
);
-- Find employees with salary equal to ANY manager's salary
SELECT employee_name, salary
FROM employees
WHERE salary = ANY (
SELECT salary FROM employees WHERE role = 'Manager'
);