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:
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 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
Hi, thx for tutorial. Just a little mistake I found – In SQL EXISTS Operator Example 3 article you have two times word 'whose' right after each other.
Thank you for correcting me.