多重巢狀層級
子查詢本身可包含一或多個子查詢。可以將任意個數的子查詢套疊 (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