SQL EXISTS OPERATOR

The EXISTS operator is used to check whether data is available or not into table. You can also check this by using inner query within your sql statement.

Let’s understand each with syntax and examples.

MS SQL

Syntax:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE EXISTS
(SELECT COLUMN FROM TABLE_NAME WHERE CONDITION);

Example:

Original Data in table called “Employee” and “Department”

Id Employee Code First Name Last name Gender Birth Date Department Id
1 EMP_1001 John Peter Male 10-11-1982 1
2 EMP_1002 Josh Wills Male 09-07-1980 1
3 EMP_1003 Jeremy Parkar Female 08-09-1990 1
4 EMP_1004 Aidan Walls Male 05-03-1989 3
5 EMP_1005 Adam Mondry Male 02-06-1991 4

Id Department Name
1 Development
2 Quality Analyst
3 Marketing
4 Call Center
5 Delivery


SELECT DepartmentName
FROM Department D
WHERE Exists(Select DepartmentId from Employee E WHERE E.DepartmentId=D.Id)

Above sql statement will check whether department ID is exists in Employee table or not. If exist, then it displays department name otherwise not.


Department Name
Development
Marketing
Call Center


Another example is to find department names from the list of employees whose expense if higher than 200. This sql statement will return only those department names whose employee has expense more than 200 only.


SELECT DepartmentName
FROM Department D
WHERE Exists(Select DepartmentId from Employee E WHERE E.DepartmentId=D.Id and Expense>200)

Department Name
Development
Marketing