SQLServer的子查询详解

2023-05-15 11:28

短信预约 -IT技能 免费直播动态提醒

一、子查询基础知识

子查询是嵌套在SELECT、INSERT、UPDATE、DELETE语句中或另一个子查询中的查询。
可以在允许表达式的任何位置使用子查询。

示例：

USE AdventureWorks2016;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
    (SELECT MAX(OrdDet.UnitPrice)
     FROM Sales.SalesOrderDetail AS OrdDet
     WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO

子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

许多包含子查询的 Transact-SQL 语句也可以表述为联接。其他问题只能用子查询提出。在 Transact-SQL 中，包含子查询的语句与不包含子查询的语义等效版本之间通常没有性能差异。但是，在某些必须检查是否存在的情况下，联接会产生更好的性能。否则，必须为外部查询的每个结果处理嵌套查询，以确保消除重复项。在这种情况下，联接方法将产生更好的结果。

以下示例显示了返回相同结果集和执行计划的子查询和联接：

USE AdventureWorks2016;
GO


SELECT [Name]
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE [Name] = 'Chainring Bolts' );
GO


SELECT Prd1.[Name]
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.[Name] = 'Chainring Bolts';
GO

嵌套在外部 SELECT 语句中的子查询具有以下组件：

包含常规选择列表组件的常规查询。
包含一个或多个表或视图名称的常规子句。
可选：WHERE、GROUP BY、HAVING。

子查询的 SELECT 查询始终括在括号中。它不能包含 or 子句，并且只能在还指定 TOP 子句时才包含子句。

子查询可以嵌套在外部 WHERE、HAVING、SELECT、INSERT、UPDATE、DELETE或语句的 or 子句中，也可以嵌套在另一个子查询中。最多可以嵌套 32 个级别，但限制因可用内存和查询中其他表达式的复杂性而异。单个查询可能不支持嵌套多达 32 个级别。如果子查询返回单个值，则子查询可以出现在可以使用表达式的任何位置。

如果表仅出现在子查询中而不出现在外部查询中，则该表中的列不能包含在输出（外部查询的选择列表）中。

包含子查询的语句通常采用以下格式之一：

WHERE expression [NOT] IN (subquery)
WHERE expression comparison_operator [ANY | ALL] (subquery)
WHERE [NOT] EXISTS (subquery)

在某些 Transact-SQL 语句中，可以像计算独立查询一样计算子查询。从概念上讲，子查询结果被替换到外部查询中（尽管这不一定是 SQL Server 实际处理带有子查询的 Transact-SQL 语句的方式）。

有三种基本类型的子查询：

对引入的列表进行操作，或者比较运算符由 INANY或ALL 修改的列表。
使用未修改的比较运算符引入，并且必须返回单个值。
是否使用EXISTS引入存在性测试。

二、子查询规则

使用比较运算符引入的子查询的选择列表只能包含一个表达式或列名。
如果外部查询的子句包含列名，则该子句必须与子查询选择列表中的列连接兼容。
ntext、text 和 image 数据类型不能在子查询的选择列表中使用。
由于它们必须返回单个值，因此由未修改的比较运算符（不后跟关键字或）引入的子查询不能包含 and 子句。
关键字不能与包含的子查询一起使用。
不能指定 and 子句。
ORDER BY只有在也指定TOP时才能指定。
无法使用子查询创建的视图进行更新。

三、限定子查询中的列名

示例：外部查询子句中的 BusinessEntityID 列由外部查询子句（Sales.Store）中的表名隐式限定。子查询的选择列表中对 CustomerID 的引用由子查询子句（即 Sales.Customer 表）限定。

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Sales.Store
WHERE BusinessEntityID NOT IN
    (SELECT CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

一般，语句中的列名由同一级别的子句中引用的表隐式限定。如果子查询子句中引用的表中不存在列，则外部查询子句中引用的表将隐式限定该列。

下面是指定这些隐式假设的查询的外观：

USE AdventureWorks2016;
GO
SELECT [Name]
FROM Sales.Store
WHERE Sales.Store.BusinessEntityID NOT IN
    (SELECT Sales.Customer.CustomerID
     FROM Sales.Customer
     WHERE TerritoryID = 5);
GO

显式声明表名永远不会错，并且始终可以使用显式限定覆盖有关表名的隐式假设。

四、子查询的多层嵌套

子查询本身可以包含一个或多个子查询。任意数量的子查询可以嵌套在一个语句中。

示例：查询查找同时也是销售人员的员工的姓名。

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

输出：

最里面的查询返回销售人员 ID。下一个更高级别查询使用这些销售人员 ID 进行评估，并返回员工的联系人 ID 号。最后，外部查询使用联系人 ID 查找员工的姓名。

还可以将此查询表示为联接：

USE AdventureWorks2016;
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;
GO

五、相关子查询

可以通过执行一次子查询并将结果值替换到外部查询的子句中来计算许多查询。在包含相关子查询（也称为重复子查询）的查询中，子查询依赖于其值的外部查询。这意味着子查询将重复执行，外部查询可能选择的每一行执行一次。

示例：

USE AdventureWorks2016;
GO
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
GO

输出结果：

此语句中的上一个子查询不能独立于外部查询进行计算。它需要 Employee.BusinessEntityID 的值，但此值会随着 SQL Server 检查 Employee 中的不同行而更改。这正是计算此查询的方式：SQL Server 通过将每行中的值替换为内部查询来考虑将 Employee 表的每一行包含在结果中。例如，如果 SQL Server 首先检查的行，则变量 Employee.BusinessEntityID 采用值 285，SQL Server 将其替换到内部查询中。这两个查询示例表示具有相关子查询的前一个示例的分解。

USE AdventureWorks2016;
GO
SELECT Bonus
FROM Sales.SalesPerson
WHERE BusinessEntityID = 285;
GO

结果为 0.00（没有收到奖金，因为他们不是销售人员），因此外部查询的计算结果为：

USE AdventureWorks2016;
GO
SELECT LastName, FirstName
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000 IN (0.00);
GO

由于这是 false，因此的行不包含在具有相关子查询的上一个示例查询的结果中。对的行执行相同的过程。您将看到此行包含在结果中，因为包含结果。

小结：相关子查询还可以通过在外部查询中引用表中的列作为表值函数的参数，在子句中包含表值函数。在这种情况下，对于外部查询的每一行，将根据子查询计算表值函数。