Oracle 多欄子查詢實戰：以「各類商品最低價」為例解析 IN (col1, col2) 語法

Oracle 子查詢不限於單欄回傳，**多欄子查詢** 允許在 WHERE 子句中同時比對多個欄位，語法為 WHERE (col1, col2) IN (SELECT col1, col2 FROM ...)。本文以「查詢各類商品中價格最低的商品」為例，逐步拆解從單欄聚合到多欄比對的完整思路，並對比 EXISTS、JOIN 等替代寫法效能。

業務場景

表 PRODUCTS (PRODUCT_ID, PRODUCT_TYPE_ID, NAME, PRICE)。需求：找出每個 PRODUCT_TYPE_ID 下 PRICE 最低的商品 (若同價並列全顯示)。

步驟 1：單欄聚合——找出各類別最低價

SELECT product_type_id, MIN(price) AS min_price
FROM   products
GROUP  BY product_type_id;

結果：

PRODUCT_TYPE_ID  MIN_PRICE
---------------  ---------
1                19.95
2                13.95
3                12.99
4                10.99

步驟 2：多欄子查詢——主查詢比對 (type_id, price) 元組

SELECT product_id, product_type_id, name, price
FROM   products
WHERE  (product_type_id, price) IN (
         SELECT product_type_id, MIN(price)
         FROM   products
         GROUP  BY product_type_id
       );

關鍵語法： (col1, col2) IN (SELECT col1, col2 ...) —— 括號不可省略，欄位順序、數量、型別必須與子查詢 SELECT 列完全一致。

結果

PRODUCT_ID  PRODUCT_TYPE_ID  NAME                  PRICE
----------  ---------------  --------------------  -----
1           1                Modern Science        19.95
4           2                Tank War              13.95
8           3                From Another Planet   12.99
9           4                Classical Music       10.99

替代寫法對比

SELECT p1.*
FROM products p1
WHERE EXISTS (
  SELECT 1 FROM products p2
  WHERE p2.product_type_id = p1.product_type_id
  GROUP BY p2.product_type_id
  HAVING MIN(p2.price) = p1.price
);

SELECT * FROM (
  SELECT p.*, ROW_NUMBER() OVER (PARTITION BY product_type_id ORDER BY price) rn
  FROM products p
) WHERE rn = 1;

SELECT p.*
FROM products p
JOIN (
  SELECT product_type_id, MIN(price) min_price
  FROM products GROUP BY product_type_id
) m ON p.product_type_id = m.product_type_id AND p.price = m.min_price;

效能建議

1. 首選分析函數：ROW_NUMBER/RANK 寫法最簡潔且執行計畫穩定 (WINDOW SORT + FILTER)。
2. 多欄 IN 適用中小表：資料量 < 10 萬行時優化器常能轉為 HASH JOIN RIGHT SEMI，效能足夠。
3. 大表/高頻查詢：建立物化視圖或摘要表預先聚合，查詢時直接 JOIN。
4. 索引策略：CREATE INDEX idx_products_type_price ON products(product_type_id, price); 可服務所有上述寫法。

擴充：多欄 NOT IN 陷阱與 NOT EXISTS 改寫

-- 危險：若子查詢回傳含 NULL，NOT IN 永遠回傳 FALSE (三值邏輯)
WHERE (type_id, price) NOT IN (SELECT type_id, price FROM ...);

-- 安全：改用 NOT EXISTS
WHERE NOT EXISTS (
  SELECT 1 FROM ... 
  WHERE type_id = p.type_id AND price = p.price
);

總結

多欄子查詢 (col1, col2) IN (...) 是表達「元組比對」的標準 SQL 語法，語意清晰、可讀性強。實務上，配合分析函數 ROW_NUMBER 可獲得更優效能與擴充性，建議根據資料量、版本、團隊習慣靈活選擇。