SQL Server 2005 子查詢同名欄位解析陷阱：可能導致誤刪全表資料

現象重現

-- 建立測試環境
CREATE TABLE SubQueryBug (ID1 INT);
INSERT INTO SubQueryBug VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

CREATE TABLE SubQueryBug2 (ID2 INT);
INSERT INTO SubQueryBug2 VALUES (1);

-- 直接查詢：預期報錯
SELECT ID1 FROM SubQueryBug2;  -- Msg 207, Invalid column name 'ID1'

-- 子查詢中使用外層同名欄位：卻不報錯！
SELECT * FROM SubQueryBug WHERE ID1 IN (SELECT ID1 FROM SubQueryBug2);
-- 結果：傳回 SubQueryBug 全部 10 筆資料！

-- 若改為 DELETE
DELETE FROM SubQueryBug WHERE ID1 IN (SELECT ID1 FROM SubQueryBug2);
-- 後果：SubQueryBug 全表資料被清空！

根本原因：欄位解析規則

SQL Server 解析子查詢欄位時，遵循由內而外的作用域規則：

1. 先在子查詢的 FROM 子句資料表中尋找欄位。
2. 找不到則往外層查詢的作用域尋找。
3. 外層查詢剛好有同名欄位 ID1，因此子查詢的 ID1 解析為外層參考。

等價於：

SELECT * FROM SubQueryBug sqb
WHERE sqb.ID1 IN (SELECT sqb.ID1 FROM SubQueryBug2);

因為 SubQueryBug2 有 1 列，子查詢傳回外層該列的 ID1 值（10 筆皆為各自的 ID1），IN 條件恆成立，導致全表命中。

版本差異

• SQL Server 2005：確實存在此行為（已驗證）。
• SQL Server 2008+：行為相同，並非 Bug 而是 ANSI SQL 標準行為（相關子查詢外部參考）。

防禦性寫法：永遠使用別名與雙部分名稱

-- 正確寫法：明確指定來源
SELECT * FROM SubQueryBug AS a
WHERE a.ID1 IN (SELECT b.ID1 FROM SubQueryBug2 AS b);  -- b 無 ID1，直接報錯

-- 或使用 EXISTS（語意更清晰、效能通常更好）
SELECT * FROM SubQueryBug AS a
WHERE EXISTS (SELECT 1 FROM SubQueryBug2 AS b WHERE b.ID2 = a.ID1);

最佳實務清單

1. 所有資料表在查詢中務必加別名（AS t）。
2. 所有欄位引用務必加前綴（t.col）。
3. 子查詢優先考慮 EXISTS / NOT EXISTS，避免 IN 配合同名欄位的陷阱。
4. 啟用 SSMS 選項：Tools → Options → Text Editor → Transact-SQL → IntelliSense → Enable，輸入別名後自動提示欄位，減少手誤。
5. Code Review 時檢查是否有「無前綴欄位出現在子查詢 SELECT 清單」。

補充：相關子查詢外部參考的合法用途

-- 找出沒有對應訂單的客戶
SELECT c.CustomerID, c.CustomerName
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

這是標準用法，關鍵在於明確指定外部參考來源，而非依賴隱含解析。