Multiple Levels of Nesting

A subquery can itself include one or more subqueries. Any number of subqueries can be nested in a statement.

The following query finds the names of employees who are also sales persons.

Use AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact
WHERE ContactID IN
    (SELECT ContactID
     FROM HumanResources.Employee
     WHERE EmployeeID IN
        (SELECT SalesPersonID
         FROM Sales.SalesPerson)

Here is the result set.

LastName                                           FirstName
-------------------------------------------------- -----------------------
Jiang                                              Stephen
Abbas                                              Syed
Alberts                                            Amy
Ansman-Wolfe                                       Pamela
Campbell                                           David
Carson                                             Jillian
Ito                                                Shu
Mitchell                                           Linda
Reiter                                             Tsvi
Saraiva                                            Jos
Vargas                                             Garrett
Varkey Chudukatil                                  Ranjit
Valdez                                             Rachel
Tsoflias                                           Lynn
Pak                                                Jae
Blythe                                             Michael
Mensa-Annan                                        Tete

(17 row(s) affected)

The innermost query returns the sales person IDs. The query at the next higher level is evaluated with these sales person IDs and returns the contact ID numbers of the employees. Finally, the outer query uses the contact IDs to find the names of the employees.

You can also express this query as a join:

USE AdventureWorks;
GO
SELECT LastName, FirstName
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
JOIN Sales.SalesPerson s 
ON e.EmployeeID = s.SalesPersonID

See Also

Concepts

Subquery Fundamentals

Help and Information

Getting SQL Server 2005 Assistance