SQL EXISTS Operator 5

SQL EXISTS Operator

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

Employee Table Data 1

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
		)
SQL EXISTS Operator in Subquery 2

Let me show you the result of the sub query

Subquery Result 3

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.

SQL EXISTS Operator 4

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'
		)
SQL EXISTS Operator 5

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

SQL EXISTS Operator 8

Comments are closed.