SQL EXISTS Operator

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

TIP: Before get into this example, I suggest you to refer the SQL Subquery article to understand the sub query designing, and query parsing.

SQL EXISTS Operator Syntax

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

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

• Columns: It 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. SQL JOINS are used to join multiple table.
• Subquery: Here we have to provide the Sub query. If the sub query returns true then it will return the records otherwise, it doesn’t return any records.

In this article we will show you, How to use the EXISTS Operator in SQL Server with examples. For this, We are going to use the below shown data

SQL EXISTS Operator Example 1

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

T-SQL CODE

-- Example for SQL Server EXISTS Operator
USE [SQL Tutorial]
GO
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
		)

OUTPUT

Let me show you the result of the sub query

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

SQL CODE

-- Example for SQL Server EXISTS Operator
USE [SQL Tutorial]
GO
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
		)

OUTPUT

As you can see that the query is returning Empty records, because the sub query is returning false. Let us show you one more example for better understanding.

SQL EXISTS Operator Example 2

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

SQL CODE

-- Example for SQL Server EXISTS Operator
USE [SQL Tutorial]
GO
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'
		)

OUTPUT

SQL EXISTS Operator Example 3

The following query will find all the Employees present in the Employees table whose Occupation is either Management or Professional or Clerical. Here we are going to use the SQL IN Operator inside the Sub Query

T-SQL CODE

-- Example for SQL Server EXISTS Operator
USE [SQL Tutorial]
GO
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')
		)

OUTPUT

SQL EXISTS Operator Example 4

You might be wonder, why am I using the EMP1.[EmpID] = EMP2.[EmpID] inside the sub query. So let me remove that line and see

SQL CODE

-- Example for SQL Server EXISTS Operator
USE [SQL Tutorial]
GO
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')
		)

OUTPUT

As you can see from the above screenshot, it is returning all the records present in the employee tablet. Because, the sub query returns TRUE

Thank You for Visiting Our Blog