SQL 查詢邏輯差異深度解析：NOT IN vs != 為何結果不同

題目回顧

查詢「沒有銷售 'GEO POSITIONING SYSTEM' 的部門」。

• 標準解法：SELECT deptname FROM qdept WHERE deptname NOT IN (SELECT deptname FROM qsale WHERE itemname = 'Geo Positioning System')
• 自行嘗試：SELECT DISTINCT deptname FROM qsale WHERE itemname != 'Geo Positioning System'

兩者結果完全不同，原因何在？

核心邏輯差異

| 查詢 | 語義 | 結果集包含 | |------|------|------------| | **NOT IN 子查詢** | 部門在銷售表中**完全沒有**該產品的記錄 | 從未賣過 GPS 的部門（包括完全無銷售記錄的部門） | | **!= 過濾** | 部門在銷售表中**至少有一條**非 GPS 的記錄 | 賣過「非 GPS 產品」的部門（即使也賣過 GPS） |

具體數據例子

-- qsale 表數據
deptname   | itemname
-----------|-------------------
銷售部     | Geo Positioning System  -- 賣過 GPS
銷售部     | Laptop                  -- 也賣過筆記本
技術部     | Mouse                   -- 只賣過滑鼠
人事部     | (無記錄)                -- 無任何銷售

• 標準解法 (NOT IN)：
  • 子查詢返回：{'銷售部'}
  • 外層查詢：deptname NOT IN ('銷售部')
  • 結果：技術部、人事部 (銷售部被排除)
• 自行嘗試 (!=)：
  • 過濾條件：itemname != 'Geo Positioning System'
  • 匹配行：銷售部-Laptop, 技術部-Mouse
  • DISTINCT 後：銷售部、技術部 (銷售部因賣過筆記本而被包含！)

關鍵洞察

「沒有做過 X」 ≠ 「做過非 X」

• 全稱否定 (NOT IN / NOT EXISTS)：要求該部門在所有銷售記錄中，**零** 條匹配 GPS。
• 存在肯定 (!=)：只要求該部門**至少一條**記錄不是 GPS。

正確的替代寫法

若不想用 NOT IN (注意 NULL 值陷阱)，推薦 NOT EXISTS 或 LEFT JOIN ... IS NULL：

-- 1. NOT EXISTS (標準、安全、性能好)
SELECT deptname
FROM qdept d
WHERE NOT EXISTS (
    SELECT 1 FROM qsale s 
    WHERE s.deptname = d.deptname 
    AND s.itemname = 'Geo Positioning System'
);

-- 2. LEFT JOIN (直觀)
SELECT d.deptname
FROM qdept d
LEFT JOIN qsale s 
    ON d.deptname = s.deptname 
    AND s.itemname = 'Geo Positioning System'
WHERE s.deptname IS NULL;

避坑指南

• NOT IN 子查詢結果集包含 NULL 時，整個條件返回 UNKNOWN (無結果)。永遠在子查詢加 WHERE col IS NOT NULL 或改用 NOT EXISTS。
• != / <> 只能用於「存在非目標值」的場景，不能用於「完全不存在目標值」。
• 關聯子查詢 (NOT EXISTS) 通常比非關聯 NOT IN 執行計劃更優，尤其有索引時。