The ANY keyword is a MySQL operator that returns the Boolean value TRUE if the comparison is TRUE for ANY of the subquery condition. In other words, this keyword returns true if any of the subquery condition is fulfilled when the SQL query is executed. The ANY keyword must follow the comparison operator. It is noted that ALL SQL operator works related to ANY operator, but it returns true when all the subquery values are satisfied by the condition in MySQL.
The ANY operator works like comparing the value of a table to each value in the result set provided by the subquery condition. And then, if it finds any value that matches at least one value/row of the subquery, it returns the TRUE result.
The following is the syntax that illustrates the use of ANY operator in MySQL:
Where comparison operators can be one of the following:
This syntax can also be written as:
We can understand how ANY works in MySQL through the below statement:
Suppose table1 has a row that contains a number (10). In such a case, the above expression returns true if table2 contains (20, 15, and 6). It is because there is a value 6 in table2, which is less than 10. This expression returns false if table2 contains (15, 20), or if table2 is empty. If all the table fields contain (NULL, NULL, NULL), this expression is unknown.
Let us create a two table named table1 and table2 and then insert some values into them using the below statements:
After successful execution of the above statement, we can verify it by using the SELECT statement as follows:
Now, we will execute the below statement to understand the use of the ANY operator:
This statement returns true and gives the below output because table2 contains (20, 10, and 7) and there is a value 7 in table2 which is less than 10, 20, and 25 of table1.
The IN keyword in MySQL is an alias for = ANY when used with a subquery in the statement. Hence, the below two statements are identical in MySQL:
But we cannot say that IN and = ANY were synonyms when we used it with an expression list. It is because IN can take a list of expressions, but = ANY cannot.
Also, NOT IN cannot be an alias for <> ANY operator, but it can be used for <> ALL.
The word SOME in MySQL can be an alias for ANY. Therefore, these two SQL statements are equivalent:
Advantages of ANY operator in MySQL
In this article, we have learned how to use the ANY operator in MySQL. It filters the result set from SQL syntax only when any of the values satisfy the condition. Otherwise, it gives a false value.