T-SQL查询进阶--深入理解子查询

刺骨的言语ヽ痛彻心扉 2022-06-12 11:27 158阅读 0赞

转自 [http://www.cnblogs.com/CareySon/archive/2011/07/18/2109406.html][http_www.cnblogs.com_CareySon_archive_2011_07_18_2109406.html]

### 引言 ###

SQL有着非常强大且灵活的查询方式，而多表连接操作往往也可以用子查询进行替代,本篇文章将会讲述子查询的方方面面。

### 简介 ###

子查询本质上是嵌套进其他SELECT,UPDATE,INSERT,DELETE语句的一个被限制的SELECT语句,在子查询中，只有下面几个子句可以使用

1.  SELECT子句（必须）
2.  FROM子句(必选）
3.  WHERE子句(可选)
4.  GROUP BY(可选)
5.  HAVING(可选)
6.  ORDER BY(只有在TOP关键字被使用时才可用)

子查询也可以嵌套在其他子查询中,这个嵌套最多可达32层。子查询也叫内部查询(Inner query)或者内部选择(Inner Select),而包含子查询的查询语句也叫做外部查询（Outter)或者外部选择(Outer Select),子查询的概念可以简单用下图阐述:

[![1][]][1 1]

上图是作为数据源使用的一个子查询.

通常来讲，子查询按照子查询所返回数据的类型，可以分为三种，分别为：

1.  返回一张数据表(Table)
2.  返回一列值(Column)
3.  返回单个值(Scalar)

下面，我们按照这三种方式来阐述子查询

### 子查询作为数据源使用 ###

当子查询在外部查询的FROM子句之后使用时,子查询被当作一个数据源使用,即使这时子查询只返回一个单一值(Scalar)或是一列值(Column)，在这里依然可以看作一个特殊的数据源,即一个二维数据表(Table).作为数据源使用的子查询很像一个View(视图),只是这个子查询只是临时存在，并不包含在数据库中。

比如这个语句:

SELECT     P.ProductID, P.Name, P.ProductNumber, M.Name AS ProductModelName
    FROM         Production.Product AS P INNER JOIN
    (SELECT     Name, ProductModelID
     FROM          Production.ProductModel) AS M 
    ON P.ProductModelID = M.ProductModelID

上述子查询语句将ProductModel表中的子集M,作为数据源（表）和Product表进行内连接。结果如下:

[![2][]][2 1]

作为数据源使用也是子查询最简单的应用。当然，当子查询作为数据源使用时，也分为相关子查询和无关子查询，这会在文章后面介绍到.

### 子查询作为选择条件使用 ###

作为选择条件的子查询也是子查询相对最复杂的应用.

作为选择条件的子查询是那些只返回一列(Column)的子查询，如果作为选择条件使用，即使只返回单个值，也可以看作是只有一行的一列.比如:

在AdventureWorks中：

我想取得总共请病假天数大于68小时的员工:

SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact]
      WHERE ContactID IN 
    
      (SELECT EmployeeID
      FROM [AdventureWorks].[HumanResources].[Employee]
      WHERE SickLeaveHours>68)

结果如下：

![3][]

上面的查询中，在IN关键字后面的子查询返回一列值作为外部查询的选择条件使用.

同样的，与IN关键字的逻辑取反的NOT IN关键字，这里就不再阐述了

但是要强调的是，不要用IN和NOT IN关键字，这会引起很多潜在的问题，这篇文章对这个问题有着很好的阐述:[http://wiki.lessthandot.com/index.php/Subquery\_typo\_with\_using\_in][http_wiki.lessthandot.com_index.php_Subquery_typo_with_using_in]。这篇文章的观点是永远不要再用IN和NOT IN关键字，我的观点是存在即合理，我认为只有在IN里面是固定值的时候才可以用IN和NOT IN，比如:

SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact]
      WHERE ContactID  IN (25,33)

只有在上面这种情况下，使用IN和NOT IN关键字才是安全的，其他情况下，最好使用EXISTS,NOT EXISTS,JOIN关键字来进行替代. 除了IN之外，用于选择条件的关键字还有ANY和ALL,这两个关键字和其字面意思一样. 和"<",">",”="连接使用，比如上面用IN的那个子查询：

我想取得总共请病假天数大于68小时的员工

用ANY关键字进行等效的查询为:

SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact]
      WHERE ContactID =ANY
    
      (SELECT EmployeeID
      FROM [AdventureWorks].[HumanResources].[Employee]
      WHERE SickLeaveHours>68)

> ####  ####

在作为ANY和ALL关键字在子查询中使用时，所实现的效果如下

<table style="margin-top:0px; margin-right:auto; margin-bottom:0px; margin-left:auto; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse; color:rgb(51,51,51); font-family:verdana,Arial,Helvetica,sans-serif; font-size:14px; line-height:25px"> 
 <tbody style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> =ANY</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 和IN等价</td> 
  </tr> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> &lt;&gt;ALL</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 和NOT IN等价</td> 
  </tr> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> &gt;ANY</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 大于最小的(&gt;MIN)</td> 
  </tr> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> &lt;ANY</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 小于最大的(&lt;MAX)</td> 
  </tr> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> &gt;ALL</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 大于最大的(&gt;MAX)</td> 
  </tr> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> &lt;ALL</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 小于最小的(&lt;MIN)</td> 
  </tr> 
  <tr style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px"> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> =ALL</td> 
   <td style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:3px; padding-right:3px; padding-bottom:3px; padding-left:3px; border-top-width:1px; border-right-width:1px; border-bottom-width:1px; border-left-width:1px; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-top-color:rgb(192,192,192); border-right-color:rgb(192,192,192); border-bottom-color:rgb(192,192,192); border-left-color:rgb(192,192,192); border-collapse:collapse"> 下面说</td> 
  </tr> 
 </tbody> 
</table>

=ALL关键字很少使用，这个的效果在子查询中为如果只有一个返回值，则和“=”相等，而如果有多个返回值，结果为空

这里要注意，SQL是一种很灵活的语言，就像子查询所实现的效果可以使用JOIN来实现一样（效果一样，实现思路不同），ANY和ALL所实现的效果也完全可以使用其他方式来替代,按照上面表格所示，>ANY和>MIN完全等价，比如下面两个查询语句完全等价:

SELECT *
    FROM AdventureWorks.HumanResources.Employee
    WHERE SickLeaveHours>ANY
    
    (SELECT SickLeaveHours FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>68)
    
    
    SELECT *
    FROM AdventureWorks.HumanResources.Employee
    WHERE SickLeaveHours>
    
    (SELECT MIN(SickLeaveHours) FROM AdventureWorks.HumanResources.Employee WHERE SickLeaveHours>68)

#### 相关子查询和EXISTS关键字 ####

前面所说的查询都是无关子查询(Uncorrelated subquery),子查询中还有一类很重要的查询是相关子查询(Correlated subquery),也叫重复子查询比如，还是上面那个查询，用相关子查询来写:

我想取得总共请病假天数大于68天的员工:

结果和使用IN关键字的查询结果相同:

![3][3 1]

如何区别相关子查询和无关子查询呢?最简单的办法就是直接看子查询本身能否执行，比如上面的例子中的子查询：

(SELECT *
      FROM [AdventureWorks].[HumanResources].[Employee] e
      WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)

这一句本身执行本身会报错.因为这句引用到了外部查询的表

对于无关子查询来说，整个查询的过程为子查询只执行一次，然后交给外部查询，比如：

SELECT *
    FROM AdventureWorks.HumanResources.Employee
    WHERE SickLeaveHours>ANY
    
    SQLRESULT

上面的无关子查询，整个查询过程可以看作是子查询首先返回SQLResult(SQL结果集),然后交给外部查询使用，整个过程子查询只执行一次

而相反,作为相关子查询,子查询的执行的次数依赖于外部查询，外部查询每执行一行，子查询执行一次，比如：

还是上面的例子:我想取得总共请病假天数大于68天的员工

SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact] c
      WHERE EXISTS
    
      (SELECT *
      FROM [AdventureWorks].[HumanResources].[Employee] e
      WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)
    
    ----
    step 1:
    SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact] c
      WHERE EXISTS
    
      (SELECT *
      FROM [AdventureWorks].[HumanResources].[Employee] e
      WHERE 1=e.ContactID AND e.SickLeaveHours>68)
    ----
    step 2:
    SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact] c
      WHERE EXISTS
    
      (SELECT *
      FROM [AdventureWorks].[HumanResources].[Employee] e
      WHERE 2=e.ContactID AND e.SickLeaveHours>68)
    ----
    step n:
    SELECT [FirstName]
          ,[MiddleName]
          ,[LastName]
      FROM [AdventureWorks].[Person].[Contact] c
      WHERE EXISTS
    
      (SELECT *
      FROM [AdventureWorks].[HumanResources].[Employee] e
      WHERE n=e.ContactID AND e.SickLeaveHours>68)

如上面代码所示。上面的相关子查询实际上会执行N次（N取决与外部查询的行数），外部查询每执行一行，都会将对应行所用的参数传到子查询中，如果子查询有对应值，则返回TRUE（既当前行被选中并在结果中显示），如果没有，则返回FALSE。然后重复执行下一行。

### 子查询作为计算列使用 ###

当子查询作为计算列使用时，只返回单个值(Scalar) 。用在SELECT语句之后，作为计算列使用。同样分为相关子查询和无关子查询

相关子查询的例子比如：我想取得每件产品的名称和总共的销量

SELECT [Name],
          (SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderDetail S
          WHERE S.ProductID=P.ProductID) AS SalesAmount
    FROM [AdventureWorks].[Production].[Product] P

部分结果如下：

[![5][]][5 1]

当子查询作为计算列使用时，会针对外部查询的每一行，返回唯一的值。

同样的，SQL子查询都可以使用其他语句达到同样的效果，上面的语句和如下语句达到同样的效果：

SELECT P.Name,COUNT(S.ProductID)
    FROM [AdventureWorks].[Production].[Product] P 
    LEFT JOIN  AdventureWorks.Sales.SalesOrderDetail S
    ON S.ProductID=P.ProductID
    GROUP BY P.Name

子查询作为计算列且作为无关子查询时使用，只会一次性返回但一值，这里就不再阐述了。

### 小结 ###

本篇文章通过子查询的三种不同用途来阐述子查询。同时，所有的子查询还可以分为相关子查询和无关子查询，而子查询所实现的功能都可以使用连接或者其他方式实现。但一个好的作家应该是掌握丰富的词汇，而不是仅仅能表达出自己的意思。学会多种SQL查询方式是学习SQL查询必经之路。

[http_www.cnblogs.com_CareySon_archive_2011_07_18_2109406.html]: http://www.cnblogs.com/CareySon/archive/2011/07/18/2109406.html
[1]: /images/20220612/4ab21661995c471aaf01810907ef6352.png
[1 1]: http://images.cnblogs.com/cnblogs_com/CareySon/201107/201107181306027785.png
[2]: /images/20220612/c27aaef02c774c84b8a64d9622e7483e.png
[2 1]: http://images.cnblogs.com/cnblogs_com/CareySon/201107/201107181306047587.png
[3]: /images/20220612/49b768bae83b423fa7a9732b1a06f2ef.png
[http_wiki.lessthandot.com_index.php_Subquery_typo_with_using_in]: http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in
[3 1]: /images/20220612/b3adac73191240baaef27c6b3a74618f.png
[5]: /images/20220612/8b8f82cd20e24662a1279fbda7babedd.png
[5 1]: http://images.cnblogs.com/cnblogs_com/CareySon/201107/201107181306153051.png