Nested subqueries

from the Artful SQL Server & Access Tips List

Many SQL Server developers get confused about how to use subqueries, especially nested subqueries (i.e., a subquery that contains a subquery). Let's start with the basics.

There are two kinds of subqueries: standard and correlated. The standard subquery executes once, and its results are fed into the parent query. A correlated subquery executes once for every row retrieved by the parent query. Here I shed some light on the power of nested subqueries (I'll save correlated subqueries for another day).

Consider this problem: You want to generate a list of the salespeople who have sold flat washers. The data you need is scattered among four tables: Person.Contact, HumanResources.Employee, Sales.SalesOrderHeader, and Sales.SalesOrderDetail. In SQL Server, you write it outside-in, but it's often helpful to think about it inside-out first, i.e., working out as far as required one statement at a time.

Working from the inside out, you examine the Sales.SalesOrderDetail table, matching the ProductNumber value with a LIKE statement. You connect these rows to the Sales.SalesOrderHeader table from which you obtain the SalesPersonIDs. Then you connect to the HumanResources.Employee table using the SalesPersonID. Finally, you use the ContactID to connect to the Person.Contact table.

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 example illustrates several cool things about SQL Server. You see that it is possible to substitute a SELECT statement for the IN() parameter. In this example, you do it twice, thus creating a nested subquery.

I am a big fan of normalization, although I don't take it to absurd lengths. Normalization carries with it the increased complexity of various queries. Subqueries can prove very useful in these circumstances, and nested subqueries even more so.

When the answers you need are scattered across lots of tables and you must somehow bring the pieces together again, you may find that a nested subquery is the way to go.
Return to the Artful SQL Server & Access tips page