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
Table: employees
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.