SQL中逻辑查询处理的各个阶段

SQL中逻辑查询处理的各个阶段

[code=SQL]SQL不同于其他的编程语言的最大最大特征有3个吧，

一个是它是面向集合的编程思想，第二个是三值逻辑（这个后面会说到），还有一个就是今天主要要说的查询元素的逻辑处理次序。

请看一个基本查询的逻辑过程：
(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1)  FROM <left_table>
(3)    <join_type> JOIN <right_table>
(2)      ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>
大家可以看到 这里的运行步骤不是像一般的编程 一句句从上往下 它是跳动的 有活力的
这里提前说下 每一步都会产生一个虚拟的表（也可能是游标，下面会提到），作为下一个步骤的输入。大家最后看到的结果其实就是最后一个虚拟表了。
好了，下面我开始具体阐释每个步骤：
测试环境：
--题目要求：求出学生最低科目成绩不低于90分且年龄在7岁以上的学生姓名
create table #student(s# int,sname varchar(10),age int)
create table #study (s# int, c# char(1),score int)
insert #student
1,'xiaozhu',10 union all select
2,'xiaomao' ,9union all select
3,'xiaozhe' ,7union all select
4,'xiaophai',8 union all select
5,'xiaoduo',9
insert #study select
1,'A',99 union all select
1,'B',90 union all select
1,'C',99 union all select
2,'A',99 union all select
2,'b',99 union all select
2,'c',98 union all select
3,'A',99 union all select
3,'b',92 union all select
3,'c',91 union all select
3,'d',90 union all select
4,'A',88 union all select
4,'B',96
--SQL语句
select top 1 sname,MIN(score) as minsocre
from #student s left outer join #study sc
on s.s#=sc.s#
where age>7
group by sname
having MIN(score)>=90
order by minsocre
/*
sname      minsocre
---------- -----------
xiaozhu    90
*/

(1)：执行笛卡尔积（CROSS JOIN ）
大家这里可能会觉得奇怪 明明上面的2后面只有一个表怎么笛卡尔积呢？其实不是这样的，它做的就是要将FROM最前面2表进行CROSS JOIN（因为可能多表）
在我的例子里就是#student 和#study表进行笛卡尔积操作(这个笛卡尔积相信大家都会)，这里就会出现第一个虚拟表，我们叫它VTB1
/*

s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
1           xiaozhu    10          2           A    99
1           xiaozhu    10          2           b    99
1           xiaozhu    10          2           c    98
1           xiaozhu    10          3           A    99
1           xiaozhu    10          3           b    92
....
....
.....太多了，不占空间
5           xiaoduo    9           2           b    99
5           xiaoduo    9           2           c    98
5           xiaoduo    9           3           A    99
5           xiaoduo    9           3           b    92
5           xiaoduo    9           3           c    91
5           xiaoduo    9           3           d    90
5           xiaoduo    9           4           A    88
5           xiaoduo    9           4           B    96
*/

(2)应用ON筛选器（联接条件）
说到这里，首先得说下 SQL用于查询的有三个筛选器，分别是ON，where，having。
这里的ON筛选器就是用VTB1作为输入，再利用ON后面的联结条件进行筛选，生成VTB2。
sql编程第二特征：三值逻辑：它其实就是所谓的TRUE FALSE UNKOWN
主要来说这个UNKOWN 它这逻辑值和NULL在一起就会”出事“。凡是NULL参与的比如 NULL>42, NULL-8>9 这些结果都是UNKOWN
UNKOWN 的逻辑结果在不同的环境下是被不同的方式处理的：如
在三个筛选器上，它是会被当做FALSE处理；
在CHECK约束上是会被当成TRUE来处理，check(col>8) 你插入的COL为NULL，因为NULL>8 的结果不就是UNKOWN ，所以是可以插入的。
在筛选器上的2个NULL比较将是FALSE 但是在UNIQUE约束，分组，排序上2个NULL是等价的。
好了，大概就这些吧 。我贴下VTB2的结果：
/*
s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
2           xiaomao    9           2           A    99
2           xiaomao    9           2           b    99
2           xiaomao    9           2           c    98
3           xiaozhe    7           3           A    99
3           xiaozhe    7           3           b    92
3           xiaozhe    7           3           c    91
3           xiaozhe    7           3           d    90
4           xiaophai   8           4           A    88
4           xiaophai   8           4           B    96
*/

(3)添加外部行（outer row）
这里首先要知道一个地方：如果你的表没有涉及到OUTER JOIN 那么就不需要这步，这第三步就是为外部联接准备的。
我的测试环境下为了说明这个地方，加了LEFT JOIN ，它这里起到效果简单来说就是拿回刚才第二步损失的部分记录，因为你也看到了第五个学生'xiaoduo'
在虚拟表VTB2中没出现，这里完成后 它又回来了。vtb3
/*
s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
2           xiaomao    9           2           A    99
2           xiaomao    9           2           b    99
2           xiaomao    9           2           c    98
3           xiaozhe    7           3           A    99
3           xiaozhe    7           3           b    92
3           xiaozhe    7           3           c    91
3           xiaozhe    7           3           d    90
4           xiaophai   8           4           A    88
4           xiaophai   8           4           B    96
5           xiaoduo    9           null        null null
*/
PS: 如果你涉及到三表及三表以上操作，你就可以将这个VTB3表和你FROM后面的第三个表进行1-3步骤的重复操作，依次类推，最后再拿出一个虚拟表就OK

(4)应用WHERE筛选器
这里看起来很简单，只要将VTB3表 用WHERE 后面的条件过滤下得到VTB4表就可以。其实还是有很多知识在这的。
首先，在这我知道了为什么这里不可以用聚合函数了，因为数据在前面那几步还没被分组了，怎么可以用聚合函数呢？也知道了不可以用那些你在select
后面定义的列别名，理由很简单，你都没操作那步呢。
再者，我们考虑一个问题：我们那个条件age>7 可以写在ON后面么？答案在这里是否定的！
A：from #student s left outer join #study sc
on s.s#=sc.s#
where age>7

B：from #student s left outer join #study sc
on s.s#=sc.s# and s.age>7
这里2种情况形成的虚拟表是这样的
A:
/*
s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
2           xiaomao    9           2           A    99
2           xiaomao    9           2           b    99
2           xiaomao    9           2           c    98
4           xiaophai   8           4           A    88
4           xiaophai   8           4           B    96
5           xiaoduo    9           null        null null
*/
B:也就是VTB3
/*
s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
2           xiaomao    9           2           A    99
2           xiaomao    9           2           b    99
2           xiaomao    9           2           c    98
3           xiaozhe    7           3           A    99
3           xiaozhe    7           3           b    92
3           xiaozhe    7           3           c    91
3           xiaozhe    7           3           d    90
4           xiaophai   8           4           A    88
4           xiaophai   8           4           B    96
5           xiaoduo    9           null        null null
*/
大家可以看到，如果把AGE>7写ON后面 这个出来的临时表比写WHERE后面多了那个年龄是7岁的xiaozhe，这显然不是我们想要看到的。
原因就是因为你这个ON后面的条件是在第二步就做完了，这个时候你过滤掉了7岁的xiaozhe 可是你第三步外联接下 它就又可以回来的，
而如果写where 后面就可以防止这种情况发生了。现在可以给出结论了，写2个地方区别就在于ON过滤掉的记录可以再恢复，但是WHERE过滤掉的就回不来了。
PS：我这说的这种情况也只是在有OUTER JOIN的情况下才会出现的，如果是INNER join之类的就不会出现差别，因为它不存在第三步。
大家可以将语句换成
select  sname,MIN(score) as minsocre --这里注意把TOP 1去掉 不然看不出来了
from #student s left outer join #study sc
on s.s#=sc.s# and age>7
group by minsocre
having MIN(score)>=90
order by minsocre
/*
sname      minsocre
---------- -----------
xiaomao    98
xiaozhu    90

*/
为了清楚说明，我再写下这步产生的VTB4表：
/*
s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
2           xiaomao    9           2           A    99
2           xiaomao    9           2           b    99
2           xiaomao    9           2           c    98
4           xiaophai   8           4           A    88
4           xiaophai   8           4           B    96
5           xiaoduo    9           null        null null
*/

(5) 分组
这步就是分组，你确定你要分组的对象，它唯一的。然后根据这个基础对象，把组分好。例子里就是学生的姓名(假设没出现重名的).
这里的VTB5书上的意思就是其实由2部分组成：、
1是实际组构成的成组部分（xiaozhu,xiaomao,xiaophai,xiaoduo）这几个基础对象吧，
2就是本来那个VTB4传下来的基础数据。
这里我们就可以看出一个东西：你在SELECT后面的列 要么就包含在聚合函数里面要么就成为GROUP BY 的基础对象。
例如:你直接select sname,score 因为你分组了 你这组只能返回关于这个组的一条信息。那么对于XIAOZHU这组来说有三个SCORE，你让它如何返回？
所以我们要用聚合函数来限定这个SCORE，如MIN(SCORE)。。。。。。
这里再提一个小地方：原本在2000里面你如果GROUP BY MIN(SCORE) 你在select 后面用SELECT MIN(SCORE)+1 as newscore 这样是不可以的。
就是说：你不能对GROUP BY 的基础对象做处理，除非他是表里的列。但是2005里面你可以放心使用我上面说的情况。
由于GROUP BY ALL是非标准的遗留物，所以不推荐，我也就不说了。。
VTB5：
/*
s#          sname      age         s#          c#   score
----------- ---------- ----------- ----------- ---- -----------
1           xiaozhu    10          1           A    99
1           xiaozhu    10          1           B    90
1           xiaozhu    10          1           C    99
------------------------------------------------------------
2           xiaomao    9           2           A    99
2           xiaomao    9           2           b    99
2           xiaomao    9           2           c    98
-------------------------------------------------------
4           xiaophai   8           4           A    88
4           xiaophai   8           4           B    96
-------------------------------------------------------
5           xiaoduo    9           null        null null
*/

用 ROLLUP 汇总数据，在生成包含小计和合计的报表时，ROLLUP 运算符很有用。
CUBE 和 ROLLUP 之间的区别在于：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
例如，简单表 Inventory 中包含：