The SQL NOT EXISTS Operator is used to check the existence of any record in a subquery.
The NOT EXISTS operator returns true if the subquery does not contains any records otherwise it returns false.
SELECT column_name(s) FROM table name WHERE NOT EXISTS (subquery)
Lets look at an example of using SQL NOT EXISTS operator in SQL statement.
First we will prepare a sample table and insert some records in table.
Lets create a Employee tables, and insert data in table using below scripts.
CREATE TABLE [dbo].[Employee]( [Emp_Id] [int] NOT NULL PRIMARY KEY, [First_Name] [varchar](250) NOT NULL, [Last_Name] [varchar](250) NOT NULL, [Salary] [int] ) INSERT INTO dbo.Employee VALUES(1, 'Rajesh', 'Singh', 10000), (2, 'Amit','Agarwal', 15000), (3, 'Sanjay', 'Moishra', 17000), (4, 'Sujoy', 'Gosh', 9000), (5, 'karthik','Chauhan', 25000), (6, 'Chand', 'Kaur', 27000), (7, 'Lovnish', 'Gupta', 28000), (8, 'Munish', 'Rawat', 11000), (9, 'Ramesh', 'Kumar', 5000), (10, 'Manoj', 'Negi', 30000), (11, 'Ajay', 'Singh', 24000)
Now we have a Employee table in SQL Server as shown in below screenshot.
SELECT * FROM dbo.Employee
Lets write the query which will returns the employee whose salary is less than 25000.
SELECT * FROM dbo.Employee Emp1 WHERE NOT EXISTS ( SELECT * FROM dbo.Employee Emp2 WHERE Emp1.Emp_Id = Emp2.Emp_Id AND Emp2.Salary >=25000 )
You can see, it returns the details of those employees whose salary are less than 25000.
Lets understand the output of above query, for given condition subquery evaluates and checks every records from the outer query and based on that returns TRUE, if records does not exist otherwise FALSE and the result of subquery is used by outer query. Outer query returns the details of those employees only for which subquery evaluated as true.
So, basically SQL NOT EXISTS returns all the records which does not satisfy the EXISTS condition.