How to Check for Existing Rows in a Subquery in SQL Server

This tutorial will show you how to use the EXISTS predicate and check if a row does or doesn’t exist in a subquery. The EXISTS predicate accepts a subquery as input, calculates the code and returns TRUE if the subquery returns any rows and FALSE otherwise. It is generally used with correlated subqueries, which refer to attributes from the table that appears in the outer query.

Setting Up

We will use two tables called Employees and Orders. Both tables have a column called EmpId that acts as a way to connect both tables for relationship purposes. Create both tables and insert the data shown below into your tables:

Employees

– EmpId (int, not null), FirstName (nvarchar(30), not null), LastName (nvarchar(30), not null), HireDate (datetime, not null), MgrId (int, null), Ssn (nvarchar(20), not null), Salary (money, not null)

Orders

– OrderId (PK, int, not null), EmpId (int, not null), CustId (nvarchar(30), not null), OrderTs (datetime, not null), Qty (int, not null)

The EXISTS Predicate

Now that we have both tables created and filled with data, we can create a query that checks for the existence of an employee Id with a last name of ‘Rivera’ in the Employees and Orders tables.

 

 

The outer query filters employees with a last name of “Rivera” for whom the EXISTS predicate returns TRUE. The EXISTS predicate returns TRUE if the current customer has related orders in the Orders table.

Queries can be read just like a sentence, making for easier understanding of the query as such: select the employee ID and social security number attributes from the Employees table, where the last name is equal to Rivera, and at least one employee exists in the Orders table with the same employee ID as the employee’s employee ID. This query returns the following output:

The NOT EXISTS Predicate
The EXISTS predicate can be negated with the NOT logical operator. We will use the previous query and add a NOT EXISTS predicate to it and the results will be different.

 

 

 

Output

As you can see the table outputs nothing because no other employees with a last name of ‘Rivera’ do not exist in the table.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

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