SQL Server EXISTS

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the EXISTS operator in a SQL statement to evaluate a SELECT SUBQUERY.

What is EXISTS in SQL Server?

EXISTS is a logical operator which is used to evaluate a SELECT SUBQUERY to determine whether it returns any row of information from the table. The outcome of the operation is a Boolean value which is either TRUE (1) or FALSE (0). If the subquery returns a row the EXISTS operation succeeds and evaluates to TRUE and exits and vice versa. There is also a counter part to the EXISTS operator called NOT EXISTS which returns TRUE when the evaluated subquery does not fetch any rows from the table.

The EXISTS and NOT EXISTS operators are used very commonly with CORRELATED SUBQUERIES.

Operation

The EXISTS or NOT EXISTS operators are used to evaluate subqueries which are part of SELECT, INSERT, UPDATE, and DELETE statements. As mentioned above the EXISTS or NOT EXISTS operators do not return any resultset or records but only the Boolean values.

SQL Server EXISTS Syntax

The basic syntax of the EXISTS and NOT EXISTS operators can be represented as below.

Main_Query WHERE EXISTS (SELECT subquery);
Main_Query WHERE NOT EXISTS (SELECT subquery);

In this syntax,

• Main_Query – the outer query containing the EXISTS/NOT EXISTS condition in the WHERE clause.
• EXISTS – logical operator to evaluate a subquery positively.
• NOT EXISTS – logical operator to evaluate a subquery negatively.
• SELECT subquery – the SELECT query which is being evaluated for its applicability (i.e. whether it does or does not fetch data).

SQL Server EXISTS Examples

Let us understand both varieties of the operator with practical examples. Suppose we have an employees table containing employee details along with their department and manager id’s as below. We will use this sample table as the reference for the examples.

emp_id	first_name	last_name	emp_salary	dept_id	mgr_id
1270	Claudia	Crawford	52000	221	1279
1271	Jeff	Goldsmith	50000	221	1279
1272	Nigel	Knight	60000	222	1281
1273	James	Orwell	66000	222	1288
1274	Cindy	Smith	65000	222	1288
1275	Niki	Bailey	55000	223	1277
1276	Mike	Mattis	69000	223	1277
1277	Angelia	Jackson	59000	223	1275
1278	Martha	DeClarke	55000	223	1290
1279	Albert	Hemingway	55000	221	1288

1) SQL Server  EXISTS example

We can see from the above that some employee ids are also manager ids. This means that some of the employees are managers and manage the other employees. We might want to retrieve the list of such employees who are also managers. This is be achieved using EXISTS as done by the following query.

SELECT emp_id, first_name, last_name
FROM employees emp1
WHERE EXISTS
(SELECT emp_id FROM employees emp2 WHERE emp1.emp_id=emp2.mgr_id);

It will generate the following resultset of management grade employees.

emp_id	first_name	last_name
1275	Niki	Bailey
1277	Angelia	Jackson
1279	Albert	Hemingway

Explanation: Here the outer main query executes first and fetches a record from the table with the employee id in it. Then the inner subquery will execute to check whether outer table (aliased emp1) row employee id matches the manager id of any row of the inner table (aliased emp2). If it does then that employee with the corresponding emp_id is a manager and the EXISTS condition evaluates to TRUE and the row of information (i.e. record) is retained and added to the final resultset. Likewise, the process repeats itself for every row of information (i.e. record) from the table and the retained records form the resultset.

2)  SQL Server  NOT EXISTS example

The following example is the reverse of the above example and produces the reverse result with the help of the NOT EXISTS operator (i.e. it returns the list of employees who are not managers but individual contributors/workers).

SELECT emp_id, first_name, last_name
FROM employees emp1
WHERE NOT EXISTS
(SELECT emp_id FROM employees emp2 WHERE emp1.emp_id=emp2.mgr_id);

It will generate the following resultset of non-management grade employees.

emp_id	first_name	last_name
1270	Claudia	Crawford
1271	Jeff	Goldsmith
1272	Nigel	Knight
1273	James	Orwell
1274	Cindy	Smith
1276	Mike	Mattis
1278	Martha	DeClarke

Explanation: Here too the outer main query executes first and fetches a record from the table with the employee id in it. Then the inner subquery will execute to check whether outer table (aliased emp1) row employee id matches the manager id of any row of the inner table (aliased emp2). If it does not then that employee with the corresponding emp_id is not a manager and the NOT EXISTS condition evaluates to TRUE and the row of information (i.e. record) is retained and added to the final resultset. Likewise, the process repeats itself for every row of information (i.e. record) from the table and the retained records form the resultset.