关于SQLServer2005的学习笔记—SQL查询解析步骤

最近一来因工作上的事情比较闹心，没心事再研究SQLServer2005的体系结构；一来关于体系结构确实过于复杂，远远不如应用来的直接明了，所以暂时搁笔。

出于工作上的需要，对应用开发需要更多的了解，所以把心事暂时放到这方面。

 

先从最简单的入手来分析一下SQL的执行步骤，为什么了解执行步骤，其实与JOIN后的ON条件和WHERE条件容易混淆有关系。

是先执行ON还是先执行WHERE，很大程度上会决定SQL的结果集正确与否。

CREATE TABLE Customers

(

 CustomerID CHAR(5)    NOT NULL PRIMARY KEY,

 City       VARCHAR(10) NOT NULL

);

CREATE TABLE Orders

(

 OrderID    INT    NOT NULL PRIMARY KEY,

 CustomerID CHAR(5) NULL REFERENCES Customers(CustomerID)

);

 

INSERT INTO Customers VALUES('FISSA','Madrid');

INSERT INTO Customers VALUES('FRNDO','Madrid');

INSERT INTO Customers VALUES('KRLOS','Madrid');

INSERT INTO Customers VALUES('MRPHS','Zion');

INSERT INTO Orders VALUES(1,'FRNDO');

INSERT INTO Orders VALUES(2,'FRNDO');

INSERT INTO Orders VALUES(3,'KRLOS');

INSERT INTO Orders VALUES(4,'KRLOS');

INSERT INTO Orders VALUES(5,'KRLOS');

INSERT INTO Orders VALUES(6,'MRPHS');

INSERT INTO Orders VALUES(7,NULL);

 

试看看以上两个语句有什么不同，你就会发现很有趣的现象。

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

 WHERE C.City='Madrid'

 GROUP BY C.CustomerID

 HAVING COUNT(O.OrderID)<3

 ORDER BY NumOrders;

 

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

   AND C.City='Madrid'

 GROUP BY C.CustomerID

 HAVING COUNT(O.OrderID)<3

 ORDER BY NumOrders;

 

--Step1，首先对FROM后面的表进行笛卡尔乘积，生成虚表STEP1

WITH STEP1

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

 FROM Customers C,Orders O

)

SELECT * FROM STEP1

 

--Step2，再次应用ON语句中的条件，如果没有外关联的话，这里的ON和WHERE实际上是没有什么差别的，生成虚表STEP2

WITH STEP2

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

 FROM Customers C

 JOIN Orders O

   ON C.CustomerID=O.CustomerID

)

SELECT * FROM STEP2

 

--Step3，如果指定了OUTER JOIN，SQL会自动把STEP2表中未匹配的行作为外部行添加到STEP3中，此处找到了CustomerID=FISSA,City=Madrid，这个没有订单但又有相关名字的用户

WITH STEP3

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

)

SELECT * FROM STEP3

 

--Step4，应用WHERE条件，过滤不符合条件的记录

AS

(

SELECT C.CustomerID C_CustomerID,C.City C_City,O.OrderID O_OrderID,O.CustomerID O_CustomerID

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

 WHERE C.City='Madrid'

)

SELECT * FROM STEP4

 

--Step5，对以上的结果集进行分组

WITH STEP5

AS

(

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

 WHERE C.City='Madrid'

 GROUP BY C.CustomerID

)

SELECT * FROM STEP5

 

--Step6，处理CUBE、ROLLUP之类的语句，此处无此需求

略

SELECT * FROM STEP6

 

--Step7处理Having筛选器，与WHERE条件有些类似

WITH STEP7

AS

(

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

 WHERE C.City='Madrid'

 GROUP BY C.CustomerID

 HAVING COUNT(O.OrderID)<3

)

SELECT * FROM STEP7

 

--Step8，处理SELECT列表，即别名转换把COUNT(O.OrderID)转换成NumOrders

略

SELECT * FROM STEP8

 

--Step9，应用DISTINCT语句，此处无此需求

略

SELECT * FROM STEP9

 

--Step10，应用ORDER BY语句进行排序

SELECT C.CustomerID,COUNT(O.OrderID) AS NumOrders

 FROM Customers C

 LEFT OUTER JOIN Orders O

   ON C.CustomerID=O.CustomerID

 WHERE C.City='Madrid'

 GROUP BY C.CustomerID

 HAVING COUNT(O.OrderID)<3

 ORDER BY NumOrders

 

--Step11，执行TOP选项，此处无此需求

略

SELECT * FROM STEP11