T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - ANY Operator



The T-SQL (Transact-SQL) ANY Operator 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 operator in T-SQL (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

❮ T-SQL - Operators