【译】T-SQL进阶：超越基础（二）：编写子查询

原文链接：传送门。

有时候，当你开始创建复杂的SQL语句时候，其超越了基础的T-SQL语句，你会发现你需要用其他SELECT语句的结果集来约束你的查询。当你将一个SELECT语句嵌入到一个父级SELECT语句时，这些嵌入的SELECT语句便被称为子查询，或者称为相关子查询。在本进阶系列的这个章节，我将会讨论子查询的不同方面，在后续的章节中，我会讨论相关子查询。

什么是子查询

一个子查询其实就是包含在其他T-SQL语句中的一个SELECT语句。一个子查询可以被用在一个表达式可以用到的任何地方。许多自查都会返回一个单独的列值，这是因为它们都会用于与比较操作符或者表达式的交互。当一个子查询没有用作表达式或者与比较操作符一起使用时，它可以返回多个值。另外，当子查询用在FROM子句或者与EXISTS关键字一起使用时，它们甚至可以返回多个列的多个行值，即返回一个集合。

在一个T-SQL语句中一个子查询是很容易注意到的，因为其是包含在插入语中的一个SELECT语句。因为子查询是被包含在一个T-SQL语句中，子查询常常被称为内部子查询。同时包含子查询的T-SQL语句被称为外部查询。子查询的另一个特性是其可以独立于外部查询来运行，并不会出错，其将返回列的集合或者空集合。

另一种形式子查询是相关子查询。但是相关子查询不能独立于外部T-SQL语句而运行。一个相关子查询使用外部T-SQL的一列或者多列来约束从相关子查询的返回集。对于这篇文章来说，关于相关子查询知道这些已经足够了。在后续的进阶系列文章中，我会进一步阐述相关子查询的。

当使用子查询时，还需要考虑其他一些东西：

  • 一个子查询不允许返回ntext, text and image数据类型
  • ORDER BY子句不能用在一个子查询中，除非你同时也用了TOP操作符
  • 使用子查询的视图不能进行更新操作
  • COMPUTE 和 INTO子句不能用在一个子查询中

用于子查询示例的示例数据

为了演示如何使用子查询我将需要一些测试数据。我没有创建我自己的测试数据，我所有的示例都会使用AdventureWorks2008R2  数据库。如果你想跟随我并在你的环境中运行我的示例，那么你可以从这儿下载AdventureWorks2008R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587。

返回一个单独值的子查询示例
如上所述的，用在一个表达式或者在比较操作符的一边返回一个值的子查询需要返回一个单独的值。在T-SQL语句中，有许多地方需要子查询返回一个单独的列值，比如在选择列表中，WHERE子句中等等。在本章中我将提供一些列的示例，其展示了使用子查询作为表达式，或者与比较操作符一起使用来满足不同的业务需求。

在列集合中的子查询
在列列表的子查询其实就是一个SELECT语句，其返回了一个单独的列值，其将被放置在一个SELECT语句的列列表之中。为了演示在选择列表中如何使用一个子查询，让我们假设我们将从具有如下业务需求的SELECT语句中返回一个结果集合：

  • 返回所有的 Sales.SalesOrderHeader 记录，其OrderDate 等于“2007-02-19 00:00:00.000”
  • 将返回的记录按照SalesOrderID 排序
  • 将返回的每行数据标上数字，最早的订单具有RowNumber 1，其后的RowNumber  为2，以此类推
  • 结果集需要一个名为TotalOrders 的列，其需要计算出OrderDate 为“2007-02-19 00:00:00.000” 的订单的总数

满足这些需求的SQL代码显示在列表1中：

SELECT ROW_NUMBER() OVER (ORDER BY SalesOrderID) RowNumber
      , (SELECT COUNT(*) 
         FROM [Sales].[SalesOrderHeader] 
         WHERE ModifiedDate = '2007-02-19 00:00:00.000') 
                     AS TotalOrders
      , *
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2007-02-19 00:00:00.000';

列表1：在列列表中的子查询

在这个单独的T-SQL语句中，你看见了两个不同的SELECT子句。子查询是嵌入在列表1的SQL语句中间的SELECT语句，并且其具有圆括号来包围着它。我将把这个子查询拉取出来并将其放置在列表2中，在这种情况下你可以测试它来验证其是否可以独立于完整的T-SQL语句来运行。

SELECT COUNT(*) 
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '2007-02-19 00:00:00.000'

列表2：列表1中的子查询

通过在列列表中具有一个子查询，列表1中的T-SQL语句能够计算出其OrderDate等于“2007-02-19 00:00:00.000”的SalesOrderHeader 行的数量，并随着详细行信息将这个信息返回。

在WHERE子句的子查询示例

在许多时候你想要基于一个SELECT子句的结果集来驱动一个WHERE子句。当你在一个WHERE子句中具有一个SELECT语句时，这个SELECT语句便是一个子查询。为了演示在一个WHERE子句中使用子查询，假设你需要展示所有的包含此商品“long-sleeve logo jerseys”购买的Sales.SalesOrderDetail 记录。列表3的代码通过子查询满足了我如上显示的需求。

SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM [Production].[Product]
                    WHERE Name = 'Long-Sleeve Logo Jersey, XL');

列表3：WHERE子句中的子查询

列表3中的子查询是在WHERE 条件的右边，这个子查询识别出了一个Production.Product 记录的ProductID，其商品名为“Long-Sleeve Logo Jersey, XL”。这个子查询允许我找到所有的Sales.SalesOrderDetail  记录，其有一个与产品名“Long-Sleeve Logo Jersey, XL”相关的ProductID。

使用子查询来控制TOP子句的示例

使用TOP子句返回的行数可以通过一个表达式来进行控制。列表5中的代码识别了Sales.SalesOrderDetail 行的数量，其应该基于TOP子句中的子查询来返回。

SELECT TOP (SELECT TOP 1 OrderQty 
            FROM [Sales].[SalesOrderDetail]
            ORDER BY ModifiedDate) *  
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 716;

列表4：TOP子句中的子查询

列表4中的代码使用从子查询中返回的OrderQty 值来恒等于将被用于TOP子句的值。通过使用子查询来控制TOP子句将要返回的行数，其允许你构建一个子查询，其在运行时动态的等于你的查询将要返回的行数。

HAVING语句中的子查询示例

为了演示在HAVING语句中使用子查询，假设你具有如下业务需求：

产生一个结果集，其包含了每个日期的Sales.SalesOrderHeader.OrderDate 及订单数量，并且订单数量超过了“2006-05-01”的订单数量。

为了满足这个需求，我写了列表5中的查询，其在HAVING子句中使用了子查询。

SELECT count(*), OrderDate 
FROM [Sales].[SalesOrderHeader]
GROUP BY OrderDate
HAVING count(*) >
       (SELECT count(*) 
        FROM [Sales].[SalesOrderHeader]
        WHERE OrderDate = '2006-05-01 00:00:00.000');

列表5：HAVING子句中的子查询

列表5中的代码在HAVING子句的右边具有子查询，并在我的子查询中使用了COUNT函数来决定“2006-05-01”的订单数量。

在一个函数调用中使用子查询的示例

为了演示在一个函数调用中使用子查询，假设你有如下需求：对于每一个Sales.SalesOrderHeader 记录显示OrderDate及最大的OrderDate之间的天数。列表6的代码满足了这个需求。

SELECT SalesOrderID
      , OrderDate
      ,DATEDIFF
          (
            dd,OrderDate
        ,(SELECT MAX(OrderDate)
          FROM [Sales].[SalesOrderHeader])
          ) AS DaysBetweenOrders
         ,(SELECT MAX(OrderDate)
        FROM [Sales].[SalesOrderHeader]) 
            AS MaxOrderDate
FROM [Sales].[SalesOrderHeader];

列表6：函数调用中的子查询

列表6中的代码有两个不同的子查询。两个子查询都返回了Sales.SalesOrderHeader 表的最大的OrderDate。但第一个子查询用于给DATEDIFF函数的第二个参数传递一个日期值。

返回多个值的子查询示例

到现在为止，我所有的示例都包含了一个返回一个单独列单独值的子查询。并不是所有的子查询都具有那样的需求。接下来的示例将会使用返回多个列 和/或 多个值的子查询。

在FROM子句的子查询示例

在FROM子句中，你通常会定义一个表或者表的子集，而你的T-SQL会对其进行操作。每一个表都提供了一组记录，你的查询语句会使用其来为你的查询决定最终的结果集。一个子查询可以被认为是返回了一组记录的查询，因此其可以像一个表一样被用在一个FROM语句中。列表7的代码展示了我如何在一个FROM子句中使用子查询。当一个子查询被用在一个FROM子句中时，从子句中产生的结果集通常称为派生表。

SELECT SalesOrderID 
FROM (SELECT TOP 10 SalesOrderID 
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 716
      ORDER BY ModifiedDate DESC) AS Last10SalesOrders;

列表7：FROM子句中的子查询

  • 列表7的代码使用FROM子句中的子查询来创建一个表别名，名为Last10SalesOrders。我的子查询返回了包含了ProductID为716的最后10条数据。
  • 列表7的代码是一个如何在FROM子句中使用子查询的一个繁琐的示例。通过在FROM子句中使用子查询，你可以简单的构造更复杂的FROM句法，其将子查询的结果与其他表或者另外的子查询进行关联，就如同我在列表8中做的那样。
SELECT DISTINCT OrderDate
FROM (SELECT TOP 10 SalesOrderID 
      FROM [Sales].[SalesOrderDetail]
      WHERE ProductID = 716
      ORDER BY ModifiedDate DESC) AS Last10SalesOrders
JOIN [Sales].[SalesOrderHeader] AS SalesOrderHeader
ON Last10SalesOrders.SalesOrderID = SalesOrderHeader.SalesOrderID
ORDER BY OrderDate

列表8：将一个派生表与一个真实表进行关联

在列表8中，我带着我在列表7中创建的子查询/派生表并将其与 SalesOrderHeader  表进行关联。

与IN关键字搭配使用子查询的示例

另一个你可以使用子查询返回某一列的多个值的地方是当你的子查询产生的结果集将要与IN关键字一起使用的时候。列表9的代码演示了如何使用子查询将值传递给IN关键字。

SELECT * FROM [Sales].[SalesOrderDetail] 
WHERE ProductID IN 
        (SELECT ProductID 
         FROM [Production].[Product]
         WHERE Name like '%XL%');

列表9：使用子查询将值传递给IN关键字

列表9中的代码使用子查询从Product表返回不同的ProductID值，其名称包含了字符串“XL”。这些从子查询返回的ProductID接下来被用在IN 关键子中以约束哪些数据行将从Sales.SalesOrderDetail 表返回。

在一个更新数据的语句中使用子查询的示例

到目前为止，我所有的示例都是在演示在一个SELECT语句的不同部分如何使用一个子查询。一个子查询也可以被用在 INSERT，UPDATE或者DELETE 语句中。列表10中的代码演示了如何在一个INSERT语句中使用一个子查询。

DECLARE @SQTable TABLE (
OrderID int,
OrderDate datetime,
TotalDue money,
MaxOrderDate datetime);
-- INSERT with SubQuery
INSERT INTO @SQTable 
   SELECT SalesOrderID,
          OrderDate, 
          TotalDue, 
          (SELECT MAX(OrderDate) 
           FROM [Sales].[SalesOrderHeader]) 
   FROM [Sales].[SalesOrderHeader]
   WHERE CustomerID = 29614;
-- Display Records
SELECT * FROM @SQtable;

列表10：在一个INSERT语句中的子查询

在我列表10的代码中，我使用子查询来计算将要被插入到列MaxOrderDate 中的值。这只是一个如何在INSERT语句中使用子查询的单独的例子。请记住子查询也可以被用在一个UPDATE/DELETE语句中。

在子查询和JOIN之间的性能考虑

如果你已经读过微软提供的“子查询基础”文档，那你或许已经碰到了这个关于包含子查询的语句的性能的论述：

在T-SQL中，通常在一个包含子查询的语句和其不包含子查询的语义等价版本的语句之间没有性能差异。为了比较使用子查询的查询的性能和一个不包含子查询的等价查询，我将重写列表3的子查询来使用JOIN操作符。列表11展示了我重写后的JOIN查询，其与列表3的查询是等价的。

SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = 'Long-Sleeve Logo Jersey, XL';

列表11：与列表3的查询等价的JOIN查询

为了比较使用了子查询的列表3的查询性能和使用了JOIN的列表11的查询性能，我会使用列表12的代码来运行这两个查询。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Listing 3 query
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = (SELECT ProductID 
                   FROM Production.Product
                    WHERE Name = 'Long-Sleeve Logo Jersey, XL'); 
-- Listing 11 query
SELECT SOD.* 
FROM [Sales].[SalesOrderDetail] AS SOD
INNER JOIN 
[Production].[Product] AS P
ON SOD.ProductID = P.ProductID
WHERE P.Name = 'Long-Sleeve Logo Jersey, XL';

运行列表12的代码之后，我查看由 “SET STATISTICS” 语句产生的信息。通过查看统计结果我发现两个查询都对表SalesOrderDetail进行了3,309 次逻辑读，并对表 Product  产生了2次逻辑读，并且每一个都使用的CPU时间为31ms。另外，我查看SQL SERVER为这两个查询创建的执行计划，我发现SQL SERVER为这两者产生了相同的执行计划。因此，使用子查询或者JOIN查询对于我的情形来说产生了相同的性能，正如同微软文档中所描述的。

总结

一个子查询是嵌入到另一个T-SQL语句中的SELECT语句。这个子查询可以独立于外部查询来独立运行，因而有时候被称为独立子查询。请记住任何时候你在一个表达式的位置使用了一个子查询，或者其与比较操作符一起使用，那么其只能返回一个单独的行和列。通常一个子查询可以用JOIN逻辑进行重写。子查询是一个强大的工具，其可以帮助你创建复杂的T-SQL语句来满足你的业务。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/qianxingmu/p/14078304.html