Working with nested subqueries in SQL Server

by Arthur Fuller | Jan 29, 2007 2:36:00 PM

Tags: Databases, Arthur Fuller, server, subquery, nested subquery, Microsoft SQL Server, SQL Server Newsletter

Takeaway: Arthur Fuller provides an example that demonstrates the power of nested subqueries. He notes that nested subqueries can help you find the answers you need when the pieces are scattered across a number of tables.

Many SQL Server programmers are confused about the use of 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. In this tip, 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.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

13 comment(s)