The SQL EXISTS Operator is used to restrict the number of rows returned by the SELECT Statement. The EXISTS Operator in SQL check the Subquery for rows existence, and if there are any then it will return TRUE otherwise FALSE.
SQL EXISTS Operator 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: Allows us to choose the number of columns from the tables. It may be One or more.
- Source: One or more tables present in the Database. JOINS are used to join multiple table.
- Subquery: Here we have to provide the Subquery. If the sub query returns true then it will return the records otherwise, Server doesn’t return any records.
For this EXISTS Operator demo, We are going to use the below shown data

SQL EXISTS Operator Example
The following SQL Server Exists operator query will find all the Employees present in the table whose [Sales] is greater than 1000.
TIP: Before get into this Exists operator example, I suggest you to refer the Subquery article to understand the sub query 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 sub query

Let me change the condition as 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 sub query is returning false. Let us show you one more SQL Exists Operator example for better understanding.

The following exists 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 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') )

EXISTS Operator Example 4
You might be wonder, why am I using the 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, this SQL exists is returning all the records present in selected table. Because, the subquery returns TRUE

Comments are closed.