星期五, 8月 03, 2018

[SQL] 利用 UNION ALL 取代 OR 條件

在調校這篇 [SQL] WHERE 條件合理性 時,其實一開始的想法是要用 UNION ALL 來把兩個條件拆開,只不過後來從參數值發現是個烏龍,筆記一下這個未採用作法,其實也是會有所改善

原語法
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  LEFT JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE S.ORDER_NO = @P1
  OR i.odno = @P2
[SQL] 使用 UNION ALL  來取代 OR 條件-1
UNION ALL 取代 OR 條件
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  LEFT JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE S.ORDER_NO = @P1
UNION ALL
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE i.odno = @P2

[SQL] 使用 UNION ALL  來取代 OR 條件-2

執行計畫成本比較

[SQL] 使用 UNION ALL  來取代 OR 條件-3

沒有留言:

張貼留言