SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server - ANY Keyword



The SQL Server (Transact-SQL) ANY keyword is used with WHERE and HAVING clause. It returns TRUE if any of the subquery values satisfies the condition.

Syntax

The syntax for using ANY keyword in SQL Server (Transact-SQL) is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name operator ANY
(SELECT column1, column2, ... 
FROM table_name WHERE condition);

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

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • To select records of male employees from Employee table, the query is given below.

    SELECT * FROM Employee
    WHERE EmpID = ANY
    (SELECT EmpID 
    FROM Contact_Info WHERE Gender = 'M');
    

    The subquery will produce following result:

    EmpID
    3
    6
    7
    8

    The query then compares EmpID values with subquery result. It returns TRUE as few records satisfy the condition and produces the following result:

    EmpIDNameCityAgeSalary
    3JoParis272800
    6HuangBeijing282800

❮ SQL Server Keywords