你真的了解T-SQL逻辑查询吗?

MS SQL Server 2005 逻辑查询处理中的各个阶段(一)

大家好，我是浪客，和大家分享一些最近我从《Microsoft SQL SERVER 2005技术内幕：T-SQL查询》书中的心得，时间仓促，有错误大家尽管提出来。

先来看个查询:

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_codition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) WITH {CUBE | ROLLUP}

(7) HAVING <having_condition>

(10) ORDER BY <order_by_list>

熟悉SQL的人应该一眼就能看出来上面语句的作用，基本包括了我们一般查询表的写法，SQL不同于其他编程语言最明显的特征就是他的代码顺训。在大多数语言中，代码按编码的顺序被处理，但在SQL语言中，第一个被处理的子句是FROM子句，尽管SELECT语句第一个出现，但几乎总是最后处理。

         其实从逻辑上来说，每个步骤都会产生一个虚拟表，该虚拟表用作下一个步骤的输入，下面就一步一步来分析，由于我们只分析其中的逻辑处理顺序，所以很多效率问题没有考虑太多，关于查询优化的文章，我会在以后慢慢分享给大家，呵呵（上面的标记代表执行步骤的顺序）。

为了演示这些步骤，我们打开MS SQL SERVER 2005 -SQL Server Management Studio新建一个查询输入：

SET NOCOUNT ON;

USE tempdb;

GO

IF OBJECT_ID('dbo.orders') IS NOT NULL

DROP TABLE dbo.orders;

GO

IF OBJECT_ID('dbo.customers') IS NOT NULL

DROP TABLE dbo.customers;

GO

CREATE TABLE dbo.customers

(

customerid CHAR(5) NOT NULL PRIMARY KEY,

city VARCHAR(10) NOT NULL

);

INSERT INTO dbo.customers

SELECT 'FISSA','Madrid'

UNION ALL

SELECT 'FRNDO','Madrid'

UNION ALL

SELECT 'KRLOS','Madrid'

UNION ALL

SELECT 'MRPHS','Zion'

CREATE TABLE dbo.orders

(

orderid INT  NOT NULL PRIMARY KEY,

customerid CHAR(5) NULL REFERENCES customers(customerid)

);

INSERT INTO dbo.orders

SELECT 1,'FRNDO'

UNION ALL

SELECT 2,'FRNDO'

UNION ALL

SELECT 3,'KRLOS'

UNION ALL

SELECT 4,'KRLOS'

UNION ALL

SELECT 5,'KRLOS'

UNION ALL

SELECT 6,'MRPHS'

UNION ALL

SELECT 7,NULL

我们建立了两个表，一个是customers 客户表，字段按分别为id（用户的姓名） 和城市，

Orders表是订单 字段分别为 id和客户的姓名，下面我们再新建一个查询：

SELECT c.customerid,COUNT(o.orderid) AS numorders

FROM dbo.customers as c

LEFT OUTER JOIN dbo.orders AS o

ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

ORDER BY numorders;

运行结果如下：

FISSA  0

FRNDO  2

查询的作用我想大家都应该知道吧，就不说了

1.       FROM：对FROM子句中的前两个表执行笛卡尔积（交叉连接 CROSS JOIN）生成一个虚拟表VIRTUAL TABLE 1 简称VT1。这个过程是怎么样的，我们大致可以用一个查询来表示代码如下：

SELECT c.customerid,c.city,o.orderid,o.customerid

FROM customers AS c

CROSS JOIN orders AS o

运行结果如下：

         切图切不下来大家讲究下，我直接Copy 下来的。

FISSA         Madrid     1       FRNDO

FISSA         Madrid     2       FRNDO

FISSA         Madrid     3       KRLOS

FISSA         Madrid     4       KRLOS

FISSA         Madrid     5       KRLOS

FISSA         Madrid     6       MRPHS

FISSA         Madrid     7       NULL

FRNDO     Madrid     1       FRNDO

FRNDO     Madrid     2       FRNDO

FRNDO     Madrid     3       KRLOS

FRNDO     Madrid     4       KRLOS

FRNDO     Madrid     5       KRLOS

FRNDO     Madrid     6       MRPHS

FRNDO     Madrid     7       NULL

KRLOS       Madrid     1       FRNDO

KRLOS       Madrid     2       FRNDO

KRLOS       Madrid     3       KRLOS

KRLOS       Madrid     4       KRLOS

KRLOS       Madrid     5       KRLOS

KRLOS       Madrid     6       MRPHS

KRLOS       Madrid     7       NULL

MRPHS     Zion          1       FRNDO

MRPHS     Zion          2       FRNDO

MRPHS     Zion          3       KRLOS

MRPHS     Zion          4       KRLOS

MRPHS     Zion          5       KRLOS

MRPHS     Zion          6       MRPHS

MRPHS     Zion          7       NULL        

2.       嘿嘿，聪明的朋友可能已经发现CROSS JOIN 所谓的笛卡尔积其实就是为限定的联接，也就是说，如果左表包含n，右表包含m，总结果就是n*m行的结果，细心数一下就是28行。前面的orders有7条记录，customers有4条记录，总结果就有4*7=28行。所以前面我也就说过，其实最先执行的是FROM子句，也就是先获取最先的两个表的笛卡尔积。

下面我们进行第2个步骤的演示，看最上面的那个查询语句，接下来就要执行的是ON条件的筛选（ON,WHERE,HAVING）中的第一个，ON筛选器中的逻辑表达式被应用到上一步的返回的虚拟表VT1中的所有行。只有使 <join_codition> 的条件为TRUE 时候那些行才会被包含到步骤2返回的虚拟表VT2中。

在这里我给大家说个相关联的知识，也很重要噢。

在SQL中逻辑表达式的可能值包括TRUE,FALSE,UNKOWN。他们就叫三值逻辑，SQL中的UNKNOWN逻辑值通常出现在包含NULL值的逻辑表达式中（例如，NULL>42,NULL=NULL）。NULL值通常表示丢失或者不相关的值。当比较丢失值和另一个值（也可能是NULL）时，逻辑结果总是为UNKONWN。

下面我给大家总结了几个用法：

1.在筛选语句中（ON,WHERE,HAVING）中所有UNKONWN都当作FALSE来处理，包括NULL IS NULL==之类的语句

2.在CHECK约束中，当作TRUE处理，比如你的CHECK 约束为 salary>30 当用户插入一个NULL值的时候，NULL>30 是返回TRUE的。

3.在UNIQUE唯一约束中，当作TRUE来处理，所以不能插入两个都为NULL的值。

4.GROUP BY 和 ORDER BY 都能把NULL分到一起去。

所以当

SELECT c.customerid,COUNT(o.orderid) AS numorders

FROM dbo.customers as c

LEFT OUTER JOIN dbo.orders AS o

ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

HAVING COUNT(o.orderid)<3

ORDER BY numorders;

执行的时候 ON 语句把刚才获取的28行记录筛选成如下：

代码：

SELECT c.customerid,c.city,o.orderid,o.customerid

FROM customers AS c

CROSS JOIN orders AS o

WHERE c.customerid=o.customerid

大家不要看到是WHERE就条件就忽略了 ON CROSS JOIN  不能使用 ON 语句作为连接条件我就用WHERE 模拟一下哈，要记住执行顺序还是ON哦～。

结果如下：

FRNDO      Madrid     1       FRNDO

FRNDO      Madrid     2       FRNDO

KRLOS        Madrid     3       KRLOS

KRLOS        Madrid     4       KRLOS

KRLOS        Madrid     5       KRLOS

MRPHS      Zion          6       MRPHS

是不是发现不同customerid 和有NULL出现的记录全部给PASS了，哇哈哈，不要激动下一步。

（厕所中。。。）

3.       回来了，好的我们继续，下一步就是步骤3，也就是传说中的<join_type> JOIN ，上一步我们把VT2该过滤的都已经搞定了，所以要给他的外部链接OUTER JOIN 指定一种类型

就我现在所学的知识大概就知道（LEFT,RIGHT,FULL之类的），

其实说起来很简单,这几种连接很简单，通过上面的例子

LEFT JOIN 就是把左边的表 也就是在我们这里的customers表的记录作为保留表，就是把他的所有记录又添加进来，RIGHT JOIN 就是右边的表 呵呵，FULL 就是全连接也就是把记录又重新全部给添加进来了 右边和左边的，步骤3返回VT2中的行以及保留表在步骤2被过滤的行，看如下：

SELECT c.customerid,c.city,o.orderid,o.customerid

FROM customers AS c

LEFT OUTER JOIN orders AS o

ON c.customerid=o.customerid

返回的结果是

FISSA         Madrid     NULL         NULL

FRNDO     Madrid     1       FRNDO

FRNDO     Madrid     2       FRNDO

KRLOS       Madrid     3       KRLOS

KRLOS       Madrid     4       KRLOS

KRLOS       Madrid     5       KRLOS

MRPHS     Zion          6       MRPHS

我对比 步骤2的返回结果和 步骤3的 发现只多出来一行

FISSA         Madrid     NULL         NULL

看看是为什么呢？

步骤2中的左表是customers 一共有FRNDO，KRLOS，MRPHS这三个大哥，LEFT OUTER JOIN 毛了，所以他就把剩下的被T出去的FISSA 给拉了回来，也就是FISSA ，所以就有了这条记录：

FISSA         Madrid     NULL         NULL，

提示：只有在使用外部连接的时候才会执行步骤3，同时如果有多个OUTER JOIN 会一直重复步骤1，2，3知道所有表都被遍历了，当然在使用GROUP BY ALL选项的时候有一些例外，精彩一会继续。

4.       我们继续步骤4，WHERE 大哥筛选器，也就是只有符合<where_condition>条件的记录才会返回到虚拟表VT4，在这里我们又来说说几个小技巧。

首先在数据还没有分组的时候，我们不能是哟哦那个聚合筛选器，例如，不能使用WHERE orderdate=MAX(orderdate)。也不能引用SELECT 列表中的别名，因为SELECT列表这时还未被处理，SELECT 在步骤8呢，就像引用当然出错，例如不能使用SELECT YEAR(orderdate) AS u WHERE u>2000。在这里的条件是WHERE c.city=’Madrid’，代码如下：

SELECT c.customerid,c.city,o.orderid,o.customerid

FROM customers AS c

LEFT OUTER JOIN orders AS o

ON c.customerid=o.customerid

WHERE c.city='Madrid'

结果：

FISSA         Madrid     NULL         NULL

FRNDO     Madrid     1       FRNDO

FRNDO     Madrid     2       FRNDO

KRLOS       Madrid     3       KRLOS

KRLOS       Madrid     4       KRLOS

KRLOS       Madrid     5       KRLOS

发现所有的记录城市这一列都是Madrid，所以生成的虚拟表VT4，就是这样的

5.       步骤5，也就是执行接下来的GROUP BY 分组操作了，说白了，就是把每个唯一的值组合在一组，而且只有一组哦，一组很关键，下面就知道了呵呵。如果我们认为简单就是执行

SELECT c.customerid,c.city,o.orderid,o.customerid

FROM customers AS c

LEFT OUTER JOIN orders AS o

ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

我们就错了，运行我们会得到

消息8120，级别16，状态1，第1 行

选择列表中的列'customers.city' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。这样的错误，

原因可想而知，我们先假设条件成立 返回结果是：

FISSA         Madrid     NULL         NULL

FRNDO     Madrid     1       FRNDO

FRNDO     Madrid     2       FRNDO

KRLOS       Madrid     3       KRLOS

KRLOS       Madrid     4       KRLOS

KRLOS       Madrid     5       KRLOS

也都分组了，可是我们发现

FRNDO     Madrid     1       FRNDO

FRNDO     Madrid     2       FRNDO

KRLOS       Madrid     3       KRLOS

KRLOS       Madrid     4       KRLOS

KRLOS       Madrid     5       KRLOS

 

中都有同名的FRNDO和KRLOS 也就是说，他们不能分在一组了，因为他们重名了，前面说过每个唯一的值组合在一组，而且只有一组，可是现在出现了

FRNDO     Madrid     1       FRNDO

FRNDO     Madrid     2       FRNDO

和

KRLOS       Madrid     3       KRLOS

KRLOS       Madrid     4       KRLOS

KRLOS       Madrid     5       KRLOS

这个情况，虽然我们能理解他们是一组，可SQL 不这么认为，因为记录不唯一，所以我们不能这么做，GROUP BY 只能操作列表中的列或者聚合函数。

SELECT c.customerid,COUNT(o.orderid) AS numorders

FROM dbo.customers as c

LEFT OUTER JOIN dbo.orders AS o

ON c.customerid=o.customerid

WHERE c.city='Madrid'

GROUP BY c.customerid

也就只有这样做了，

FISSA         0

FRNDO     2

KRLOS       3

没个记录都唯一，也都是一组。

前面我们提到了 GROUP BY ALL，他是微软的非标准遗留物，不怎么推荐使用噢，使用了 GROUP BY c.customerid 以后 在前面用WHERE 筛选的记录有会重新被添加到虚拟表中VT5中来噢，也就是MRPHS 这条记录，你可以自己尝试一下。

6.       步骤6，我以后和大家说 CUBE和ROLLUP

 

7.       用HAVING 筛选器，只有符合条件<having_condition>的组才会添加到虚拟表VT6中来，HAVING是第一个也是唯一一个应用到已分组数据的筛选器哦，

HAVING COUNT(o.orderid)<3，我们查找 订单数量小于3的所有记录，因为

FISSA 0

FRNDO      2

KRLOS        3

中的 KRLOS 因为订单数量=3 所以把他K了哦，返回虚拟表VT7。

这里同样给大家提示，指定COUNT（o.orderid）而不是COUNT(*)是非常重要的,因此该链接是外部联接,没有订单的消费者将作为外部行添加到结果集，所以无法准备统计FISSA的订单数。COUNT（o.orderid）会准确统计每个消费者的订单数。顺便子查询中不能用聚合函数的输入，例如，HAVING SUM((SELECT …))>10。

8.       处理SELECT 了，这个太简单了，不用我多说了吧，步骤8中，有个小技巧提示一下:

UPDATE dbo.t1 set c1=c2,c2=c1;是成立的，因为存储引擎会瞬间操作，所以不用担心，要使用一个中间变量来交换他们的值，呵呵。

9.       如果语句引用了DISTINCT子句，将从上一步返回的虚拟表中移除重复的行，生成虚拟表9了。在我们的示例查询中，没有使用，不过如果使用了GROUP BY ，再使用DISTINCT是多余的，因为GROUP BY 不可能会先记录一样的哦，他分组是唯一的，前面我们说到了。

10.   应用到了ORDER BY ，这里就有很多知识了，这是唯一一个不生成虚拟表的操作步骤，他其实是返回一个游标。这一步也是唯一一个可以使用SELECT 列表中的列表名，因为他在SELECT后面发生，嘿嘿奸诈吧。ORDER BY 中 2000 数据库和2005 有点区别，具体我就不细说了，2005他实现ANSI:1999的规定，允许访问SELECT 阶段的输入虚拟表和输出虚拟表，也就是，如果未指定DISTINCT，你可以在ORDER BY 中子句中使用任何在SELECT 中出现的表达式，也就是说，你可以按最后结果集中不存在的表达式排序，例如一表有t1,t2，字段你就可以使用select t1 from 表 order by t2 ，t2没有出现在select列表中，但是t2可以出现在t2 中，所以可以实现。但是指定了DISTINCT就不能访问未返回的表达式。

还有就是ORDER BY 子句的查询不能用作表表达式，表表达式包括，视图、内联表值函数、子查询、派生表、和共用表表达式(CTE)。

下面是错误的：

Select * from (select * from table order by date ) as d;

和

Create view dbo.err

As

Select * from table order by date desc。

11.   最后一步是TOP选项。好累啊。简单说下，top 是T-SQL 特有的不属于关系范畴。这一步根据物理查询确定哪些行被优先请求。

当ORDER BY 中是唯一的ORDER BY ID 只有一个子句，结果是确定的，多了就也许不确定噢，不过加了 TOP选项后面加了 WITH TIES 结果也就是唯一的啦。如果没有指定ORDER BY 或者指定了不唯一的ORDER BY 而未制定WITH TIES 结果是不确定的 。所以只有指定了ORDER BY 才用TOP哦。

总结：这就是SQL SERVER 2005中的查询逻辑处理阶段，可是实际的物理查询也有与此有区别的。我们会在下一个章节中提到。就这样再见。

                                                BY-浪客  

Email:beyondmj0.student@sina.com.