[转]SQL Server 高级查询 – 学习笔记
SQL Server 高级查询 – 学习笔记
本文是《SQL Server 2005 高级程序设计》, Professional SQL Server 2005 Programming – 第六章：高级查询的学习笔记。主要包含如下内容：
1. 嵌套的子查询；
2. 相关子查询；
3. 派生表；
4. EXISTS 运算符的使用；
大部分的子查询（当然不是全部）也能够用联接（Join）来实现，关于联接和子查询的性能比较，需要具体分析。本文所有的范例SQL 脚本使用AdventureWorks 数据库。
1. 构建嵌套的子查询
嵌套的子查询（nested subquery） - 要么返回一个用于外部查询的值，要么返回一个与IN运算符一起使用的值的列表。当想要明确使用 = 运算符时，则使用返回一个值的查询，也就是从一行返回一列。如果要返回一个列表，则必须在外部查询中使用 IN 运算符。
（1）创建使用单值的嵌套子查询
查询系统中购买产品的第一天所销售的所有产品项的ProductID，SQL 脚本如下：
SELECT DISTINCT soh.OrderDate, sod.ProductID
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate =(SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader)
内部查询（SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader）获取一个值，该值将在外部查询中使用。由于这里使用了 = ，因此内部查询只能返回一个值，也就是一行中返回一列，否则将在运行时产生错误。
（2）创建返回多个值列表的嵌套子查询
在实际的应用中，最常见的是这种子查询，返回某种类型的范围列表，并在查询中作为一种判断标准来使用。
下面查询条件：查询应征了公司另一个职位的所有雇员的列表。
SELECT e.EmployeeID, FirstName, LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID IN
(SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate)
上述代码返回2条记录：
上述的查询脚本也可以使用内部联接而非嵌套的子查询来实现，脚本如下所示：
SELECT e.EmployeeID, FirstName, LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
JOIN HumanResources.JobCandidate jc
on jc.EmployeeID = e.EmployeeID
2. 构建相关子查询
相关子查询与前面的嵌套子查询的不同之处在于，在相关子查询中，信息是双向而非单向的。在嵌套子查询中，内部查询只处理一次，信息被传出以用于外部查询，外部查询也只执行一次。
在相关子查询中，按以下3个步骤来进行：
（1）外部查询得到一条记录并将其传入到内部查询。
（2）内部查询基于传入的值执行。
（3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成其处理。
WHERE 子句中的相关子查询
查询每一位顾客在系统中的第一张订单中的OrderID 和 OrderDate，也就是要知道顾客在第一天下订单的日期和订单的ID。
SELECT soh.SalesOrderID, soh.OrderDate, soh.CustomerID
FROM Sales.SalesOrderHeader soh
WHERE soh.OrderDate=(SELECT MIN(soh2.OrderDate)
FROM Sales.SalesOrderHeader soh2
WHERE soh2.CustomerID = soh.CustomerID)
ORDER BY CustomerID
SELECT 列表中的相关子查询
查询顾客的名字以及首次订购的日期。因为要获得顾客的名字，这意味着必须用到Person.Contact表，另外还需要知道第一次订购的日期。
SELECT c.LastName,
(SELECT MIN(OrderDate)
FROM Sales.SalesOrderHeader o
WHERE o.ContactID = c.ContactID)
AS OrderDate
FROM Person.Contact c
上述查询结果中，OrderDate列会有NULL值。这是由于Sales.SalesOrderHeader 表中没有与Person.Contact 表（外部查询）中的记录相匹配的记录。
上述查询也可以通过Join联接来实现：
SELECT c.LastName, o.OrderDate
FROM Person.Contact c
LEFT JOIN(SELECT ContactID, MIN(OrderDate) AS OrderDate
FROM Sales.SalesOrderHeader
GROUP BY ContactID ) AS o
ON c.ContactID = o.ContactID
3. 派生表
派生表（derived table），有时也称为内联视图，是由查询结果集的列和行组成，与常规的表一样。
假设要获取订购过某种产品的顾客列表，如Minipump：
SELECT c.FirstName, c.LastName, p.Name
FROM Person.Contact AS c
JOIN Sales.SalesOrderHeader AS soh
ON c.ContactID = soh.ContactID
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE p.Name = 'Minipump'
上述查询很容易，但是如果想要获得同时订购过Minipump产品，又订购过 AWC Logo Cap 产品的所有顾客，就有点麻烦了。
需要把Minipump的订购者和AWC Logo Cap产品的订购者的查询结果联接起来，可以通过派生表来实现。要创建派生表，需要注意如下事项：
1. 用圆括号包围产生结果集的查询结果；
2. 给查询的结果赋予别名；
下面通过派生表，实现 - 获得同时订购过Minipump产品，又订购过 AWC Logo Cap 产品的所有顾客：
SELECT c.FirstName, c.LastName
FROM Person.Contact AS c
JOIN(SELECT DISTINCT ContactID
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE p.Name = 'Minipump'
) pumps
ON c.ContactID = pumps.ContactID
JOIN(SELECT DISTINCT ContactID
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE p.Name = 'AWC Logo Cap'
) caps
ON c.ContactID = caps.ContactID
但需要注意的是，派生表并不能解决所有的问题。当结果集相当庞大且需要联接大量的记录时，可能要考虑使用临时表，并在其上创建索引（派生表没有索引）。
4. EXISTS 运算符
当使用EXISTS 时，并不真正返回数据 – 返回的是关于数据是否存在的TRUE/FALSE，数据满足在EXISTS 语句运行所基于的查询中建立的条件。
--- 查询应征了该公司另一个职位的雇员列表：
SELECT e.EmployeeID, FirstName, LastName
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE EXISTS
(SELECT EmployeeID
FROM HumanResources.JobCandidate jc
WHERE jc.EmployeeID = e.EmployeeID)
使用EXISTS 关键字时，SQL Server 不必在所有的行上进行连接。实际上，SQL Server将浏览记录，直到找到第一个满足条件的记录然后停止于那里。只要有一条记录满足条件，EXISTS 将为真，因此不再需要继续浏览。在进行内部连接时，性能差异将更为显著。
