SQL Server Performance and the IN clause

I enjoy using sub-queries with the IN clause because it fits in my head nicely. I mean that I can translate a sub-query easily in my head with little effort whereas a join requires a little more brain gymnastics. Of course, mileage may vary for you.
Here’s an example taken from the Internet:

USE AdventureWorks ;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM Production.Product p
WHERE ProductNumber LIKE ‘FW%’)));
GO

This appeals to my way of thinking: the employeeIDs from the SalesOrderHeader table and orderID from the SalesOrderDetail and ProductID like a certain product number. The bucket brigade up the list of nested queries makes eminent sense.
Unfortunately, the SQL Server database engine isn’t human and doesn’t ‘think’ like me either. It’s inner workings, like all relational databases, are based on Set Theory from the abstract world of mathematics.
So if efficiency is what you’re after then use joins. Even if I only remove one of the sub-queries above and make it a join I get a performance boost:

USE AdventureWorks ;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail sod, Production.Product p
WHERE sod.ProductID = p.ProductID
and p.ProductNumber LIKE ‘FW%’));
GO

Here the last sub-query is made into a join. Try it for yourself with your own T-SQL code.
As I’ve heard said before. The best optimization is done using brain power rather than computing power.

---

Possibly related posts: (automatically generated)

• Profiler for SQL Server Express
• Enhancements to the OUTPUT clause in SQL Server 2008
• Monitoring SQL Server Performance: Quickie Edition