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!