MySQL ANY & ALL Operators
In this page:
What Are ANY and ALL?
ANY and ALL let you compare a value against every row returned by a subquery, rather than against a single scalar value. ANY returns true if the comparison holds for at least one row in the subquery result, while ALL requires the comparison to hold for every row. They are always paired with a comparison operator such as =, >, or < placed directly before ANY or ALL.
Example: What Are ANY and ALL?
CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 20), (2, 50), (3, 90);
SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE id = 1);= ANY Behaves Like IN
Using = ANY(subquery) is functionally identical to using the IN operator with the same subquery -- both check whether the value matches at least one row in the subquery result. MySQL lets you write either form, but IN is generally more readable for simple equality checks, while ANY is more useful once you move beyond plain equality.
Example: = ANY Behaves Like IN
CREATE TABLE customers (id INT, region TEXT);
INSERT INTO customers VALUES (1, 'North'), (2, 'South');
SELECT * FROM customers WHERE region = ANY (SELECT 'North');
SELECT * FROM customers WHERE region IN ('North');Comparing with ALL
A comparison like > ALL(subquery) is true only when the value is greater than every single row the subquery returns, making ALL effectively a check against the subquery's maximum (for >) or minimum (for <) value. This is often clearer to write directly with MAX() or MIN(), but ALL is useful when the subquery logic is more complex than a single aggregate.
Example: Comparing with ALL
CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 20), (2, 50), (3, 90);
SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE id IN (1, 2));ANY/ALL with Empty Subqueries
If the subquery returns zero rows, ANY comparisons always evaluate to false since there is no row for the condition to match, while ALL comparisons always evaluate to true since the condition trivially holds for every row in an empty set. This edge case matters when the subquery depends on a filter that might legitimately match nothing.
Example: ANY/ALL with Empty Subqueries
CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 20);
SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE id = 999);
SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE id = 999);ANY/ALL vs EXISTS
ANY and ALL compare a specific column value against a subquery's results, whereas EXISTS just checks whether the subquery returns any rows at all, ignoring the actual values. Use ANY/ALL when the comparison value itself matters, and EXISTS when you only care whether a related row is present, such as checking if a customer has placed at least one order.
Example: ANY/ALL vs EXISTS
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: