SQL EXISTS Operator

The SQL EXISTS Operator is used to restrict the number of rows returned by the SELECT Statement. The EXISTS Operator checks the Subquery for row’s existence, and if there are any, then it will return TRUE otherwise, FALSE.

Syntax

The basic syntax of the SQL Server EXISTS operator can be written as:

SELECT [Column Names]
FROM [Source]
WHERE EXISTS (Write Subquery to Check)

• Columns: This allows us to choose the number of columns from the tables. It may be One or more.
• Source: One or more tables are present in the Database. JOINS are used to join multiple tables.
• Subquery: Here, we have to provide the Subquery. If the subquery returns true, then it will return the records. Otherwise, the Server doesn’t return any records.

For this demo, We are going to use the below-shown data

SQL EXISTS Operator Example

The following query will find all the Employees present in the table whose [Sales] is greater than 1000.

TIP: Before getting into this Exists operator example, I suggest you refer to the Subquery article to understand the subquery designing and query parsing.

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
			AND [Sales] > 1000
		)

Let me show you the result of the subquery.

Let me change the condition to Sales = 1000, which is a false condition

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
			AND [Sales] = 1000
		)

As you can see that the query is returning Empty records because the subquery is returning false. Let us show you one more Exists Operator example for a better understanding.

The following query will find all the Employees within the table whose [Occupation] is equal to Management

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
			AND [Occupation] = 'Management'
		)

The following SQL Server exists operator query will find all the persons present in the table whose Occupation is either Management, Professional or Clerical.

Here we are going to use the IN Operator inside the SubQuery.

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE EMP1.[EmpID] = EMP2.[EmpID] 
		  AND [Occupation] IN ('Management', 'Professional', 'Clerical')
		)

Example 4

You might be wondering why I am using EMP1.[EmpID] = EMP2.[EmpID] inside the subquery. So let me remove that line and see the result set.

SELECT EMP1.[EmpID]
      ,EMP1.[FirstName]
      ,EMP1.[LastName]
      ,EMP1.[Education]
      ,EMP1.[Occupation]
      ,EMP1.[YearlyIncome]
      ,EMP1.[Sales]
      ,EMP1.[HireDate]
  FROM [Employee] AS EMP1
  WHERE EXISTS( SELECT * FROM [Employee] AS EMP2 
		WHERE [Occupation] IN ('Management', 'Professional', 'Clerical')
		)

As you can see from the below screenshot, it returns all the records present in the selected table. Because the subquery returns TRUE