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_idfirst_namelast_nameemp_salarydept_idmgr_id
1270ClaudiaCrawford520002211279
1271JeffGoldsmith500002211279
1272NigelKnight600002221281
1273JamesOrwell660002221288
1274CindySmith650002221288
1275NikiBailey550002231277
1276MikeMattis690002231277
1277AngeliaJackson590002231275
1278MarthaDeClarke550002231290
1279AlbertHemingway550002211288
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_idfirst_namelast_name
1275NikiBailey
1277AngeliaJackson
1279AlbertHemingway

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_idfirst_namelast_name
1270ClaudiaCrawford
1271JeffGoldsmith
1272NigelKnight
1273JamesOrwell
1274CindySmith
1276MikeMattis
1278MarthaDeClarke

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.

Advertisement