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.

You can also search these topics using sql any and all difference, sql any character, sql any column contains, sql any vs exists.

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
3	Sakunthala	Female	29	Hyderbhad
4	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

You can also search these topics using sql any column is null, sql any operator oracle, t sql any operator, sql any all operators, sql server any operator example, oracle sql any all operator.