MySQL ANY and ALL Operators (Easy Reference)

By /
Mysql Any And All

In this tutorial, we will learn about MySQL ANY and ALL operators. ANY and ALL operators are logical operators. They are usually used with the WHERE and HAVING clauses. 

The ANY operator returns true if any of the subquery values satisfy the given condition. The ANY operator compares a value to each value in the result-set of the inner query and returns true if at least one row of that result-set satisfies the given condition.

The ALL operator returns true if all of the subquery values satisfy the given condition. The ALL operator compares a value to each value in the result-set of the inner query and returns true if and only if all the rows of that result-set satisfy the given condition.

Comparison operators precede the ANY and ALL operators.

Syntax of MySQL ANY and ALL Operators

MySQL Any Syntax:

SELECT expression FROM table_name WHERE column_name comparison_operator ANY(SELECT column_name FROM table_name WHERE condition);Code language: SQL (Structured Query Language) (sql)

MySQL ALL Syntax:

SELECT expression FROM table_name WHERE column_name comparison_operator ALL(SELECT column_name FROM table_name WHERE condition);Code language: SQL (Structured Query Language) (sql)

Comparison_operator includes standard comparison operators like =, !=, >=, <=, <>, >, or <.

Example of the MySQL ANY operator

Consider the below Students and Marks tables.

Mysql Students Table
Students Table
Mysql Marks Table
Marks Table

Let us write a query that lists the student names if it finds ANY record in the Marks table for the corresponding student who has scored above 85 in English.

SELECT FirstName, LastName FROM Students WHERE ID=ANY(SELECT StudentID FROM Marks WHERE English>85);Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Mysql Any Example

Example of the MySQL ALL Operator

We’ll continue to use the same table here.

Example 1

Let us consider the below query.

SELECT * FROM Students WHERE ID<ALL(SELECT StudentID FROM Marks WHERE Science>85);Code language: SQL (Structured Query Language) (sql)

Now let me break down what this means. Our condition is basically that the values in the ID column must be less than the lowest value in the result-set to evaluate to true.

Now let us look at the output for the above query,

Mysql All Example 1

So if you observe the two tables carefully, the lowest value we get in the inner query is 2. So our final result-set should contain rows with an ID less than 2 and that’s what we get in our output.

Example 2

Now consider the query below.

SELECT FirstName, LastName FROM Students WHERE ID = ALL(SELECT StudentID FROM Marks WHERE Science=99);Code language: SQL (Structured Query Language) (sql)

The condition here is basically that the values in the ID column must be equal to any value in the result-set to evaluate to true.

The output we get is,

Mysql All Example 2

Conclusion

MySQL ANY and ALL operators can be very interesting and confusing at the same time. These logical operators find a lot of use while dealing with large tables and databases. I would encourage you to read the below references.

References