~楓花雪岳~: [SQL] T-SQL 語法邏輯順序

星期五, 4月 05, 2013

[SQL] T-SQL 語法邏輯順序

T-SQL 執行時有其邏輯順序，根據不同邏輯順序會產生不一樣的結果，官方文章上分散在兩篇文章內，分別為

SELECT - Logical processing order of the SELECT statement
FROM clause plus JOIN, APPLY, PIVOT - on-search_condition

該篇會把內容整合記錄

Sample Data

每個學生擁有的書籍來當成範例說明

DECLARE @STD TABLE (STDNO char(3),STDName nchar(10))
INSERT INTO @STD VALUES('001',N'張三')
INSERT INTO @STD VALUES('002',N'李四')
INSERT INTO @STD VALUES('003',N'王五')

DECLARE @Books TABLE (ID int,STDNO char(3) , Kind char(10) , BookName char(30))
INSERT INTO @Books VALUES(1,'001','SQL','SQL Server T-SQL')
INSERT INTO @Books VALUES(2,'001','SQL','SQL Server Turning')
INSERT INTO @Books VALUES(3,'001','SQL','SQL Server Statistics')
INSERT INTO @Books VALUES(4,'003','Win','Windows Server')
INSERT INTO @Books VALUES(5,'003','SQL','SQL Server T-SQL')
INSERT INTO @Books VALUES(6,'003','Win','HyperV')

Logical processing order of the SELECT statement

官方文件內容說明

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order might vary from this list.

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE 或 WITH ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP

文件上的備註說明

The preceding sequence is usually true. However, there are uncommon cases where the sequence might differ.

For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. In this situation, the CONVERT can execute before the WHERE clause executes. Uncommon indeed. Often there is a way to modify your view to avoid the different sequence, if it matters in your case.

以欄位別名為例，利用兩個簡單範例來說明上述邏輯順序

-- 簡易範例 1
SELECT STDNO AS 學生編號 , STDName AS 學生姓名
FROM @STD
ORDER BY 學生編號 DESC -- 利用欄位別名來排序

ORDER BY 的順序在 SELECT 之後，因此 ORDER BY 可以指定 SELECT 的欄位別名，當成排序的欄位選擇。

-- 簡易範例 2
SELECT STDNO AS 學生編號 , STDName AS 學生姓名
FROM @STD
WHERE 學生編號 = '001' -- 會出現此錯誤訊息"無效的資料行名稱 '學生編號'"。

WHERE 的邏輯順序，因為在 SELECT 之前，所以 WHERE 條件內不可以指定欄位別名來當成條件

on-search_condition

官方文件說明

There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates doesn't make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

計算每個學生擁有關於 SQL 的書籍有多少本，沒有的學生必須秀 0 來表示，藉此來說明 LEFT JOIN 篩選條件位置（ON 或 WHERE）的差異

-- WHERE 內條件判斷
SELECT 
  A.STDNO AS 學生編號 , 
  COUNT(B.BookName) AS 書籍數量
FROM @STD AS A 
  LEFT JOIN @Books AS B ON A.STDNO = B.STDNO
WHERE B.Kind = 'SQL'
GROUP BY A.STDNO

-- ON 內條件判斷
SELECT 
  A.STDNO AS 學生編號 , 
  COUNT(B.BookName) AS 書籍數量
FROM @STD AS A 
  LEFT JOIN @Books AS B ON A.STDNO = B.STDNO AND B.Kind = 'SQL' 
GROUP BY A.STDNO