跟我學 MS SQL Server（七）：子查詢刪除資料實戰與陷阱避坑指南

一、核心問題：依據其他表條件刪除本表資料

標準 SQL-92 不支援 `DELETE ... JOIN`，標準做法使用子查詢配合 `IN`/`EXISTS` 將外部條件映射為刪除條件。

二、標準範本：寵物店範例

-- 刪除品種為 'Shitzu' 的所有庫存記錄
DELETE FROM Inventory
WHERE BreedID IN (
    SELECT BreedID FROM Breed WHERE BreedName = 'Shitzu'
);

關鍵點

1. 子查詢回傳單一欄位 (`BreedID`)，外層 `WHERE` 使用 `IN` 比對
2. 子查詢為非相關子查詢：不引用外層資料表欄位，獨立執行
3. 等效 `EXISTS` 寫法 (通常效能更佳，尤其有索引時)：

   DELETE FROM Inventory i
   WHERE EXISTS (
       SELECT 1 FROM Breed b
       WHERE b.BreedID = i.BreedID
       AND b.BreedName = 'Shitzu'
   );

三、安全測試鐵律：先 SELECT 再 DELETE

-- 1. 驗證將被刪除的資料
SELECT * FROM Inventory
WHERE BreedID IN (SELECT BreedID FROM Breed WHERE BreedName = 'Shitzu');

-- 2. 確認無誤後，執行 DELETE
DELETE FROM Inventory
WHERE BreedID IN (SELECT BreedID FROM Breed WHERE BreedName = 'Shitzu');

絕對不要跳過步驟 1，`DELETE` 無 `WHERE` 或條件錯誤將導致資料永久遺失。

四、DELETE + JOIN：非標準但廣泛支援

SQL Server、MySQL、PostgreSQL、Oracle (特定語法) 支援：

-- SQL Server / MySQL 語法
DELETE i
FROM Inventory i
JOIN Breed b ON i.BreedID = b.BreedID
WHERE b.BreedName = 'Shitzu';

-- Oracle 需用別名關聯
DELETE FROM Inventory i
WHERE EXISTS (SELECT 1 FROM Breed b WHERE b.BreedID = i.BreedID AND b.BreedName = 'Shitzu');

建議：生產環境優先使用標準 `EXISTS` 子查詢，可移植性最強，優化器成熟。

五、多表級聯刪除：標準無解，需應用層協調

SQL-92 `DELETE` 單陳述式僅能指定一個目標表。解決方案：

1. 應用層事務：依順序執行多個 `DELETE` (先子表後父表)，包裝在分散式交易
2. 外鍵 CASCADE DELETE：定義 `ON DELETE CASCADE`，資料庫自動級聯 (需謹慎評估業務影響)
3. 軟刪除：增加 `IsDeleted BIT` / `DeletedAt DATETIME` 欄位，邏輯刪除替代物理刪除
4. 預存程序：封裝多表刪除邏輯，統一錯誤處理與審計

六、效能優化關鍵

七、分批刪除範本 (SQL Server)

DECLARE @BatchSize INT = 5000, @RowCount INT = 1;
WHILE @RowCount > 0
BEGIN
    DELETE TOP (@BatchSize) FROM Inventory
    WHERE BreedID IN (SELECT BreedID FROM Breed WHERE BreedName = 'Shitzu');
    SET @RowCount = @@ROWCOUNT;
    -- 可選：短暫延遲釋放鎖
    WAITFOR DELAY '00:00:00.050';
END;

八、常見錯誤與修正

下一篇將進入串行資料類型篇：`CHAR`、`VARCHAR`、`NCHAR`、`NVARCHAR`、`TEXT`、`VARCHAR(MAX)` 儲存機制、排序規則、Unicode 處理與遷移策略。