MySQL ANY

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.

Syntax

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:

SELECT column_lists FROM table_name1 WHERE column_name Operator ANY (SELECT column_name FROM table_name2 WHERE condition); 

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.

Example

Let us create a two table named table1 and table2 and then insert some values into them using the below statements:

CREATE TABLE table1 (
	num_value INT
); 
INSERT INTO table1 (num_value) 
VALUES(10), (20), (25);

CREATE TABLE table2 (
	num_val int
); 
INSERT INTO table2 (num_val)
VALUES(20), (7), (10);

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:

SELECT num_value FROM table1 
WHERE num_value > ANY (SELECT num_val FROM table2);

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:

SELECT colm1 FROM table1 WHERE colm1 = ANY (SELECT colm1 FROM table2);
SELECT colm1 FROM table1 WHERE colm1 IN (SELECT colm1 FROM table2);

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:

SELECT colm1 FROM table1 WHERE colm1 <>ANY (SELECT colm1 FROM table2);
SELECT colm1 FROM table1 WHERE colm1 <> SOME (SELECT colm1 FROM table2);

Advantages of ANY operator in MySQL

ANY is a logical operator that returns the Boolean value. It allows us to select any or some rows of the SELECT statement.
Since comparison operators precede this operator, it always returns TRUE if any subqueries satisfy the specified condition.
It provides the result, which is a unique column value from a table that matches any record in the second table.
We can perform several comparisons using ANY operator with the SELECT and WHERE keywords.

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.

Next TopicMySQL Exists

← prev next →