TSQL查询——嵌套子查询

   SQL有着非常强大且灵活的查询方式，而多表连接操作往往也可以用子查询进行替代，子查询本质上是嵌套进其他select,update,insert,delete语句的一个被限制的select语句，在子查询中，只有下面几个子句可以使用

1、select子句（必须）

2、from子句（必须）

3、where子句（可选）

4、group by(可选)

5、having(可选)

6、order by(只有在top关键字被使用时才可用)

子查询可以嵌套在其他子查询中，这个嵌套最多可以达到32层。子查询也叫内部查询（inner query）或者内部选择（innder  select）,而包含子查询的查询语句也叫外部查询（outter）或者外部选择（Outer select）,子查询的概念可以用简单下图阐述：

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

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

1、返回一张数据表（table）

2、返回一列值（column）

3、返回单个值（Scalar）

子查询作为数据源使用

    当子查询在外部查询的form子句之后使用，子查询被当做一个数据源使用，即使这时子查询只返回一个单一值（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表进行内连接。结果如下：

作为数据源使用也是子查询最简单的应用。当然，当子查询作为数据源使用时，也分为相关子查询和无关子查询。

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

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

  在AdvertureWorks中

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

SELECT [FirstName]
      ,[MiddleName]
      ,[LastName]
  FROM [AdventureWorks].[Person].[Contact]
  WHERE ContactID IN 

  (SELECT EmployeeID
  FROM [AdventureWorks].[HumanResources].[Employee]
  WHERE SickLeaveHours>68)

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

同样的，于in关键字的逻辑取反的Not in关键字，这里不再赘述

但是要强调的是，不要用in和not in关键字，这回引起很多潜在的问题，这篇文章对这个问题有很好的阐述http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in，总之一句话:用exist代替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的那个子查询

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

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

SELECT [FirstName]
      ,[MiddleName]
      ,[LastName]
  FROM [AdventureWorks].[Person].[Contact]
  WHERE ContactID =ANY

  (SELECT EmployeeID
  FROM [AdventureWorks].[HumanResources].[Employee]
  WHERE SickLeaveHours>68)

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

=ANY	和IN等价
<>ANY	和NOT IN等价
>ANY	大于最小的(>MIN)
<ANY	小于最大的(<MAX)
>ALL	大于最大的(>MAX)
<ALL	小于最小的(<MIN)
=ALL	下面说

   =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关键字

  前面所说的查询都是无关子查询，子查询中还有一类很重要的查询是相关子查询，也叫重复子查询，比如，还是上面那个子查询，用相关子查询写：

我想取得总共请病假天数大于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)

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

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

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

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

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

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

SQLRESULT

上面的无关子查询，整个查询过程可以看做是子查询首相返回SQL Result（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

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

同样的，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