SQL - EXISTS Operator

The EXISTS operator is used to check the existance of records in a subquery.

The EXISTS operator will return TRUE if a subquery returns at least one record, otherwise returns FALSE. If EXISTS return TRUE then only the outer query will be executed.

Syntax:

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

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' '[email protected]' 33000 1
2 'James' 'Bond'
3 'Neena' 'Kochhar' '[email protected]' 17000 2
4 'Lex' 'De Haan' '[email protected]' 15000 1
5 'Amit' 'Patel' 18000
6 'Abdul' 'Kalam' '[email protected]' 25000 2
Department Table
DeptId Name
1 'Finance'
2 'HR'
3 'Sales'

The following query checks the employees whose departments are listed in the Departments table.

SQL Script: Left Join Query
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE EXISTS (SELECT * FROM Employee WHERE Salary > 15000);
EmpId FirstName LastName Salary
1 'John' 'King' 33000
2 'James' 'Bond'
3 'Neena' 'Kochhar' 17000
4 'Lex' 'De Haan' 15000
5 'Amit' 'Patel' 18000
6 'Abdul' 'Kalam' 25000
SQL Script: Left Join Query
SELECT EmpId, FirstName, LastName FROM Employee
WHERE EXISTS (SELECT * FROM Employee WHERE Salary > 30000);
EmpId FirstName LastName
1 'John' 'King'
2 'James' 'Bond'
3 'Neena' 'Kochhar'
4 'Lex' 'De Haan'
5 'Amit' 'Patel'
6 'Abdul' 'Kalam'
SQL Script: Left Join Query
SELECT EmpId, FirstName, LastName FROM Employee
WHERE EXISTS (SELECT * FROM Employees WHERE Salary > 35000);
EmpId FirstName LastName
     
SQL Script: Left Join Query
SELECT EmpId,FirstName FROM Employee WHERE EXISTS 
(SELECT DeptId FROM Department WHERE Department.DeptId = Employee.DeptId);
EmpId FirstName LastName
1 'John' 'King'
3 'Neena' 'Kochhar'
4 'Lex' 'De Haan'
6 'Abdul' 'Kalam'

NOT EXISTS

Use the NOT operator with EXISTS to reverse the effect of EXISTS.

SQL Script: Left Join Query
SELECT EmpId, FirstName FROM Employee WHERE NOT EXISTS 
(SELECT DeptId FROM Department WHERE Department.DeptId = Employee.DeptId);
EmpId FirstName LastName
2 'James' 'Bond'
5 'Amit' 'Patel'