EXISTS Operator

EXISTS Operator in SQL Server

In this SQL Server article, I will explain how to use the EXISTS operator in SQL Server (Transact-SQL) with syntax and examples.

The SQL Server EXISTS operator is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

The EXISTS operator in SQL Server is used to checks the existence of a result of a subquery. Generally, we use this EXISTS operator in WHERE clause to check whether subquery is returning results or not. The EXISTS subquery tests whether a subquery fetches at least one-row or not. When no data is returned then this operator returns ‘FALSE’. 

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE [NOT] EXISTS (subquery)
Parameters:

column_name: Name of the column of the table.

expression1: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

table_name: Name of the table.

WHERE [NOT] EXISTS: Tests the subquery for the existence of one or more rows. If a single row satisfies the subquery clause, it returns Boolean TRUE. When the subquery returns no matching rows the optional NOT keyword returns a Boolean TRUE.

Subquery: The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

Note: SQL statements that use the EXISTS condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

If you observe above SQL EXISTS operator syntax we will get values where the subquery returns any data or rows otherwise it will not return any data. We will check this operator with an example of that create “EmployeeDetails” and “EmployeeContactDetails” table by using the following script in your database.

Use below script to create and populate the “EmployeeDetails” and “EmployeeContactDetails” tables with some test data.
CREATE TABLE EmployeeDetails
(
  ID INT, 
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50),
  Salary INT,
  Location VARCHAR(50)
)
GO
 
INSERT INTO EmployeeDetails VALUES(1, 'Pranaya', 'Male','IT', 25000, 'Mumbai')
INSERT INTO EmployeeDetails VALUES(2, 'Anurag', 'Male','HR', 15000, 'Mumbai')
INSERT INTO EmployeeDetails VALUES(3, 'Priyanka', 'Female','IT', 50000, 'Hyderabad')
INSERT INTO EmployeeDetails VALUES(4, 'Preety', 'Female','IT', 75000, 'Delhi')
INSERT INTO EmployeeDetails VALUES(5, 'Subrat', 'Male','HR', 65000, 'Delhi')
INSERT INTO EmployeeDetails VALUES(6, 'Hina', 'Female','Sales', 55000, 'Hyderabad')
INSERT INTO EmployeeDetails VALUES(7, 'Sushanta', 'Male','Sales', 20000, 'Delhi')
GO
Fetch the records

SELECT * FROM EmployeeDetails

EXISTS Operator in SQL Server

CREATE TABLE EmployeeContactDetails
(
  ID INT, 
  EmployeeID VARCHAR(50),
  Email VARCHAR(50),
  Mobile VARCHAR(10),
  PinCode VARCHAR(20)
)
GO
 
INSERT INTO EmployeeContactDetails VALUES(1,1, 'Pranaya@g.com', '1111111111', '755019')
INSERT INTO EmployeeContactDetails VALUES(2,2, 'Anurag@g.com', '2222222222', '755020')
INSERT INTO EmployeeContactDetails VALUES(3,5, 'Priyanka@g.com', '3333333333', '755030')
INSERT INTO EmployeeContactDetails VALUES(4,6, 'Preety@g.com', '4444444444', '755040')
GO
Fetch the records

SELECT * FROM EmployeeContactDetails

EXISTS Operator in SQL Server

Example – With SELECT Statement

Let’s look at a simple example.

The following is a Select statement that uses the EXISTS condition:

SELECT *
FROM EmployeeDetails
WHERE EXISTS (SELECT *
              FROM EmployeeContactDetails
              WHERE EmployeeDetails.ID = EmployeeContactDetails.EmployeeID);

Result:

EXISTS Operator in SQL Server

This SQL Server EXISTS condition example will return all records from the EmployeeDetails table where there is at least one record in the EmployeeContactDetailsn table with a matching employee ID.

Example – With SELECT Statement using NOT EXISTS

The SQL Server EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM EmployeeDetails
WHERE NOT EXISTS (SELECT *
              FROM EmployeeContactDetails
              WHERE EmployeeDetails.ID = EmployeeContactDetails.EmployeeID);

Result:

EXISTS Operator in SQL Server

This SQL Server EXISTS Operator example will return all records from the EmployeeDetails table where there are no records in the EmployeeContactDetails for the matching employee ID column.

In the next article, I will discuss UNION and UNION ALL SET Operators in SQL Server with some examples.

SUMMARY

In this article, I try to explain the EXISTS Operator in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *