Sql ANY Operator


The SQL ANY operator are used on subqueries that return multiple values.

The SQL ANY operator returns true if any of the subquery values (single value or from list of values) meet the condition on outer table query.

It can be used on any valid sql select statement with where and having clause.


Sql ANY Syntax

The below syntax is used to create any operator with sub query or inner query.


SELECT column-name1, column-name2, ... column-nameN 
FROM table-name 
WHERE column-name operator ANY 
(SELECT column-name FROM table-name WHERE condition)

Note:-
  • The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Sample Database Table - EmpInfo

ID EmpName Gender Age City
1 Devi Mai Female 24 Bangalore
2 Azaghu Varshith Male 23 Kumbakonam
3 Geetha Female 21 Mumbai
4 Sakunthala Female 29 Hyderbhad
5 Pandurengan Male 26 Mumbai
6 Vidyavathi Female 24 Aruppukoottai

SQL ANY Operator Example With Numeric Field

The following SQL statement will display records from "EmpInfo" table and It compares a value from main table to each value in a list or results from a subquery and evaluates to true if any of the subquery values meet the condition.


SELECT * FROM EmpInfo 
WHERE Age > ANY (SELECT Age FROM EmpInfo WHERE ID > 4)

The sub-query returns 5 and 6. i.e, These are the ages (26, 24) for which employee "ID" is greater than "4".

Then main query is compared with this, if any of the ages in table is greater than any of the values of sub-query (i.e the ages in table should be either greater than 26 or 24) .

The result of above query is:

ID EmpName Gender Age City
4 Sakunthala Female 29 Hyderbhad
5 Pandurengan Male 26 Mumbai

SQL ANY Operator Example With Character Field

See below SQL statement, it contains character datatype to perform sql any operator.


SELECT * FROM EmpInfo 
WHERE City = ANY (SELECT City FROM EmpInfo WHERE Age < 24)

The sub-query returns "Kumbakonam" and "Mumbai" for which employee "Age" is less than "24".

Then main query is compared with this, if any of the cities in table is matching any of the values of sub-query (i.e the cities in table should be equal to "Kumbakonam" or "Mumbai").

The result of above query is:

ID EmpName Gender Age City
2 Azaghu Varshith Male 23 Kumbakonam
3 Geetha Female 21 Mumbai
5 Pandurengan Male 26 Mumbai