SQL Exists Operator

In SQL, the EXISTS operator is useful to show the results if the subquery returns data. Generally, we will use EXISTS operator in the WHERE clause to check whether the subquery has return values or not.

Syntax of SQL EXISTS Operator

Following is the syntax of exists operator in the SQL server.

SELECT column1, column2 FROM tablename WHERE EXISTS(Subquery)

If you observe the above SQL EXISTS operator syntax, we will get values only when the subquery returns any data or rows otherwise, it will not return any data.

Now, we will check exists operator with examples for that create “EmployeeDetails” table by using the following script in your SQL database.

create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))

 insert into EmployeeDetails
 values(1,'suresh','software engineer',25000,'chennai'),
 (2,'rohini','AEO',15000,'chennai'),
 (3,'madhavsai','business analyst',50000,'nagpur'),
 (4,'mahendra','CA',75000,'guntur'),
 (5,'sateesh','Doctor',65000,'guntur')

 select * from EmployeeDetails

Once we execute the above script, our table “EmployeeDetails” will create and return the result as shown below.

Newly created employeedetails table in sql server

Now run the following examples to check EXISTS operator in the SQL server.

SQL EXISTS Operator Example

Following SQL exists operator example will return employee details whose values match with the values returned by the subquery.

SELECT * FROM EmployeeDetails WHERE EXISTS(SELECT * FROM EmployeeDetails WHERE empid = 1)

When we execute the above SQL exists operator query, we will get the following result.

SQL Exists Operator Example Output or Result

SQL Exists Operator Example2

Following SQL exists operator query will return employee details whose values match with the values returned by the subquery.

SELECT * FROM EmployeeDetails WHERE EXISTS(SELECT * FROM EmployeeDetails WHERE empid = 100)

When we execute the above SQL exists operator query, we will get the following result.

SQL Exists Operator Example Result or Output

SQL NOT EXISTS Operator

The EXISTS operator will return all the records whose values match with the subquery return values. Suppose, if we use NOT with EXISTS operator, it will return all the records whose values do not match the subquery values.

SQL Not Exists Operator Example

Following SQL not exists operator example will return records whose values do not exist in the subquery returned result or rows.

SELECT * FROM EmployeeDetails WHERE NOT EXISTS(SELECT * FROM EmployeeDetails WHERE empid = 1)

When we execute the above SQL not exists operator query, we will get the following result.