SQL Server - EXISTS Operator

The SQL Server (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 SQL Server (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

Table 2: Contact_Info table

• The below SQL Server 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:

  EmpID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  4	Kim	Amsterdam	30	3100

• The below mentioned SQL Server 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:

  EmpID	Name	City	Age	Salary
  3	Jo	Paris	27	2800
  6	Huang	Beijing	28	2800