第一章 逻辑查询处理

　　SQL编程有许多独到之处，如：面向集合的思维方式、查询元素的逻辑处理顺序、三值逻辑(three-valued logic,逻辑表达式的可能值包括TRUE、FALSE、UNKNOWN。他们被称为三值逻辑。)如果不掌握这些知识就开始用SQL编程，得到的将是冗余的、性能低下的代码，而且难以维护。

　　在SQL Server中负责生成实际工作计划(执行计划)的组件是查询优化器(query optimizer)。以何种顺序访问表、使用什么访问方法和索引、应用哪种联接算法等都是由查询优化器来决定的。优化器会生成多个有效的执行计划，并从中选择一个开销最低的计划。

1.1 逻辑查询处理的各个阶段

　　逻辑查询处理的步骤序号，详细描述了逻辑查询处理的各个阶段。

(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
　　　　　| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
　　　　　| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
　　　　　| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>

 　　SQL与其他编程语言不同的最明显特征是代码的处理顺序。在SQL中，第一个处理的子句是FROM子句，尽管SELECT语句第一个出现，但几乎总是放在最后处理。

　　每一步都会生成一个虚拟表，该虚拟表会作为下一步的输入。这些虚拟表对调用者是不可用的，只有最后一步生成的虚拟表才会返回给调用者。

1.1.1 逻辑查询处理阶段简介

• (1)FORM FROM阶段标识出查询的来源表，处理表运算符。每个表运算符也会应用一系列子阶段。
• (1-J1)笛卡尔积 这个阶段对表运算符设计的两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1-J1
• (1-J2)ON筛选器 这个阶段对VT1-J1中的行根据ON子句(<on_predicate>)中出现的谓词进行筛选。只有让该谓词取值为TRUE的行，才能插入VT1-J2中。
• (1-J3)添加外部行 如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，则将保留表(perserved table)中没有找到匹配的行，作为外部行添加到VT1-J2中，生成VT1-J3。
• (2)WHERE 这个阶段根据在WHERE子句中出现的谓词(<where_predicate>)对VT1中的行进行筛选。只有让谓词计算结果为TRUE的行，才会插入VT2中。
• (3)GROUP BY 按照GROUP BY子句中指定的列名列表，将VT2中的行进行分组，生成VR3。最终，每个分组只有一个结果行。
• (4)HAVING 根据HAVING子句中出现的谓词(<having_predicate>)对VT3中的分组进行筛选。只有让谓词计算结果为TRUE的组，才会插入到VT4。
• (5)SELECT 处理SELECT子句中的元素，产生VT5。
• (5-1)计算表达式 计算SELECT列表中的表达式，生成VT5-1。
• (5-2)DISTINCT 删除VT5中的重复行，生成VT5-2。
• (5-3)TOP 根据ORDER BY子句定义的逻辑排序，从VT5-2中选择前面指定数量或百分比的行，生成表VT5-3。
• (6)ORDER BY 根据ORDER BY子句中指定的列名列表，对VT5-3中的行进行排序，生成游标VC6。

1.3 逻辑查询处理阶段详解

1.3.1 步骤1：FROM阶段

　　FROM阶段负责标识表或要查询的表，如果指定了表运算符，这个阶段还要按从左到右的顺序对这些运算符进行处理。每个表运算符都是对一个或两个输入表进行运算，返回一个输出表。

　　步骤1-J1:笛卡尔积(交叉联接)

　　这个子阶段是可应用于联接表运算符的三个子阶段中的第一个阶段，对联接设计的两个表执行笛卡尔积运算(交叉联接或未限定的联接)，生成虚拟表VT1-J1。这个虚拟表为左表行和右表行的每一个可能的组合包含一行数据。如果左表包含n行，右表包含m行，则VT1-J1将包含 n * m 行数据。

　　步骤1-J2:ON筛选器(联接条件)

　　ON筛选器是在查询中可以指定的三种筛选器(ON，WHERE和HAVING)中的第一个。ON筛选器中的谓词作用域上一步返回的虚拟表(VT1-J1)中的所以行。只有使<on_predicate>为TRUE的那些行，才会包含在由这一步返回的虚拟表(VT1-J2)中。

　　步骤1-J3:添加外部行(Outer Row)

　　这一步只在外联接(outer join)中才会发生。对于外联接，通过为其指定一种外联接类型(LEFT，RIGHT或FULL)，就把一个或两个输入表标记为保留表(preserved table)，即表示希望返回该表的所有行，即时筛选器<on_predicate>过滤掉了一些行。步骤1-J3返回VT1-J2中的行，以及保留表在步骤1-J2中被过滤掉的行。这些新添加的行称为外部行(outer row)。外部行中非保留表的值被赋为NULL。最后生成虚拟表VT1-J3。

1.3.2 步骤2：WHERE阶段

　　对上一步返回的虚拟表中的所有行应用WHERE筛选器。只有让<where_predicate>逻辑条件为TRUE的行，才会组成这一步要返回的虚拟表(VT2)。

　　对于包含OUTER JOIN子句的查询，到底应该在ON筛选器还是在WHERE筛选器中指定逻辑表达式。二者的主要区别是ON筛选器在添加外部行(步骤1-J3)之前应用，而在WHERE筛选器则是在步骤1-J3之后才应用。ON筛选器对保留表中部分行的删除并不是最终的，因为步骤1-J3会把这些行再添加回来；相反，WHERE筛选器对行的删除是最终的。

1.3.3 步骤3：GROUP BY阶段

　　在GROUP BY阶段，根据<group_by_specification>指定的列表，将上一步返回的虚拟表中的行分配到各组。这里指定的是一组供分组使用的属性列表，称为分组集(grouping set)。

　　由分组集中所有属性值的每个唯一组合标识出一个组。上一步返回的每个基行(base row)都和一个组相关联，且仅关联到一个组。最终得到的虚拟表VT3由两部分组成：组织到分组中的VT2原来的行(原始信息)和组标识符(组信息)。

1.3.4 步骤4：HAVING阶段

　　HAVING筛选器用于对上一步返回的虚拟表中的组进行筛选。只有用于使<having_predicate>逻辑条件取值为TRUE的组，才会成为这一步返回的虚拟表(VT4)的一部分。HAVING筛选器是唯一可用于分组数据的筛选器。

　　注意：子查询不能作为聚合函数的输入，例如不能使用 HAVING SUM((SELECT ...)) > 10

1.3.5 步骤5：SELECT阶段

　　虽然SELECT子句出现在查询的最前面，但却放在第5步处理。SELECT阶段将构造最终返回给调用者的表。这个阶段涉及3个子阶段：(5-1)计算表达式、(5-2)应用DISTINCT子句及(5-3)应用TOP选项。

　　步骤5-1：计算表达式

　　SELECT列表中的表达式可以返回上一步得到的虚拟表的基础列，也可以是对这些基础列的操作。

　　步骤5-2：应用DISTINCT子句

　　如果在查询中指定了DISTINCT子句，将从上一步返回的虚拟表中删除重复的列，并生成虚拟表VT5-2。

　　步骤5-3：应用TOP选项

　　TOP选项是T-SQL特有的一项功能，允许指定要返回的行数或百分比(向上取整)。根据查询的ORDER BY子句来选择指定数量的行。最终生成虚拟表VT5-3。

　　如果在查询中指定的ORDER BY子句具有能够确定唯一顺序的ORDER BY列表，结果将是确定的。同样地，如果没有为ORDER BY子句指定唯一的ORDER BY列表，但为TOP选项指定了WITH TIES，结果也是确定的。SQL Server会检查返回的最后一行，并返回表中所有与最后一行具有相同排序值的其他行。

　　然而，如果指定了不唯一的ORDER BY列表，而且未指定WITH TIES选项，那么TOP查询就是非确定的，返回的行只是SQL Server碰巧在物理上最先访问到的行，因此可能会产生不同的结果，但都可以认为是正确的。 如果想保证查询结果的确定性，TOP查询就必须包含唯一的ORDER BY列表或WITH TIES选项。

1.3.6 步骤6：排序用的ORDER BY阶段

　　这一步按ORDER BY子句中的列名列表对上一步返回的行进行排序，返回游标VC6。ORDER BY子句是唯一可以重用SELECT列表中创建的列别名的步骤。

　　如果指定了DISTINCT，则ORDER BY子句中的表达式只能访问上一步返回的虚拟表(VT5)。如果没有指定DISTINCT，则ORDER BY子句中的表达式可以访问SELECT阶段的输入和输出虚拟表。也就是说，可以在ORDER BY子句中指定任何可以在SELECT子句使用的表达式。即，可以按不在最后返回的结果集中的表达式来进行排序。

　　这一步与其他所有步骤不同的是，它返回的不是一个有效的表，而是一个游标。SQL的理论基础是集合论。集合中的行之间没有预先定义的顺序，它只是成员的一种逻辑组合，成员之间的顺序无关紧要。对于带有排序作用的ORDER BY子句的查询，可以返回一个对象，其中的行按特定的顺序组织在一起。ANSI把这种对象称为游标(cursor)。理解这一步是正确理解SQL最基本的步骤之一。

　　因为这一步返回的不是表(而是游标)，所以如果查询带有排序用的ORDER BY子句，则不能用这样的查询来定义表表达式。表表达式包括：视图、内联表值函数。派生表和公用表表达式。此外，必须把查询结果返回给可以按顺序一次处理一个游标记录的客户端应用程序。

　　在SQL中，表表达式中不允许使用带有ORDER BY子句的查询。而在T-SQL中，这一规定有一个例外：如果同时也指定了TOP选项。在逻辑上，TOP选项是在ORDER BY排序阶段(步骤6)之前，作为SELECT阶段的一部分而处理的(步骤5-3)。它的目的是要基于某种顺序的逻辑定义，筛选出请求数量或百分比的行。可惜TOP选项没有设计它自己的ORDER BY子句，而是借助同一查询中的ORDER BY子句来提供逻辑排序，但查询中的ORDER BY子句却又通常用于对查询结果进行排序。这样一来，就不能为TOP选项定义一个排序，同时为查询结果再定义另一个排序，这就是TOP选项的限制。

　　当最外层的查询中使用TOP，而不是用TOP查询定义表表达式时，ORDER BY子句有两种不同的作用：其一是在步骤5-3中，为TOP选项定义行之间的逻辑优先顺序；其二是在步骤6中，为结果游标定义排序顺序。可以保证觉果是按ORDER BY排序的。

　　不过，如果带有ORDER BY子句的TOP查询是用于定义表表达式，这时它代表的就是一个没有固定顺序的表。因此，在这种情况下，ORDER BY子句只是为TOP选项定义逻辑顺序，而不保证结果的排序顺序。

　　ORDER BY子句认为两个NULL值是相等的。也就是说，所以的NULL值会排序在一起。至于NULL值比已知值大还是小，ANSI把这个问题留给了具体实现，并在排序中保持一致。T-SQL认为NULL值比已知值要小(排序时排在前面)。

1.4 逻辑查询处理的深入内容

　　本节介绍逻辑查询处理的一些深入内容，包括表运算符(JOIN、APPLY、PIVOT和UNPIVOT)、OVER子句、以及集合运算符(UNION、EXCEPT和INTERSECT)。

1.4.1 表运算符

　　SQL Server2008支持4中在查询的FROM子句使用的表运算符：JOIN、APPLY、PIOVT和UNPIVOT。APPLY、PIOVT和UNPIVOT不是ANSI标准运算符，它们是T-SQL中特有的扩展。

　　表运算符用一个或两个表作为输入。根据它们相对于表运算符关键字(JOIN、APPLY、PIOVT和UNPIVOT)的位置，它们被称为左输入和右输入。与联接一样，所有表运算符都会用虚拟表作为它们的左输入，位于FROM子句的第一个表运算用一个表表达式作为左输入并返回一个虚拟表作为结果。表表达式可以表示许多对象：真实的表、临时表、表变量、派生表、CTE、视图、表值函数。

(JOIN) <left_table_expression>
       　{CROSS | INNER | OUTER} JOIN <right_table_expression>
　　　　　ON <on_predicate>
(APPLY) <left_table_expression>
　　　　　　{CROSS | OUTER} APPLY <right_table_expression>
(PIVOT) <left_table_expression>
　　　　　　PIVOT (<aggregate_func(<aggregation_element>)> FOR
　　　　　　　　<spreading_element> IN (<target_col_list>))
　　　　　　　　AS <result_table_alias>
(UNPIVOT) <left_table_expression>
　　　　　　　　UNPIVOT (<target_values_col> FOR
　　　　　　　　<target_names_col> IN (<source_col_list>))
　　　　　　　　AS <result_table_alias>

 

APPLY:

　　APPLY运算符涉及以下两个步骤中的一步或两步。

　　1. A1：把油表表达式应用于左表的行。

　　2. A2: 添加外部行。

　　APPLY运算符把右表表达式应用于左输入中的每一行。右表达式可以引用左输入中的列，对于左表中的每一行，都要计算一次右表输入的表达式。这一步会把左边的每一行和来自右表达式的相应行进行匹配，并将生成的结果集合并起来，返回组合后的结果。

　　CORSS APPLY和OUTER APPLY总是包含步骤A1，只有OUTER APPLY才包含步骤A2。

　　例如，以下查询为每个客户返回具有最大订单ID的两个订单

SELECT C.customerid, C.city, A.orderid FROM dbo.Customers As C 
　　　　CORSS APPLY (SELECT TOP(2) O.orderid, O.customerid FROM dbo.Orders as O WHERE O.customerid = C.customerid ORDER BY orderid DESC) as A

 

PIVOT

　　PIVOT运算符用于在列和行之间对数据进行旋转或透视转换，同时执行聚合运算。PIVOT操作涉及以下三个逻辑阶段：

　　1. P1：分组

　　2. P2： 扩展

　　3. P3： 聚合

　　第一阶段会隐式地对<left_table_expression>的行进行分组，分组的一句是那些未作为PIVOT输入的所有行，就像是有一个隐藏的GROUP BY子句。

　　第二阶段将把<spreading_col>列表中的值扩展到它们相应的目标列上。在逻辑上，相当于为IN子句中指定的每个目标列使用以下CASE表达式：

CASE WHEN <spreading_col> = <target_col_element> THEN <expression> END

　　第三个阶段对每个CASE表达式应用指定的聚合函数，生成结果列。

select * from (select c.city,o.orderid from customers as c left join orders as o on c.customerid=o.customerid) as b 
　　　　pivot(sum(orderid) for city in ([Madrid],[Zion])) as a

 

UNPIVOT

　　UNPIVOT与PIVOT正好相反，它把数据从列旋转到行。

1.4.2 OVER子句

　　OVER子句用于支持基于窗口(window-based)的计算。可以随聚合函数一起使用这个子句，同时它也是四个分析排名函数(ROW_NUMBER、RANK、DENSE_RANK和NTILE)必须要求的元素。OVER子句会定义数据行的一个窗口，在这个窗口上可以执行聚合或排名函数的计算。

　　OVER子句只能用于两个逻辑阶段：SELECT阶段和ORDER BY阶段。这个子句可以访问为相应逻辑阶段提供输入的任何虚拟表。

1.4.3 集合运算符

　　SQL Server2008支持4种集合运算符：UNION ALL、UNION、EXCEPT和INTERSECT。这些SQL运算符对应与数学集合论中定义的运算符。

　　使用集合运算符的查询的一般格式

(1) query1
(2) <set_operator>
(1) query2
(3) [ORDER BY <order_by_list>]

 

　　集合运算符对两个输入中的行进行整体比较。UNION ALL返回包含两个输入中所有行的结果集。UNION返回由两个输入中不同的行组成的结果集(没有重复的行)。EXCEPT返回在第一个输入中出现，但没有在第二个输入中出现的不同行。INTERSECT返回在两个输入中都出现过的不同行。

　　在涉及集合运算的单个查询中不允许使用ORDER BY 子句，因为查询期望返回的是集合。只能在查询的最后指定ORDER BY子句，对集合运算的结果进行排序。

 

1.5 总结

　　理解逻辑查询处理阶段和SQL的一些特性，对于树立SQL编程所需要的特殊思维方式是非常重要的。熟悉了该语言的这些方面，不仅可以制定出高效的解决方案，还可以解释其中的原理。