3. minus allとintersect allを模倣
次は、minus allとintersect allを模倣するSQLです。
完結編の「2つのテーブルが相等かを判定するクエリ」では、下記のselect文で、重複行を許さない集合（空集合を考慮せず）の一致を調べました。
select count(*) over(),a.* from tbl_A a minus select count(*) over(),b.* from tbl_B b
しかし、下記のように重複行を許す場合は、等しい集合でなくても結果が0件となってしまいます。
select count(*) over(),a.*
from (select 1 as ColA from dual union all
select 1 from dual union all
select 2 from dual union all
select 2 from dual union all
select 3 from dual) a
minus
select count(*) over(),b.*
from (select 1 as ColA from dual union all
select 2 from dual union all
select 2 from dual union all
select 3 from dual union all
select 3 from dual) b;
重複行を許す集合に対応するには、minusではなくminus allを使わなければいけないのです。しかし、Oracle11gの段階でminus allは実装されていません。そこで、minus allを模倣するSQLと、intersect allを模倣するSQLを考えてみます。
3-1 minus allを模倣
まずは、minus allを模倣するSQLです。テーブルのデータと、出力結果を考えます。
| ColA |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| ColA |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
下記のminus allを模倣します。
select ColA from tbl_A minus all select ColA from tbl_B;
| ColA |
| 1 |
minus allを実装する上でのポイントは、重複する要素があったときに要素の数を考慮する点です。要素の数を考慮することに注目すると、答えは下記となります。
select ColA
from (select ColA,Row_Number() over(partition by ColA order by 1) from tbl_A
minus
select ColA,Row_Number() over(partition by ColA order by 1) from tbl_B);
Row_Number関数で各要素に連番をつけてからminusを使い、minus allと同じ結果を取得しています。Row_Number関数には、こういった使い方もあるのです。
SQLのイメージは下記となります。赤線がpartition byのイメージで、緑線がminusによる出力しない制御のイメージです。
3-2 intersect allを模倣
次は、intersect allを模倣するSQLです。テーブルのデータと、出力結果を考えます。
| ColA |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| ColA |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
下記のintersect allを模倣します。
select ColA from tbl_A intersect all select ColA from tbl_B;
| ColA |
| 1 |
| 2 |
| 2 |
| 3 |
intersect allを実装する上でのポイントは、重複する要素があったときに要素の数を考慮する点です。要素の数を考慮することに注目すると、答えは下記となります。
select ColA
from (select ColA,Row_Number() over(partition by ColA order by 1) from tbl_A
intersect
select ColA,Row_Number() over(partition by ColA order by 1) from tbl_B);
minus allを模倣する方法と同じ考え方で、Row_Number関数で各要素に連番をつけてからintersectを使い、intersect allと同じ結果を取得しています。
SQLのイメージは、さきほどのminus allと同じで下記となります。赤線がpartition byのイメージで、緑線がintersectによる出力する制御のイメージです。
3-3 「2つのテーブルが相等か」を判定するクエリ（重複行を考慮）
以上により、minus allを模倣するSQLを使う、重複行を考慮した「2つのテーブルが相等か」を判定するクエリは、下記となります。
select count(*) over(),ColA, Row_Number() over(partition by ColA order by 1) from tbl_A minus select count(*) over(),ColA, Row_Number() over(partition by ColA order by 1) from tbl_B;
テーブルが複数列（ColA,ColB,ColC）なら下記となります。
select count(*) over(),ColA,ColB,ColC, Row_Number() over(partition by ColA,ColB,ColC order by 1) from tbl_A minus select count(*) over(),ColA,ColB,ColC, Row_Number() over(partition by ColA,ColB,ColC order by 1) from tbl_B;
