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.
Related Links
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)
- 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 |
Related Links