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 但查詢結果集按預期順序返回了結果，這是由於資料庫自身優化的結果，依然無法保證每次查詢都能按預期結果返回。

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

3. 所有列名必須唯一

表表達式分為：派生表、公用表表達式、檢視三種類型。其中， 派生表與公用表表達式只適用於單語句範圍，即，只存在於當前查詢語句中。檢視則可以被多條查詢語句複用 。

派生表

派生表又稱為子查詢表， 在外部查詢的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語句修改檢視定義。

關於是否應該使用檢視，仁者見仁，智者見智：

ofollow,noindex" target="_blank"> 使用SQL Server檢視的優缺點

為什麼mysql中很少見到使用檢視功能？

小結

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

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

推薦閱讀

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