SQL EXISTS Operator

The SQL EXISTS Operator is used to check the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records.

SYNTAX

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

 

Lets look at an example of using SQL EXISTS operator in SQL statement.

First we will prepare a sample tables and insert some records into these tables.

Lets create a Department and Employee tables, and insert data into tables using below scripts.

CREATE TABLE [dbo].[Department](
[Dept_ID] [int] NOT NULL PRIMARY KEY,
[Dept_Name] [varchar](100) NOT NULL,
[Dept_Code] [varchar](10) NULL
)

INSERT INTO dbo.Department
(Dept_ID, Dept_Name, Dept_Code)
VALUES(501, 'IT', '001'),
(502, 'FINANCE', '002'),
(503, 'HR', '003'),
(504, 'MIS', '004'),
(505, 'CALL CENTER', '005'),
(506, 'SALES', '006')

CREATE TABLE [dbo].[Employee](
[Emp_Id] [int] NOT NULL PRIMARY KEY,
[First_Name] [varchar](250) NOT NULL,
[Last_Name] [varchar](250) NOT NULL,
[Dept_Id] [int] REFERENCES [dbo].[Department] ([Dept_ID])
)

INSERT INTO dbo.Employee
VALUES(1, 'Rajesh', 'Singh', 501),
(2, 'Amit','Agarwal', 502),
(3, 'Sanjay', 'Moishra', 501),
(4, 'Sujoy', 'Gosh', 504),
(5, 'karthik','Chauhan', 505),
(6, 'Chand', 'Kaur', 504),
(7, 'Lovnish', 'Gupta', 504),
(8, 'Munish', 'Rawat', 502)

Now we have a Department and Employee table in SQL Server as shown in below screenshot.

SELECT * FROM dbo.Department
SELECT * FROM dbo.Employee

The following statement uses Exists operator that returns all the employees from Employee table within Department table.

 

SELECT * FROM dbo.Employee EMP
WHERE EXISTS (
                SELECT * FROM Dbo.Department Dpt 
                WHERE EMP.Dept_Id =Dpt.Dept_Id 
              )

As you can see above result set, It returns all the employees from employee table whose department exists in department table.

Now Lets say, you want to see only those department details which are assigned to employees.

Following statement uses Exists operator and returns only those departament details which are assigned to employees.

 

SELECT * FROM dbo.Department Dpt
WHERE EXISTS (
                     SELECT * FROM Dbo.Employee Emp 
                     WHERE EMP.Dept_Id =Dpt.Dept_Id 
 )

Lets fetch the details of those employees within department whose Department is either ‘FINANCE’ or ‘IT’.

SELECT * FROM dbo.Employee EMP
WHERE EXISTS (
          SELECT * FROM Dbo.Department Dpt
          WHERE EMP.Dept_Id =Dpt.Dept_Id AND Dept_Name IN ('FINANCE','IT')
           )

As you can see above query result set, It returns only those Employees within department table whose Department Name  is either ‘FINANCE’ or ‘IT’.

Lets see what happens if you do not use condition Emp.Dept_Id =Dpt.Dept_Id inside the subquery.

Lets modify the above SQL statement by removing condition EMP.Dept_Id =Dpt.Dept_Id from subquery.

You can see following SQL statement, inside subquery we have removed condition Emp.Dept_Id =Dpt.Dept_Id.

Lets execute the query and see the output.

SELECT * FROM dbo.Employee EMP
WHERE EXISTS (
             SELECT * FROM Dbo.Department Dpt
             WHERE  Dept_Name IN ('FINANCE', 'IT')
            )

 

You can see below result set of statement, It returns all the employee’s details from Employee table even after using a condition on department name that is ‘FINANCE’, and ‘IT’.

Now you will be thinking, why did it return all the employee’s details while subquery using a condition on department name.

It happens because EXISTS operator check the existence of any record in a subquery, and subquery returns the data for provided condition as Department table having data for ‘FINANCE ‘and ‘IT’, so it returns true that’s why EXISTS operator returns all the data from employee table.

 

Also Read..

SQL Server SubQuery