;

SQL EXISTS

In this tutorial, we will learn how to use EXISTS operator and checking for the existence of any record in a subquery.

SQL EXISTS Operator

  • SQL EXISTS operator is used to checking for the existence of any record in a subquery.
  • SQL EXISTS operator returns true if the subquery returns one or more records.
Syntax - EXISTS Operator
SELECT column1,column2,....columnN
FROM tableName
WHERE EXISTS
(SELECT columnName FROM tableName WHERE CONDITION);

Example:

Let us consider three tables "Employee" and "Department"  for records 

Table Name: Employee

ID EmpName City Country Gender Salary DeptID
1 Shankar Delhi India male 25000 1
2 Sourabh Delhi India male 30000 1
3 Ranvijay Mumbai India male 15000 2
4 Kapil Noida India male 25000 3
5 Shalini Jaipur India female 18000 2
6 Rakesh Faridabad India male 23000 1
7 Akshay Mumbai India male 21000 2
8 Sarah New York US female 76000 3
9 Rocky Noida India male 28000 3

Table: Department

DeptID DepartmentName
1 IT
2 Finance
3 HR

Example of EXISTS Operator

For selecting employees if salary exists greater than 25000, a query will be:

Example - EXISTS Operator
SELECT EmpName,Salary 
FROM Employee AS EMP
WHERE EXISTS (SELECT Salary FROM Employee WHERE ID=EMP.ID and Salary>25000);
Output
EmpName Salary
Sourabh 30000
Sarah 76000
Rocky 28000

For selecting employees if salary exists NOT greater than 25000, a query will be:

Example
SELECT EmpName,Salary 
FROM Employee AS EMP
WHERE EXISTS (SELECT Salary FROM Employee WHERE ID=EMP.ID and NOT Salary>25000);
Syntax - SELECT Statement
EmpName Salary
Shankar 25000
Ranvijay 15000
Kapil 25000
Shalini 18000
Rakesh 23000
Akshay 21000