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.

Table of Contents

## 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);
```

**MySQL ALL Syntax:**

```
SELECT expression FROM table_name WHERE column_name comparison_operator ALL(SELECT column_name FROM table_name WHERE condition);
```

`Comparison_operator`

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

## Example of the MySQL ANY operator

Consider the below Students and Marks tables.

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);
```

And we get the output as,

## 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);
```

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,

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);
```

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,

## 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

- MySQL Official documentation on the ANY operator.
- MySQL Official documentation on the ALL operator.