Tutorial Gateway

SQL EXISTS Operator

by

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 EXISTS operator in SQL Server 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. SQL 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, SQL Server doesn’t return any records.

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

SQL EXISTS Operator 1

SQL EXISTS Operator Example 1

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

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

-- 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

SQL EXISTS Operator 2

Let me show you the result of the sub query

SQL EXISTS Operator 3

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

-- 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

SQL EXISTS Operator 4

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.

EXISTS Operator Example 2

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

-- 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 5

EXISTS Operator Example 3

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

-- 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 7

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

-- 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

SQL EXISTS Operator 8

As you can see from the above screenshot, this SQL exists is returning all the records present in selected table. Because, the subquery returns TRUE

Placed Under: SQL