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.

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

SELECT EmpId, FirstName, LastName, Salary FROM Employee WHERE EXISTS (SELECT * FROM Employee WHERE Salary > 15000);

SELECT EmpId, FirstName, LastName FROM Employee WHERE EXISTS (SELECT * FROM Employee WHERE Salary > 30000);

SELECT EmpId, FirstName, LastName FROM Employee WHERE EXISTS (SELECT * FROM Employees WHERE Salary > 35000);

SELECT EmpId,FirstName FROM Employee WHERE EXISTS (SELECT DeptId FROM Department WHERE Department.DeptId = Employee.DeptId);

NOT EXISTS

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

SELECT EmpId, FirstName FROM Employee WHERE NOT EXISTS (SELECT DeptId FROM Department WHERE Department.DeptId = Employee.DeptId);