MySQL ANY, ALL

MySQL ANY and ALL Operators

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

courses Table

SQL ANY Examples

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

Output:

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.

Output:

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):