【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`の方が先に実行される

例えば、以下の場合

```sql
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`) は標準で入っている
* 商は標準化されていない

# 集合の相等性

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

```sql
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`を使用して重複行を削除し、行数を確認することで同一かの確認を行います

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

```
 row_count
-----------
         3
```

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

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

```
 row_count
-----------
         6
```

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

![スクリーンショット 2023-01-07 11.14.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/536974/73a5f431-a5c4-b2b1-cd30-5ac99683be4c.png)


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

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

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

```sql
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で使用できない

# 参考

https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-CodeZine-BOOKS/dp/4798157821