多层嵌套
子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。
以下查询将查找作为销售人员的雇员的姓名。
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)
最里层查询将返回销售人员的 ID。再上一层查询将用这些销售人员 ID 进行取值,并返回雇员的联系 ID 号。最后,外部查询将使用这些联系 ID 查找雇员的姓名。
也可以将该查询表示为一个联接:
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