SQL Exists Operator

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

Syntax of SQL EXISTS Operator

Following is the syntax of exists operator in sql server.

 

SELECT column1, column2 FROM tablename WHERE EXISTS(Subquery)

If you observe above sql EXISTS operator syntax, we will get a values only when 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 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 above script our table “EmployeeDetails” will create and the result will be like as shown below.

 

Newly created employeedetails table in sql server

Now run following examples to check EXISTS operator in sql server.

SQL EXISTS Operator Example

Following sql exists operator example will return an employee details who values are matching with the values returned by subquery.

 

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

When we execute above sql exists operator query, we will get the result like as shown below.

 

SQL Exists Operator Example Output or Result

SQL Exists Operator Example2

Following sql exists operator query will return an employee details whose values are matching with the values returned by subquery.

 

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

When we execute above sql exists operator query, we will get the result like as shown below.

 

SQL Exists Operator Example Result or Output

SQL NOT EXISTS Operator

Generally, in sql statement if we use EXISTS operator, then it will return all the records whose values matches with the subquery return values. Suppose if we use NOT with EXISTS operator, then it will return all the records whose values not match with the suquery values.

SQL Not Exists Operator Example

Following sql not exists operator example will return a records whose values not exists in the subquery returned result or rows.

 

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

When we execute above sql not exists operator query, we will get the result like as shown below.

 

SQL Exists Operator Example Result or Output

SQL Not Exists Operator Example2

Following sql not exists operator example will return a records whose values not exists in the subquery returned result or rows.

 

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

When we execute above sql not exists operator query, we will get the result like as shown below.

 

SQL Not Exists Operator Example Output or Result

Topics Covered