• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL ANY, ALL

← Previous Next →

In MySQL, ANY and ALL are comparison operators used with subqueries to compare a value with a set of values returned by a subquery. They allow for flexible filtering based on multiple values without using multiple OR or AND conditions.

  • ANY returns TRUE if the comparison matches at least one value from the subquery.

  • ALL returns TRUE if the comparison matches all values from the subquery.

These operators are often used with =, <, <=, >, >=, != to perform advanced conditional checks.

Basic Syntax

ANY

SELECT column_name
FROM table_name
WHERE column_name operator ANY (subquery);

  • operator can be =, <, <=, >, >=, !=.

  • Returns rows where the comparison is TRUE for at least one value from the subquery.

ALL

SELECT column_name
FROM table_name
WHERE column_name operator ALL (subquery);

  • Returns rows where the comparison is TRUE for every value from the subquery.

Using ANY

Consider a students table with scores and a students_scores table containing scores for other exams. To find students who scored more than at least one student in another exam:

SELECT name, score
FROM students
WHERE score > ANY (SELECT score FROM students_scores);

  • Returns students whose score is higher than at least one score in students_scores.

  • Equivalent to checking with multiple OR conditions.

Example with = ANY

SELECT name
FROM students
WHERE score = ANY (SELECT score FROM students_scores);

  • Returns students who have a score matching any score in students_scores.

Using ALL

ALL is stricter than ANY. For example, to find students who scored more than every score in another table:

SELECT name, score
FROM students
WHERE score > ALL (SELECT score FROM students_scores);

  • Returns students whose score is higher than the maximum score in the subquery.

Example with < ALL

SELECT name
FROM students
WHERE score < ALL (SELECT score FROM students_scores);

  • Returns students whose score is lower than all scores in students_scores.

ANY vs ALL in Practice

Operator Meaning Example
ANY Comparison matches at least one value score > ANY (subquery)
ALL Comparison matches every value score > ALL (subquery)

  • ANY is similar to OR conditions.

  • ALL is similar to AND conditions.

Using ANY and ALL with Other Comparisons

Example – Greater Than

SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2);

  • Finds employees whose salary is higher than at least one employee in department 2.

Example – Less Than

SELECT name, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE department_id = 3);

  • Finds employees whose salary is lower than every employee in department 3.

ANY and ALL with Aggregates

You can combine ANY and ALL with aggregates:

SELECT name, score
FROM students
WHERE score >= ALL (SELECT MAX(score) FROM students_scores GROUP BY exam_id);

  • Returns students whose score is greater than or equal to the maximum score in each exam.

  • Useful for ranking and comparison across multiple groups.

Practical Scenarios

  1. Find students scoring more than at least one student in another table:

SELECT name
FROM students
WHERE score > ANY (SELECT score FROM students_scores);

  1. Find students scoring more than all students in another table:

SELECT name
FROM students
WHERE score > ALL (SELECT score FROM students_scores);

  1. Compare employee salaries with another department:

SELECT name
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2);

  1. Find employees earning less than everyone in a department:

SELECT name
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE department_id = 3);

  1. Use ANY and ALL with aggregate results:

SELECT name
FROM students
WHERE score >= ALL (SELECT MAX(score) FROM students_scores GROUP BY exam_id);

  • Finds students with top scores in all exams.

Important Points

  1. ANY returns TRUE if at least one comparison is true.

  2. ALL returns TRUE if every comparison is true.

  3. Subqueries must return a single column.

  4. ANY and ALL can be combined with =, <, <=, >, >=, !=.

  5. Use ANY for flexible matching and ALL for strict matching.

  6. Helps avoid multiple OR/AND conditions, making queries cleaner.

Summary of the Tutorial

  • ANY allows comparison against a set and succeeds if any value matches.

  • ALL requires comparison to be true for all values.

  • Both are used with subqueries to filter data based on sets of values.

  • Useful for conditional comparisons, ranking, and filtering across tables.

  • Mastering ANY and ALL improves query efficiency and readability when dealing with multiple comparisons.

Practice Questions

  1. Retrieve students whose score is greater than ANY score in the students_scores table.

  2. Retrieve students whose score is greater than ALL scores in the students_scores table.

  3. Retrieve students whose score is equal to ANY score in the students_scores table.

  4. Retrieve employees whose salary is higher than ANY employee in department 2.

  5. Retrieve employees whose salary is lower than ALL employees in department 3.

  6. Retrieve products whose price is greater than ANY price in category 1.

  7. Retrieve products whose price is less than ALL prices in category 2.

  8. Retrieve students whose age is greater than ANY age in another table alumni.

  9. Retrieve students whose score is less than ALL scores in exam 101.

  10. Retrieve employees whose experience years are greater than ANY employee in department 5.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top