Equals Sub Queries Versus Exists Sub Queries

I’d like to introduce you to the idea that you can replace an equal sub query with an exists sub query. Doing this can change a seek operation to a scan operation. This usually results in a faster query, with fewer resources used. Check out this example using the adventureworks database.

USE AdventureWorks
GO
-- use =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use EXISTS
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO

If you check these two queries out with the estimated execution plan you should find the following result.

In this case the use of EXISTS is a faster performer than the equals sub query. Usually I would have suggested you go with an INNER JOIN instead, but if you check out the execution plan and cost of using an INNER JOIN, you’d find no improvement.

Now, I want you to make sure you’re not rushing off trying to replace every equals sub query with EXISTS. Check out the following example.

-- use =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO

In this case the execution plan tells a completely different story.

The moral of this story is you can replace = or IN sub queries with EXISTS sub queries. If you’re not happy with a queries performance, this should become one additional tool in your tool belt. You need to know you can use it, just make sure it’s the right tool for the job before you commit!

If you have any questions, send them in. I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

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