The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values. In particular, the ANY operator will return a result if the operation is true for any of the values in a given range, while the ALL operator will do the same but if the operation is true for all values in a given range. The given range is a subquery.

Here is an example with ANY operator used in combination with SELECT statement and WHERE clause:

Syntax

SELECT * FROM table1 WHERE table1.column1 = ANY (SELECT value FROM table2);

And similar example but with the ALL operator:

SELECT column_name FROM table1 WHERE column_name <> ALL (SELECT column_name FROM table2);

NOTE: In practice a MySQL developer tends to use operators IN or EXISTS rather than ANY and ALL.

Using SOME operator?

At the end let's mention the operator SOME. This operator is an alias to ANY, and, according to the MySQL definition, could be used to facilitate or make understandable the syntax when a query will be looking for 'some' data within a range, rather than 'any' data within same range. In other terms, this option exists to make the query more readable, if necessary, for other but English speaking developers. For instance the example below may be written in two different ways:

SELECT column1 FROM table1 WHERE column1 <> ANY  (SELECT column1 FROM table2);

Or:

SELECT column1 FROM table1 WHERE column1 <> SOME (SELECT column1 FROM table2);