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

T-SQL - EXISTS Operator



The T-SQL (Transact-SQL) EXISTS Operator is used to test the existence of records from a subquery. It returns true if the subquery returns one or more records, else returns false.

Syntax

The syntax for using EXISTS operator in T-SQL (Transact-SQL) is given below:

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

Example:

Example:

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

Table 1: Employee table

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

  • The below T-SQL statement returns TRUE and records of female employees from Employee table.

    SELECT * FROM Employee
    WHERE EXISTS 
    (SELECT EmpID FROM Contact_Info 
    WHERE Employee.EmpID = Contact_Info.EmpID AND Gender = 'F');
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
  • The below mentioned T-SQL statement returns TRUE and records of male employees from Employee table.

    SELECT * FROM Employee
    WHERE EXISTS 
    (SELECT EmpID FROM Contact_Info 
    WHERE Employee.EmpID = Contact_Info.EmpID AND Gender = 'M');
    

    This result of above query will be:

    EmpIDNameCityAgeSalary
    3JoParis272800
    6HuangBeijing282800

❮ T-SQL - Operators