SQL NOT EXISTS

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.

SYNTAX

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.

Also Read..

SQL EXISTS