多重巢狀層級

子查詢本身可包含一或多個子查詢。可以將任意個數的子查詢套疊 (Nested) 於陳述式中。

下列查詢會找出兼具銷售人員身分的員工名稱。

Use AdventureWorks2008R2;
GO
SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM HumanResources.Employee
     WHERE BusinessEntityID IN
        (SELECT BusinessEntityID
         FROM Sales.SalesPerson)
    )

以下為結果集：

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)

最內層的查詢將傳回銷售人員識別碼。緊接的上一層查詢將以這些銷售人員識別碼來運算，並傳回這些員工的連絡識別碼。最後，外層的查詢將使用連絡識別碼來找出員工名稱。

您也可以將此查詢表現成聯結 (Join)：

USE AdventureWorks2008R2;
GO
SELECT LastName, FirstName
FROM Person.Person c
INNER JOIN HumanResources.Employee e
ON c.BusinessEntityID = e.BusinessEntityID
JOIN Sales.SalesPerson s 
ON e.BusinessEntityID = s.BusinessEntityID