子查询是一个嵌套在SELECT、INSERT、UPDATE
或 DELETE
语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。
三种基本的子查询
(1)在通过 IN
或由 ANY
或 ALL
修改的比较运算符引入的列表上操作。
(2)通过未修改的比较运算符引入且必须返回单个值。
(3)通过 EXISTS
引入的存在测试。
- WHERE expression [NOT] IN (subquery)
- WHERE expression comparison_operator [ANY | ALL] (subquery)
- WHERE [NOT] EXISTS (subquery)
对子查询的限制：
(1)通过比较运算符引入的子查询选择列表只能包括一个表达式或列名称（对 SELECT *
执行的 EXISTS
或对列表执行的 IN
子查询除外）。
(2)如果外部查询的 WHERE
子句包括列名称，它必须与子查询选择列表中的列是联接兼容的。
(3)ntext、text
和 image
数据类型不能用在子查询的选择列表中。
（4）由比较运算符（即后面未跟关键字 ANY
或 ALL
的运算符）引入的子查询不能包含 GROUP BY
和 HAVING 子句(因为必须返回单个值)。
（5）包含 GROUPBY
的子查询不能使用 DISTINCT
关键字。
（6）不能指定COMPUTE 和 INTO 子句。
（7）只有指定了 TOP
时才能指定 ORDERBY。
（8）不能更新使用子查询创建的视图。
（9）由 EXISTS
引入的子查询的选择列表有一个星号 (*)，而不是单个列名。因为由 EXISTS
引入的子查询创建了存在测试并返回 TRUE
或 FALSE
而非数据，所以其规则与标准选择列表的规则相同。
（10）子查询可以嵌套但最多不应超过32层嵌套
子查询举例（例子来自msdn）
（1）使用别名（等价于自联接）
子查询和外部查询引用同一表的语句称为自联接（将某个表与自身联接）
例如，可以使用子查询查找与Terri Duffy
具有相同经理的雇员：
USEAdventureWorks;
GO
SELECT EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID IN
(SELECT ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = 12)
可以使用表的别名以区别内外查询使用的表
USEAdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
WHERE e1.ManagerID IN
(SELECT e2.ManagerID
FROM HumanResources.Employee ASe2
WHERE e2.EmployeeID = 12)
等价于下列的自连接语句
USEAdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
INNER JOIN HumanResources.Employee AS e2
ON e1.ManagerID = e2.ManagerID
AND e2.EmployeeID = 12
（2）使用
IN、NOT IN
的子查询
通过 IN（或NOT IN）引入的子查询结果是包含零个值或多个值的列表。子查询返回结果之后，外部查询将利用这些结果。
下面的查询查找Adventure Works Cycles
生成的所有车轮产品的名称。
USEAdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
(SELECTProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name = 'Wheels')
等价的联接查询
UseAdventureWorks;
GO
SELECT p.Name, s.Name
FROM Production.Product p
INNER JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
AND s.Name = 'Wheels'
此类in的子查询，联接和子查询稍有不同。使用联接的时候可以再结果中显示多个表的列而子查询则只能显示外查询表中的列。
(3)用 ANY、SOME
或 ALL
修改的比较运算符
SOME
是与 ANY
等效的 SQL-92
标准。 =ANY
运算符与 IN
等效 <>ANY
运算符则不同于 NOT IN < >ALL
与 NOT IN
等效
以>
比较运算符为例，>ALL
表示大于每一个值(它表示大于最大值)。>ANY
表示至少大于一个值(即大于最小值)。
下例查找定价高于或等于任何产品子类别的最高定价的产品
USEAdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
(4)使用 EXISTS
的子查询
用EXISTS
引入子查询后，子查询的作用就相当于进行存在测试。外部查询的 WHERE
子句测试子查询返回的行是否存在。子查询实际上不产生任何数据，它只返回 TRUE
或 FALSE
值。
使用 EXISTS
引入的子查询的语法如下：
WHERE [NOT] EXISTS (subquery)
以下查询将查找Wheels
类别中所有产品的名称：
USEAdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE EXISTS
(SELECT *
FROMProduction.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND Name = 'Wheels')
(5)用于替代表达式的子查询
在Transact-SQL
中，除了在 ORDER BY
列表中以外，在 SELECT、UPDATE、INSERT
和 DELETE
语句中任何能够使用表达式的地方都可以用子查询替代。
以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。
USEAdventureWorks;
GO
SELECT Name, ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
ListPrice - (SELECT AVG(ListPrice)FROM Production.Product)
AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1
(6)相关子查询
相关子查询（也称为重复子查询）的查询中，子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。SQL Server
通过将每一行的值代入内部查询
此查询在SalesPerson
表中检索奖金为 5000
且雇员标识号与 Employee
和 SalesPerson
表中的标识号相匹配的雇员的名和姓的一个实例。
USEAdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID
WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson sp
WHERE e.EmployeeID = sp.SalesPersonID) ;
GO
该语句中前面的子查询无法独立于外部查询进行计算。它需要 Employee.EmployeeID
值，但是此值随 Microsoft SQL Server2005
检查 Employee
中的不同行而改变。如何计算此查询：SQL Server
通过将每一行的值代入内部查询，考虑 Employee
表中的每一行是否都包括在结果中。例如，如果 SQL Server
首先检查 Syed Abbas
行，那么变量 Employee.EmployeeID
将取值 288，SQLServer
将该值代入内部查询。
相关子查询还可以用于外部查询的HAVING
子句中
以下示例将查找最高标价超过其平均价格两倍的产品型号。
USEAdventureWorks;
GO
SELECT p1.ProductModelID
FROM Production.Product p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
(SELECT 2 * AVG(p2.ListPrice)
FROM Production.Product p2
WHERE p1.ProductModelID = p2.ProductModelID) ;
GO