The SQL NOT EXISTS Operator will act quite opposite to SQL EXISTS Operator. It is used to restrict the number of rows returned by the SELECT Statement. The NOT EXISTS Operator in SQL Server will check the Subquery for rows existence, and if there are no rows then it will return TRUE, otherwise FALSE. Or we can simply say, NOT EXISTS operator will return the results exactly opposite to the result returned by the Sub query.
TIP: Before go into this example, I suggest you to refer the SQL Subquery article to understand the sub query designing, and query parsing.
SQL NOT EXISTS Operator Syntax
The basic syntax of the NOT EXISTS operator in SQL Server can be written as:
|
1 2 3 |
SELECT [Column Names] FROM [Source] WHERE NOT 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 NOT EXISTS Operator in SQL Server with examples. For this, We are going to use the below shown data
SQL NOT EXISTS Operator Example 1
The following query will find all the Employees present in the Employees table whose [Sales] is less than 1000
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Example for SQL Server NOT EXISTS Operator USE [SQL Tutorial] GO SELECT Employ1.[EmpID] ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name] ,Employ1.[Education] ,Employ1.[Occupation] ,Employ1.[YearlyIncome] ,Employ1.[Sales] ,Employ1.[HireDate] FROM [Employee] AS Employ1 WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 WHERE Employ1.[EmpID] = Employ2.[EmpID] AND [Sales] > 1000 ) |
OUTPUT
Let me change the condition as Sales < 10000, it means sub query will return all the records. And the NOT EXISTS will return zero records because it will return exactly opposite result of the sub query.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Example for SQL Server NOT EXISTS Operator USE [SQL Tutorial] GO SELECT Employ1.[EmpID] ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name] ,Employ1.[Education] ,Employ1.[Occupation] ,Employ1.[YearlyIncome] ,Employ1.[Sales] ,Employ1.[HireDate] FROM [Employee] AS Employ1 WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 WHERE Employ1.[EmpID] = Employ2.[EmpID] AND [Sales] < 10000 ) |
OUTPUT
As you can see that the query is returning Empty records, because the sub query is returning TRUE, and Not exists will return false. Let us show you one more example for better understanding.
Let me change the condition as Sales > 10000, which is a false condition. So, NOT EXISTS will return all the records.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Example for SQL Server NOT EXISTS Operator USE [SQL Tutorial] GO SELECT Employ1.[EmpID] ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name] ,Employ1.[Education] ,Employ1.[Occupation] ,Employ1.[YearlyIncome] ,Employ1.[Sales] ,Employ1.[HireDate] FROM [Employee] AS Employ1 WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 WHERE Employ1.[EmpID] = Employ2.[EmpID] AND [Sales] > 10000 ) |
OUTPUT
As you can see from the above screenshot, it is returning all the records present in the employee table. Because, the sub query returns FALSE, it means NOT EXISTS will return TRUE
SQL EXISTS Operator Example 2
The following query will find all the Employees present in the Employees table whose Occupation is neither Skilled Manual nor 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 |
-- Example for SQL Server NOT EXISTS Operator USE [SQL Tutorial] GO SELECT Employ1.[EmpID] ,Employ1.[FirstName] + ' ' + Employ1.[LastName] AS [Full Name] ,Employ1.[Education] ,Employ1.[Occupation] ,Employ1.[YearlyIncome] ,Employ1.[Sales] ,Employ1.[HireDate] FROM [Employee] AS Employ1 WHERE NOT EXISTS( SELECT * FROM [Employee] AS Employ2 WHERE Employ1.[EmpID] = Employ2.[EmpID] AND [Occupation] IN ('Skilled Manual', 'Clerical') ) |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply