NULLを含む重複レコードの取得（Oracle DB 相関副問合せのgroup by）

こんにちは、亀井です。

業務であまりHAVING句を利用しないので、最近相関副問合せでのgroup byの仕様上の動作を知りました。
相関副問合せはサブクエリの中で元のクエリにあるテーブルデータを参照する機能です。今回は重複データの参照について記載しますが、集計等で使うこともあるかと思いますのでまとめておきました。

更新履歴

日付	更新概要
2023/05/13	記事公開

データの準備

以下のようなデータを準備します。
col2とcol3の重複をチェックしますが、NULLデータを含む重複のチェックをしたい時にどうするかがポイントです。

create table TEST(
  col1 number,
  col2 number,
  col3 varchar2(64)
);

begin
  insert into TEST (col1, col2, col3) values (1, 1, '1-1');
  insert into TEST (col1, col2, col3) values (2, 2, '2-1');
  insert into TEST (col1, col2, col3) values (3, 2, '2-2');
  insert into TEST (col1, col2, col3) values (4, 3, '3-1'); -- 重複
  insert into TEST (col1, col2, col3) values (5, 3, '3-1'); -- 重複
  insert into TEST (col1, col2, col3) values (6, 3, '3-1'); -- 重複
  insert into TEST (col1, col2, col3) values (7, 4, NULL); -- 重複
  insert into TEST (col1, col2, col3) values (8, 4, NULL); -- 重複
end;
/

状況に応じたチェック方法

NULLを考慮しないチェック

全て値が存在する(NOT NULL)という前提がある場合、以下のSQLで重複のチェックが出来ます。
col2が3のものが取得できることが確認できます。col2が4のものはNULLなので比較が出来ないため取得できません。

select *
from TEST a1
where exists (
  select 1
  from TEST a2
  where (a2.col2, a2.col3) in (
      (a1.col2, a1.col3)
    )
  group by a2.col2, a2.col3
  having count(1) > 1);

NULLを考慮したチェック

以下でNULLを考慮した取得が出来ます。但し、INDEXが基本的に効かなくなるかと思います。
多量のデータから取得する場合に、INDEXを効かして絞り込めるデータがない場合は使えないかと思います。

select *
from TEST a1
where exists (
  select 1
  from TEST a2
  where (nvl(a2.col2, -1), nvl(a2.col3, '_NULL_')) in (
      (nvl(a1.col2, -1), nvl(a1.col3, '_NULL_'))
    )
  group by a2.col2, a2.col3
  having count(1) > 1);

-1と_NULL_は通常データとして存在しない値を想定して指定しています。

INDEXを考慮したチェック

相関副問合せのgroup byで問題が発生するパターン

以下の文は一見うまくいきそうですが、相関副問合せでは問題が発生します。
何もデータが取得できないことが確認できるかと思います。

select *
from TEST a1
where exists (
  select 1
  from TEST a2
  where a1.col1 = a2.col1
  group by a2.col2, a2.col3
  having count(1) > 1);

Oracle Database Release 19c SQL言語リファレンス
9. SQL問合せおよび副問合せ
副問合せの使用方法
概念的に、相関副問合せは、親である文によって処理される行ごとに1回評価されます。

上記の記載による実装がされているため、全体の検索結果をgroup by をしているわけでなく、親1行毎にSQLが評価されます。
つまり、内部的に以下のような処理を行うため、count(1)は常に1となります。

DECLARE
  l_x  NUMBER;
BEGIN
  FOR rec_parent IN (
    select *
    from TEST a1
  ) LOOP
    select 1
    into l_x
    from TEST a2
    where rec_parent.col1 = a2.col1
    group by a2.col2, a2.col3
    having count(1) > 1;
  END LOOP;
END;

ROWIDを取得した場合の例

解決方法はファンクション・インデックスやPL/SQLで1件ずつデータ比較する等あると思いますが、ROWIDを取得してそのデータを取得する場合の対応例です。

先に理解のためにROWIDをカンマ区切りで取得するSQLを記載します。

select LISTAGG(rowid,',')
from TEST a2
group by a2.col2, a2.col3
having count(1) > 1;

-- Oracle APEXの関数(APEX_STRING)で一回ネスト表に変換した後に取得
select apex_string.join(cast(COLLECT(ROWIDTOCHAR(rowid)) as apex_t_varchar2), ',')
from TEST a2
group by a2.col2, a2.col3
having count(1) > 1;

-- col1での取得例
select apex_string.join(cast(COLLECT(to_char(col1)) as apex_t_varchar2), ',')
from TEST a2
group by a2.col2, a2.col3
having count(1) > 1;

以下apex_t_varchar2型（Oracle APEXをインストールしていると存在するvarchar2の配列です）に変換して、対象rowidを取得します。

select a1.*
from TEST a1
where a1.rowid in (
  select a4.*
  from
    ( select cast(COLLECT(ROWIDTOCHAR(rowid)) as apex_t_varchar2) as rowid_t
      from TEST a2
      group by a2.col2, a2.col3
      having count(1) > 1
    ) a3,
    table(a3.rowid_t) a4
);

タイプを作成して実行することも出来ます。

create type varchar2_array as table of VARCHAR2(20);

select a1.*
from TEST a1
where a1.rowid in (
  select a4.*
  from
    ( select cast(COLLECT(ROWIDTOCHAR(rowid)) as varchar2_array) as rowid_t
      from TEST a2
      group by a2.col2, a2.col3
      having count(1) > 1
    ) a3,
    table(a3.rowid_t) a4
);

マニュアルの例

以下のように相関副問合せを利用するマニュアルの例がありますが、こちらもROWIDを利用するように変換したパターンを記載しておきます。部門ごとの金額の平均以上の人を取得するSQLです。

SELECT department_id, last_name, salary
   FROM employees x
   WHERE salary > (SELECT AVG(salary)
      FROM employees
      WHERE x.department_id = department_id)
   ORDER BY department_id;

変換すると以下のようになります。
INDEXやNULLを考えた場合こちらのやり方のほうが汎用的で性能面で安定するかと思いますが、例の方が分かりやすいので、場合によって使い分けてもいいかと思います（以下のSQLについては動作確認していません）。

select department_id, last_name, salary
from employees a1
where a1.rowid in (
  select a4.*
  from
    ( select cast(COLLECT(ROWIDTOCHAR(rowid)) as apex_t_varchar2) as rowid_t
      from employees a2
      group by a2.department_id
      having AVG(salary) > salary
    ) a3,
    table(a3.rowid_t) a4
);

apex_application_global.VC_ARR2詳細について