MySQLにINTERSECT（積集合）, EXCEPT（差集合）が追加されたよ

2022年10月にリリースされた MySQL 8.0.31 からSQL標準の`INTERSECT`, `EXCEPT`が使用可能になりました。

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html#mysqld-8-0-31-sql-syntax

今回はこの新機能についてご紹介したいと思います。

### 環境

```bash
mysql --version
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
```

## そもそもINTERSECT・EXCEPTって何？

`INTERSECT`・`EXCEPT`は複数のテーブルやクエリの結果を合わせて操作する集合演算になります。

同じ集合演算には、かの有名な`UNION`があります。
つまり`UNION`のお友達について紹介する記事、ということです（雑）。

### INTERSECT

`INTERSECT`は日本語でいうと**積集合**・**交差**と呼ばれます。
2つの集合の共通部分を取得する集合演算です。

懐かしきベン図で表すと、下記のように２つのグループが重なる部分に該当します。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/d6a0ba44-f8e2-9408-1fa9-af7b184b794f.png" width=400>

SQL標準でありOracle・PostgreSQLなどでは実装されている機能だったのですが、MySQLでは長らく実装されていませんでした。

代替手段として`INNER JOIN`や`EXISTS` + 相関サブクエリを使って同様の結果を取得する必要がありましたが、これからは楽させてもらえますね🍵

### EXCEPT

`EXCEPT`とは日本語でいうと**差集合**と呼ばれます。
ある集合から別の集合を引いて取得する集合演算です。

ベン図で表すと、下記のように片方の集合から重複する部分を引いた部分に該当します。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/fcd37ff3-9207-3946-ba09-7764b08bb17a.png" width=400>

こちらもSQL標準でありOracle・PostgreSQLなどでは実装されている機能だったのですが、MySQLでは同じく実装されていませんでした。

## INTERSECT・EXCEPTの使い方

では、実際に`INTERSECT`・`EXCEPT`を使って理解を深めていきましょう。
今回は検証用に下記のテーブルを用意します。

```sql
CREATE TABLE A (name VARCHAR(255) NOT NULL, value INT NOT NULL);
CREATE TABLE B (name VARCHAR(255) NOT NULL, value INT NOT NULL);
```

A, Bのテーブルには、それぞれ次のようなテストデータを投入します。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/ed551823-d590-bf66-2b5c-477b39e48939.png
" width="500">

### INTERSECT

まずは`INTERSECT`を使用してみましょう。
構文についてはMySQLの公式ドキュメントを参照するのが確実です（~~日本語版早く来て~~）。

https://dev.mysql.com/doc/refman/8.0/en/intersect.html

基本的には`UNION`同様、複数の`SELECT`の間に`INTERSECT`を書けばOKです。
注意点として、`UNION`同様それぞれの`SELECT`の結果の`列数＋型`が一致している必要があります。

```sql
mysql> SELECT * FROM A
INTERSECT
SELECT * FROM B;

+------+-------+
| name | value |
+------+-------+
| aaa  |   111 |
| bbb  |   333 |
| ccc  |   111 |
+------+-------+
```

この取得結果をみると、A, B両方で全てのカラムの値が同じ行（すなわち積集合）が取得できていることがわかります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/0a3a1a18-7ec1-db2f-4fef-fe06d7084a3c.png
" width="500">

簡単ですね！！

#### INTERSECT ALL

勘のいい方はお気づきかと思いますが（~~君のような勘のいいガキはゲフンゲフン~~）、よく見るとAに`bbb, 333`の組み合わせは2行あるのですが、取得結果は1行になっています。

実は`INTERSECT`は`UNION`と同様に`ALL`と`DISTINCT`を指定することができ、省略した場合は`DISTINCT`が設定されて重複行が削除されます。

もし重複行をそのままにしたい場合は`INTERSECT ALL`を使えばOKです。

```sql
mysql> SELECT * FROM A
INTERSECT ALL
SELECT * FROM B;

+------+-------+
| name | value |
+------+-------+
| aaa  |   111 |
| bbb  |   333 |
| bbb  |   333 |
| ccc  |   111 |
+------+-------+
```

このとき、A, B両方で重複していないレコードは1行しか取得されませんでした
（`ccc, 111`の行はAに2行、Bに1行ですが結果は1行だけ）。

積集合は共通の要素を抽出する演算なので、より少ない方のテーブルの行数に合わせて取得される感じでしょうか。

### EXCEPT

続いて`EXCEPT`も使っていきましょう。

https://dev.mysql.com/doc/refman/8.0/en/except.html

```sql
mysql> SELECT * FROM A
EXCEPT
SELECT * FROM B;

+------+-------+
| name | value |
+------+-------+
| aaa  |   222 |
| bbb  |   444 |
+------+-------+
```

取得結果をみると、AからBに存在するレコードを差し引いた差集合が取得できていることがわかりますね。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/221479/be5076ad-c7ad-d05a-57b1-79373a834d98.png
" width="500">

ちなみに、`EXCEPT`はAとBの順番を入れ替えると結果が変わるのでそこはご注意を（A-B != B-Aってことですね）。

```sql
mysql> SELECT * FROM B
EXCEPT
SELECT * FROM A;

+------+-------+
| name | value |
+------+-------+
| aaa  |   333 |
| bbb  |   222 |
| bbb  |   555 |
| ddd  |   111 |
+------+-------+
```

#### EXCEPT ALL

`INTERSECT`同様、`EXCEPT`も`ALL`, `DISTINCT`を指定することができます。
重複行を削除したくない場合は同じように`EXCEPT ALL`で結果を取得しましょう。

```sql
mysql> SELECT * FROM A
EXCEPT ALL
SELECT * FROM B;

+------+-------+
| name | value |
+------+-------+
| aaa  |   222 |
| aaa  |   222 |
| bbb  |   444 |
| ccc  |   111 |
+------+-------+
```

、、、なんか`ccc, 111`が多くないか？と思ったのは自分だけでしょうか。

`ccc, 111`がAに2行、Bに1行レコードが存在するので、`EXCEPT ALL`の場合は重複行が除外されず`2 - 1`で1行残った、という理解をしています。

## まとめ

というわけで簡単ではありますが `INTERSECT`, `EXCEPT` のご紹介でした。

Release Note を見ると、MySQLも日々進化しているんだなぁと感じる今日この頃です。

オチなし！おしまい！！

## 参考URL

https://www.techscore.com/tech/sql/08_01

https://qiita.com/Hiraku/items/0f3084ae8c3f91e5b59d