EXISTS Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

EXISTS Operator in SQL Server with Examples

In this article, I am going to discuss EXISTS Operator in SQL Server with Examples. Please read our previous article where we discussed the SQL Server SOME Operator with examples. At the end of this article, you will understand what exactly EXISTS Operator is and when and how to use this operator in SQL Server.

What is EXISTS Operator in SQL Server?

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 is used to checks the existence of a result of a subquery. Generally, we use this EXISTS operator in the WHERE clause to check whether the 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’. 

EXISTS Operator Syntax in SQL Server:

Parameters:

1. column_name: Name of the column of the table.
2. 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.
3. table_name: Name of the table.
4. 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.
5. 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 as false and the EXISTS condition will not be met.

Points to Remember while working with SQL Server EXISTS Operator

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 the above SQL EXISTS operator syntax we will get values where the subquery returns any data or rows otherwise it will not return any data.

Understanding the EXISTS Operator in SQL Server with Examples:

Let us understand EXISTS Operator with some examples. We are going to use the following EmployeeDetails and EmployeeContactDetails tables to understand EXISTS Operator.

Please use the below SQL Script to create and populate the EmployeeDetails and EmployeeContactDetails tables with the required 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

EmployeeContactDetails table with data:

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

EXISTS Operator With SELECT Statement

Let us see how to use the Exists Operator with SELECT Statement in SQL Server. In the following example, we use the EXISTS operator with the Select statement. Here, the EXISTS condition will check and return all records from the EmployeeDetails table where there is at least one record in the EmployeeContactDetails table with a matching employee ID.

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

When you execute the above SQL Query, you will get the following output.

SELECT Statement using NOT EXISTS

The SQL Server EXISTS Operator can also be used with the NOT operator. This will work as opposed to the EXISTS Operator. See the following example for a better understanding. Here, the 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.

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

You will get the following output when you execute the above SQL Query.

In the next article, I am going to discuss UNION and UNION ALL SET Operators in SQL Server with Examples. Here, In this article, I try to explain the EXISTS Operator in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.