Corredor

ウェブ、プログラミングの勉強メモ。

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

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

MINUS ALL の再現

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

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

Oracleの現場を効率化する100の技

Oracleの現場を効率化する100の技

  • 作者: 鈴木健吾,玉置雄大,塩原浩太,小林修,大森慎司,内村友亮
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/05/26
  • メディア: 単行本(ソフトカバー)
  • この商品を含むブログ (2件) を見る

[rakuten:rakutenkobo-ebooks:14541819:detail]