T-SQL基礎（三）之子查詢與表表達式

子查詢

在嵌套查詢中，最外面查詢結果集傳回給調用方，稱為外部查詢。嵌套在外部查詢内的查詢稱為子查詢，子查詢的結果集供外部查詢使用。

根據是否依賴外部查詢，可将子查詢分為自包含子查詢和相關子查詢。自包含子查詢不依賴外部查詢，相關子查詢則依賴外部查詢。

子查詢結果是在運作時計算的，查詢結果會跟随查詢表的變化而改變。子查詢可以傳回單個值（标量）、多個值或者整個表結果。

在邏輯上，子查詢代碼僅在外部查詢計算之前計算一次。

自包含子查詢

USE WJChi;

SELECT * FROM dbo.UserInfo WHERE Age=
(
    SELECT MAX(Age) FROM dbo.UserInfo    
);           

相關子查詢

USE WJChi;

SELECT * FROM dbo.UserInfo AS UI WHERE IdentifyId =
(
    SELECT Id FROM dbo.Identify WHERE Id=UI.IdentifyId
);           

子查詢易錯點

NULL值處理不當

USE WJChi;

SELECT * FROM dbo.Customers
WHERE custid NOT IN(
    SELECT TOP 10 C.custid FROM dbo.Customers AS C ORDER BY C.custid
);           

上述查詢語句看起來可以正常運作，但當子查詢的傳回結果集中包含NULL值時，上述查詢語句則不會傳回任何資料。解釋如下：

20 NOT IN(10, 9, 8, NULL)

等價于

NOT(20=10 OR 20=9 OR 20=8 OR 20=NULL)

，

NULL

參與的比較預算結果均為

Unknown

Unknown

參與的或運算結果依然為

Unknown

。

:warning: 我們應時刻牢記SQL是三值邏輯，這點很容易引發錯誤

列名處理不當

子查詢中的列名首先從目前查詢中進行解析，若未找到則到外部查詢中查找。子查詢中很有可能無意中包含了外部查詢的列名導緻子查詢有自包含子查詢變為相關子查詢而引發邏輯錯誤。

為避免上述錯誤，查詢中的列名盡可能使用完全限定名：

[表名].[列名]

:warning: 通常我們自己難以發現代碼中的邏輯錯誤，而我們的最終使用者嘗嘗扮演着問題發現者的角色 :joy:

編寫語義清晰明了的SQL可以很大程度的避免邏輯上的錯誤

表表達式

表表達式，也可稱為表子查詢，是一個命名的查詢表達式，表示一個有效的關系表，是以表表達式必須滿足以下三個條件：

1. 無法表表達式結果集順序

表表達式表示一個關系表，關系型資料庫基于集合理論，表中的資料是無序的。标準SQL中不允許在表表達式中使用

ORDER BY

子句，除非

ORDER BY

子句用于展示之外的其他目的，否則會報錯：

除非另外還指定了 TOP、OFFSET 或 FOR XML，否則，ORDER BY 子句在視圖、内聯函數、派生表、子查詢和公用表表達式中無效.

:warning:在查詢表表達式時，除非在外部查詢中指定了

ORDER BY

子句，否則無法保證查詢結果集中資料的順序。有時候會看到即使外部查詢未使用

ORDER BY

但查詢結果集按預期順序傳回了結果，這是由于資料庫自身優化的結果，依然無法保證每次查詢都能按預期結果傳回。

1. 所有列必須顯式指定名稱
2. 所有列名必須唯一

表表達式分為：派生表、公用表表達式、視圖三種類型。其中，派生表與公用表表達式隻适用于單語句範圍，即，隻存在于目前查詢語句中。視圖則可以被多條查詢語句複用。

派生表

派生表又稱為子查詢表，在外部查詢的FROM子句中進行定義，一旦外部查詢結束，派生表也就不複存在。

在一次查詢中派生表無法被多次引用，若要多次引用，則需要多次書寫派生表：

USE WJChi;

SELECT 
    Cur.orderyear, Prv.numcusts AS prvnumcusts, 
    Cur.numcusts - Prv.numcusts AS growth
FROM (
    SELECT 
        YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
    FROM dbo.Orders 
    GROUP BY YEAR(orderdate) AS Cur
    LEFT JOIN
    -- 為了再次使用派生表，需要重複書寫相同邏輯
    SELECT 
        YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
    FROM dbo.Orders 
    GROUP BY YEAR(orderdate) AS Prv
    ON Cur.orderyear = Prv.orderyear + 1
);           

公用表表達式

公用表表達式(CTE)定義方式如下：

WITH...AS
(
    ...
)           

與派生表類似，外部查詢完成後，CTE也就消失了。但，不同于派生表，CTE可以在一次查詢中多次使用（但不能嵌套使用而派生表可以）：

USE WJChi;

WITH YearlyCount AS
(
    SELECT 
        YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts
    FROM dbo.Orders
    GROUP BY YEAR(orderdate)
)
SELECT 
    Cur.orderyear, Prv.numcusts AS prvnumcusts
FROM YearlyCount AS Cur
LEFT JOIN
-- 再次使用CTE
YearlyCount AS Prv
ON Cur.orderyear = Prv.orderyear + 1;           

這裡需要注意一點：CTE之前的SQL語句要以分号（;）結尾。

我們也可以在一次查詢中定義多個CTE：

-- WITH隻需要使用一次
WITH Temp1 AS
(
),
Temp2 AS
(
)
SELECT ...           

視圖

視圖是虛拟表，自身不包含資料，隻存儲了動态查詢語句，多用于簡化複雜查詢。

視圖建立後被作為資料庫對象而存儲到資料庫中，除非顯式進行删除。是以，同一個視圖可以被不同的查詢多次使用。

使用以下語句建立視圖：

CREATE VIEW ViewName
AS
...           

修改視圖：

ALTER VIEW ViewName
AS
...           

删除視圖：

DROP VIEW ViewName;           

視圖是資料庫中的對象，是以我們可以控制其通路權限，如：SELECT、UPDATE或通路視圖底層資料表等。

視圖一旦建立，在底層資料表發生變更後，其不會自動更新。是以，在視圖中使用SELECT語句時盡可能顯式的指定所需列，而不是使用

SELECT *

。可以使用存儲過程：

sp_refreshview

和

sp_refreshsqlmodule

來更新視圖的中繼資料，或者使用ALTER語句修改視圖定義。

關于是否應該使用視圖，仁者見仁，智者見智：

使用SQL Server視圖的優缺點 為什麼mysql中很少見到使用視圖功能？

小結

不要讓資料庫（查詢）變得複雜；

表表達式有助于簡化代碼以提升可讀性與可維護性；

推薦閱讀

T-SQL基礎（二）之關聯查詢