SQL NOT EXISTS Operator

The SQL NOT EXISTS Operator will act quite opposite to the EXISTS Operator. It is used to restrict the number of rows returned by the SELECT Statement.

The NOT EXISTS operator 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, that the Not Exists operator will return the results exactly opposite to the result returned by the Subquery.

Before going into this example, I suggest you to refer the SQL Subquery article to understand the subquery designing and query parsing.

SQL NOT EXISTS Syntax

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

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 tables.
  • Subquery: Here we have to provide the Subquery. If the subquery 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 SQL Server NOT EXISTS Operator with examples. For this, we are going to use the table data that we mentioned in the NOT IN operator article.

SQL NOT EXISTS Example 1

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

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
		  )

Please refer to the last image to see the result set returned by the above query.

Let me change the Not Exists condition to Sales < 10000, it means the subquery will return all the available rows. And the NOT EXISTS will return zero records because it will return the exact opposite result of the subquery.

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
		  )

It returns an empty table with the following message.

(0 rows affected)

As you can see that the query is returning Empty records, because the subquery is returning TRUE, and Not exists will return false. Let us show you one more example for better understanding.

Let me change the condition to Sales > 10000, which is a false condition. So, the NOT EXISTS operator will return all the records. For the demo purpose, we used the Sales > 3000 in the image.

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
		  )

As you can see from the screenshot below, it is returning all the rows with sales that are not greater than 3000. Because the subquery returns FALSE, it means the NOT EXISTS will return TRUE.

Not Exists Example 2

The following SQL Server Not Exists query will find the Employees whose Occupation is neither Skilled Manual nor Clerical. Here, we are going to use the IN Operator inside the Subquery

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

NOT EXISTS Operator Example
Categories SQL