【SQL】集合演算の注意点と、相等性の確認

集合演算の注意点

重複行の扱い

UNIONやINTERSECTをそのまま使用すると、重複行が排除される

以下の、samplesテーブルと、sampless2テーブルがあったとして、

postgres=# SELECT * FROM samples;
 col_1 | col_2
-------+-------
     1 |    50
     2 |    51
(2 rows)

postgres=# SELECT * FROM samples2;
 col_1 | col_2
-------+-------
     2 |    51
     3 |   100
     4 |    50
(3 rows)

postgres=# SELECT * FROM samples UNION SELECT * FROM samples2;
 col_1 | col_2
-------+-------
     4 |    50
     2 |    51
     1 |    50
     3 |   100
(4 rows)

UNIONを使用して重複を含まず結合した結果を取得する場合、
2つのテーブルからそれぞれ選択される訳ではなく、重複行が削除された上で1つが選択されます。

ALLオプションを付けることで重複行を残す事が出来る（UNION ALL）

ALLオプションをつけた場合は、重複行は消されることはありません（例は後述）

また、集合演算子は重複削除のために暗黙的にソートが行われます。
そして、ALLオプションを付ける事でソートが行われないのでパフォーマンスが向上します

演算の順番の優先順位

UNIONとEXCEPTに対してINTERSECTの方が先に実行される

例えば、以下の場合

SELECT * FROM sample
UNION 
SELECT * FROM sample2
EXCEPT 
SELECT * FROM sample
INTERSECT
SELECT * FROM sample2;

 col_1 | col_2
-------+-------
     4 |    50
     1 |    50
     3 |   100
(3 rows)

INTERSECTが先に実行されます
先に実行したい場合は、カッコを使用して明示的に演算の順序を指定しなければなりません

除算の標準的な定義はない

• 和(UNION) 差(EXCEPT) 積(CROSS JOIN) は標準で入っている
• 商は標準化されていない

集合の相等性

名前が異なるテーブル同士の中身が等しいことを確認します

CREATE TABLE table_1
 (key  CHAR(1) PRIMARY KEY,
  col   INTEGER);

CREATE TABLE table_2
 (key  CHAR(1) PRIMARY KEY,
  col   INTEGER);

INSERT INTO table_1 VALUES('A', 1);
INSERT INTO table_1 VALUES('B', 2);
INSERT INTO table_1 VALUES('C', 3);

INSERT INTO table_2 VALUES('A', 1);
INSERT INTO table_2 VALUES('B', 2);
INSERT INTO table_2 VALUES('C', 3);

Table1.

 key | col
-----+-----
 A   |   1
 B   |   2
 C   |   3

Table2.

 key | col
-----+-----
 A   |   1
 B   |   2
 C   |   3

UNIONを使用して重複行を削除し、行数を確認することで同一かの確認を行います

SELECT COUNT(*) AS row_count FROM (
    SELECT * FROM table_1 UNION SELECT * FROM table_2
) TMP;

 row_count
-----------
         3

以下でUNION ALLを使用すると、重複行は削除されないことが行数からも確認できます

SELECT COUNT(*) AS row_count FROM (
    SELECT * FROM table_1 UNION ALL SELECT * FROM table_2
) TMP;

 row_count
-----------
         6

集合の和と交差をもとに相等性の確認をする

つまり、A UNION B = A INTERSECT B ということです

これでAとBが互いに等しいことの確認ができます

どんな状況であれ(A INTERSECT B) ⊆ (A UNION B)であることは分かっているので、
(A UNION B) EXCEPT (A INTERSECT B)が空集合になるかを判断すれば良いです
A = B の時に空集合となります

SELECT
    CASE WHEN COUNT(*) = 0
    THEN '等しい'
    ELSE '異なる'
    END AS result
FROM ((SELECT * FROM table_1 UNION SELECT * FROM table_2)
EXCEPT (SELECT * FROM table_1 INTERSECT SELECT * FROM table_2)) TMP;

このクエリのメリットとしては、

• 列名、列数を知る必要が無い
• NULLを含んだテーブルに対しても実行が出来る
• 行数を知るための事前準備は不要

デメリットは、

• 集合演算3回分のソートが発生する
  • そのため、パフォーマンスは落ちる
• INTERSECT と EXCEPTは現時点ではMySQLで使用できない

参考