子查询总结

最新推荐文章于 2022-12-05 14:02:12 发布

dyufei

最新推荐文章于 2022-12-05 14:02:12 发布

阅读量380

点赞数

本文链接：https://blog.csdn.net/dyufei/article/details/4797285

版权

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

子查询类型

（1）使用别名。
（2）使用 IN 或 NOT IN。
（3）在 UPDATE、DELETE 和 INSERT 语句中。
（4）使用比较运算符。
（5）使用 ANY、SOME 或 ALL。
（6）使用 EXISTS 或 NOT EXISTS。
（7）代替表达式。
(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）包含 GROUP BY 的子查询不能使用 DISTINCT 关键字。
（6）不能指定 COMPUTE 和 INTO 子句。
（7）只有指定了 TOP 时才能指定 ORDER BY。
（8）不能更新使用子查询创建的视图。
（9）由 EXISTS 引入的子查询的选择列表有一个星号 (*)，而不是单个列名。因为由 EXISTS 引入的子查询创建了存在测试并返回 TRUE 或 FALSE 而非数据，所以其规则与标准选择列表的规则相同。
（10）子查询可以嵌套但最多不应超过32层嵌套

子查询举例（例子来自msdn）

（1）使用别名（等价于自联接）

子查询和外部查询引用同一表的语句称为自联接（将某个表与自身联接）

例如，可以使用子查询查找与 Terri Duffy 具有相同经理的雇员：

USE AdventureWorks;
GO
SELECT EmployeeID, ManagerID
FROM HumanResources.Employee
WHERE ManagerID IN
    (SELECT ManagerID
     FROM HumanResources.Employee
     WHERE EmployeeID = 12)

 可以使用表的别名以区别内外查询使用的表

USE AdventureWorks;
GO
SELECT e1.EmployeeID, e1.ManagerID
FROM HumanResources.Employee AS e1
WHERE e1.ManagerID IN
    (SELECT e2.ManagerID
     FROM HumanResources.Employee AS e2
     WHERE e2.EmployeeID = 12)

等价于下列的自连接语句

USE AdventureWorks;
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 生成的所有车轮产品的名称。

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Wheels')

等价的联接查询

Use AdventureWorks;
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 表示至少大于一个值(即大于最小值)。

下例查找定价高于或等于任何产品子类别的最高定价的产品

USE AdventureWorks;
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 类别中所有产品的名称：

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE EXISTS
    (SELECT * 
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID = 
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels')

(5)用于替代表达式的子查询

在 Transact-SQL 中，除了在 ORDER BY 列表中以外，在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何能够使用表达式的地方都可以用子查询替代。

以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。

USE AdventureWorks;
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 表中的标识号相匹配的雇员的名和姓的一个实例。

USE AdventureWorks;
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 Server 2005 检查 Employee 中的不同行而改变。如何计算此查询：SQL Server 通过将每一行的值代入内部查询，考虑 Employee 表中的每一行是否都包括在结果中。例如，如果 SQL Server 首先检查 Syed Abbas 行，那么变量 Employee.EmployeeID 将取值 288，SQL Server 将该值代入内部查询。

相关子查询还可以用于外部查询的 HAVING 子句中

以下示例将查找最高标价超过其平均价格两倍的产品型号。

USE AdventureWorks;
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