MySQL ANY and ALL Operators: Definition, Syntax, and Examples

In MySQL, the ANY and ALL operators are used in combination with subqueries to perform comparisons. They allow you to compare a value to a set of values returned by a subquery.

ANY → Returns TRUE if any one value from the subquery satisfies the condition.
ALL → Returns TRUE if all values from the subquery satisfy the condition.

ANY Syntax

SELECT column_name
FROM table_name
WHERE column_name operator ANY (
  SELECT column_name
  FROM another_table
  WHERE condition
);

ALL Syntax With SELECT

SELECT column_name
FROM table_name
WHERE column_name operator ALL (
  SELECT column_name
  FROM another_table
  WHERE condition
);

operator → Can be =, !=, >, <, >=, <=.
ANY → Condition is true if at least one value matches the comparison.
ALL → Condition is true if all values match the comparison.

We have two tables:

students Table

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

courses Table

course_name	course_name
101	Math
102	Science
103	History
104	English

SQL ANY Examples

The following SQL statement find courses where any student scored below 70.

Example

SELECT course_name

FROM courses

WHERE course_id = ANY (

SELECT course_id

FROM students

WHERE score < 70

);

Try it yourself

Output:

course_name
Math
Science

Explanation:

The subquery gets course IDs where students scored below 70 (David in English course scored 65).
The main query selects courses that match these course IDs.
Only "English" is returned.

SQL ALL Examples

The following SQL statement find courses where all students scored above 70.

Example

SELECT course_name

FROM courses

WHERE course_id = ALL (

SELECT course_id

FROM students

WHERE score > 70

);

Try it yourself

Output:

course_name

Explanation:

The subquery gets course_id values where students scored above 70.
The main query selects only courses where all students have scores above 70.
This will of course return FALSE because the score column has many different values (not only the value above 70):

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.