MySQL ANY with Example

In SQL language, ANY statement allows to compare a value with the result of a subquery. It is thus possible to check if a value is “equal”, “different”, “greater than”, “greater than or equal to”, “less than” or “less than or equal to” for at least one of the values of the subquery.
 

Syntax:

This command is used in a conditional clause just after the conditional operator and just before a subquery. The following example shows basic use of ANY in an SQL query:

SELECT *
FROM table1
WHERE condition1 > ANY (
    SELECT *
    FROM table2
    WHERE condition2
)

This query can be translated as follows: select all columns of table1, where the condition is greater than any result of the subquery.

Note: the conditional operators can be: =, <, >, <>, !=,<=, >=, !> or !<.
 

 

Example :

Based on the simple example shown above, it is possible to perform a practical query that uses the ANY command:

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

Suppose that table1 has only one result in which column1 is equal to 10.

• The condition is true (cf. TRUE) if table2 contains {20,15,8} because there is at least one value less than 10
• The condition is false (cf. FALSE) if table2 contains {20,10} because no value is strictly less than 10
• The condition is unknown (cf. UNKNOW) if table2 is empty

Tip: IN command is equivalent to the = operator followed by ANY.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More