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)
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.
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.
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.
ExistsPredicate.zip
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)
Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.
The EXISTS PredicateNow 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.
Code Block
ExistsPredicate.sqlce
Create the EXISTS query.SELECT EmpId, Ssn
FROM Employees AS E
WHERE LastName = N'Rivera'
AND EXISTS
(SELECT * FROM Orders AS O
WHERE O.EmpId = E.EmpId);
Need help with cloud hosting? Try Server Intellect. We used them for our cloud hosting services and we are very happy with the results!
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.
Code Block
NotExistsPredicate.sqlce
Create a query with a NOT EXISTS predicate.
SELECT EmpId, Ssn
FROM Employees AS E
WHERE LastName = N'Rivera'
AND NOT EXISTS
(SELECT * FROM Orders AS O
WHERE O.EmpId = E.EmpId);
I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.
OutputAs 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.
ExistsPredicate.zip
