2022年10月にリリースされた MySQL 8.0.31 からSQL標準のINTERSECT, EXCEPTが使用可能になりました。
今回はこの新機能についてご紹介したいと思います。
環境
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つの集合の共通部分を取得する集合演算です。
懐かしきベン図で表すと、下記のように２つのグループが重なる部分に該当します。
SQL標準でありOracle・PostgreSQLなどでは実装されている機能だったのですが、MySQLでは長らく実装されていませんでした。
代替手段としてINNER JOINやEXISTS + 相関サブクエリを使って同様の結果を取得する必要がありましたが、これからは楽させてもらえますね🍵
EXCEPT
EXCEPTとは日本語でいうと差集合と呼ばれます。
ある集合から別の集合を引いて取得する集合演算です。
ベン図で表すと、下記のように片方の集合から重複する部分を引いた部分に該当します。
こちらもSQL標準でありOracle・PostgreSQLなどでは実装されている機能だったのですが、MySQLでは同じく実装されていませんでした。
INTERSECT・EXCEPTの使い方
では、実際にINTERSECT・EXCEPTを使って理解を深めていきましょう。
今回は検証用に下記のテーブルを用意します。
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のテーブルには、それぞれ次のようなテストデータを投入します。
INTERSECT
まずはINTERSECTを使用してみましょう。
構文についてはMySQLの公式ドキュメントを参照するのが確実です（日本語版早く来て）。
基本的にはUNION同様、複数のSELECTの間にINTERSECTを書けばOKです。
注意点として、UNION同様それぞれのSELECTの結果の列数＋型が一致している必要があります。
mysql> SELECT * FROM A
INTERSECT
SELECT * FROM B;
+------+-------+
| name | value |
+------+-------+
| aaa | 111 |
| bbb | 333 |
| ccc | 111 |
+------+-------+
この取得結果をみると、A, B両方で全てのカラムの値が同じ行（すなわち積集合）が取得できていることがわかります。
簡単ですね！！
INTERSECT ALL
勘のいい方はお気づきかと思いますが（君のような勘のいいガキはゲフンゲフン）、よく見るとAにbbb, 333の組み合わせは2行あるのですが、取得結果は1行になっています。
実はINTERSECTはUNIONと同様にALLとDISTINCTを指定することができ、省略した場合はDISTINCTが設定されて重複行が削除されます。
もし重複行をそのままにしたい場合はINTERSECT ALLを使えばOKです。
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も使っていきましょう。
mysql> SELECT * FROM A
EXCEPT
SELECT * FROM B;
+------+-------+
| name | value |
+------+-------+
| aaa | 222 |
| bbb | 444 |
+------+-------+
取得結果をみると、AからBに存在するレコードを差し引いた差集合が取得できていることがわかりますね。
ちなみに、EXCEPTはAとBの順番を入れ替えると結果が変わるのでそこはご注意を（A-B != B-Aってことですね）。
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で結果を取得しましょう。
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