Oracle DB で MINUS ALL・INTERSECT ALL を再現する

Oracle DB には MINUS ALL や INERSECT ALL がないので、ちょっと手を加えて再現する。

目次

• MINUS ALL の再現
• INTERSECT ALL の再現

MINUS ALL の再現

通常の MINUS は1つ目の SQL から2つ目の SQL にあるものを除外し、重複行は1行にまとめて返す。UNION と違い前後の SQL を入れ替えると結果が変わる (1+2 と 2+1 は同じ 3 だが、1-2 と 2-1 の結果が違う、ということと同じ)。

MINUS ALL は重複行を除外せずに返す。前後の SQL を入れ替えると結果が変わる点は MINUS と同じ。

• ROW_NUMBER() 関数と集合演算を使う方法

SELECT X
FROM
  SELECT X, ROW_NUMBER() OVER(PARTITION BY X ORDER BY 1) FROM table_1
  MINUS
  SELECT Y, ROW_NUMBER() OVER(PARTITION BY Y ORDER BY 1) FROM table_2
);

• ROW_NUMBER() 関数と相関サブクエリを使う方法

SELECT X
FROM
    (SELECT X, ROW_NUMBER() OVER(PARTITION BY X ORDER BY 1) AS Rank FROM table_1) A
WHERE
    Rank > (SELECT COUNT(*) FROM table_2 B WHERE B.Y = A.X);

INTERSECT ALL の再現

通常の INTERSECT は1つ目と2つ目の SQL に共通で含まれるレコードを抽出し、重複行は1行にまとめて返す。INTERSECT ALL は重複行をまとめずに返す。

• ROW_NUMBER() 関数と集合演算を使う方法

SELECT X
FROM
  SELECT X, ROW_NUMBER() OVER(PARTITION BY X ORDER BY 1) FROM table_1
  INTERSECT
  SELECT Y, ROW_NUMBER() OVER(PARTITION BY Y ORDER BY 1) FROM table_2
);

• ROW_NUMBER() 関数と相関サブクエリを使う方法

SELECT X
FROM
    (SELECT X, ROW_NUMBER() OVER(PARTITION BY X ORDER BY 1) AS Rank FROM table_1) A
WHERE
    Rank <= (SELECT COUNT(*) FROM table_2 B WHERE B.Y = A.X);

以上。いつ使うか分からんけど…。