gihyo.jpサイトのロゴ

MySQL道普請便り

第210回MySQLのINTERSECT句とEXCEPT句

2023-11-21

MySQL Bugs: #1309: EXCEPT statementにもあるように、MySQL 8.0.31未満のバージョンでは今までINTERSECT句やEXCEPT句を利用することはできませんでした。そのため、同様の結果を算出するためにJOINやサブクエリを使って抽出する必要がありました。しかし、MySQL 8.0.31でついにINTERSECTとEXCEPT句が追加されました。

今回は8.0.31で追加されたこのINTERSECT句とEXCEPT句について説明したいと思います。なお、今回利用するMySQLのバージョンは8.0.35になります。

準備

実際に利用する前に、今回検証で利用するデータを準備しましょう。今回利用するデータは第204回　ROLLUPを使ってデータを集計してみようで利用したものと同じデータになります。

mysql> CREATE TABLE scores (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(256) NOT NULL, color varchar(30) NOT NULL, score INT NOT NULL);

mysql> INSERT INTO scores VALUES (1, 'AAA', 'RED', 10), (2, 'BBB', 'BLUE', 30), (3, 'CCC', 'RED', 12), (4, 'AAA', 'BLUE', 20), (5, 'BBB', 'RED', 15),(6, 'CCC', 'BLUE', 25),(7, 'AAA', 'YELLOW', 50), (8, 'BBB', 'BLUE', 30), (9, 'CCC', 'RED', 40);

mysql> SELECT * FROM scores;
+----+------+--------+-------+
| id | name | color  | score |
+----+------+--------+-------+
|  1 | AAA  | RED    |    10 |
|  2 | BBB  | BLUE   |    30 |
|  3 | CCC  | RED    |    12 |
|  4 | AAA  | BLUE   |    20 |
|  5 | BBB  | RED    |    15 |
|  6 | CCC  | BLUE   |    25 |
|  7 | AAA  | YELLOW |    50 |
|  8 | BBB  | BLUE   |    30 |
|  9 | CCC  | RED    |    40 |
| 10 | AAA  | NULL   |   100 |
| 11 | BBB  | RED    |    12 |
| 12 | CCC  | NULL   |   200 |
+----+------+--------+-------+
12 rows in set (0.00 sec)

INTERSECT

INTERSECTはクエリの結果から共通する行を表示します。{Query_A} INTERSECT {Query_B} ( INTERSECT …) のように記述し、Query_Aの出力結果とQuery_Bの出力結果から共通する行が出力されます。たとえば、1つのクエリがcolor = 'RED'、もう1つの結果がscore > 30の場合に共通する結果は、id = 9のときの行になります。

mysql> SELECT * FROM scores WHERE color = 'RED';
+----+------+-------+-------+
| id | name | color | score |
+----+------+-------+-------+
|  1 | AAA  | RED   |    10 |
|  3 | CCC  | RED   |    12 |
|  5 | BBB  | RED   |    15 |
|  9 | CCC  | RED   |    40 |
| 11 | BBB  | RED   |    12 |
+----+------+-------+-------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM scores WHERE score > 30;
+----+------+--------+-------+
| id | name | color  | score |
+----+------+--------+-------+
|  7 | AAA  | YELLOW |    50 |
|  9 | CCC  | RED    |    40 |
| 10 | AAA  | NULL   |   100 |
| 12 | CCC  | NULL   |   200 |
+----+------+--------+-------+
4 rows in set (0.00 sec)

実際に2つのクエリをINTERSECTでつなげると、id = 9のデータが出力されることが確認できます。

mysql> SELECT * FROM scores WHERE color = 'RED' INTERSECT SELECT * FROM scores WHERE score > 30;
+----+------+-------+-------+
| id | name | color | score |
+----+------+-------+-------+
|  9 | CCC  | RED   |    40 |
+----+------+-------+-------+
1 row in set (0.01 sec)

INTERSECTは、2つ以上をつなげで利用することも可能です。

mysql> SELECT * FROM scores WHERE color = 'RED' 
INTERSECT SELECT * FROM scores WHERE score > 30 
INTERSECT SELECT * FROM scores WHERE name = 'CCC';
+----+------+-------+-------+
| id | name | color | score |
+----+------+-------+-------+
|  9 | CCC  | RED   |    40 |
+----+------+-------+-------+
1 row in set (0.01 sec)

また、INTERSECTの後には続けて［ALL | DISTINCT］を記述することができ、DISTINCTの場合は重複行が排除されます。どちらの記述もない場合はDISTINCTが選択されます。

mysql> SELECT name, color FROM scores WHERE color = 'RED' INTERSECT SELECT name, color FROM scores WHERE color = 'RED';
+------+-------+
| name | color |
+------+-------+
| AAA  | RED   |
| CCC  | RED   |
| BBB  | RED   |
+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT name, color FROM scores WHERE color = 'RED' INTERSECT ALL SELECT name, color FROM scores WHERE color = 'RED';
+------+-------+
| name | color |
+------+-------+
| AAA  | RED   |
| CCC  | RED   |
| CCC  | RED   |
| BBB  | RED   |
| BBB  | RED   |
+------+-------+
5 rows in set (0.00 sec)

INTERSECTはそれぞれのクエリで出力するカラムが共通している必要があります。そのため、出力するカラムの数が違う場合はエラーとなります。

mysql> SELECT name, color FROM scores WHERE color = 'RED' INTERSECT ALL SELECT id, name, color FROM scores
WHERE color = 'RED';
ERROR 1222 (21000): The used SELECT statements have a different number of columns

EXCEPT

EXCEPTはクエリの結果から共通する行を排除します。{Query_A} EXCEPT {Query_B} ( EXCEPT …) のように記述し、Query_Aの出力結果からQuery_Bの出力結果と共通する行を排除して出力されます。

たとえば、INTERSECTと同様に1つのクエリがcolor = 'RED'、もう1つの結果がscore > 30の場合ではid = 9が共通するので、Query_Aからその行を排除して出力されます。

mysql> SELECT * FROM scores WHERE color = 'RED' EXCEPT SELECT * FROM scores WHERE score > 30;
+----+------+-------+-------+
| id | name | color | score |
+----+------+-------+-------+
|  1 | AAA  | RED   |    10 |
|  3 | CCC  | RED   |    12 |
|  5 | BBB  | RED   |    15 |
| 11 | BBB  | RED   |    12 |
+----+------+-------+-------+
4 rows in set (0.00 sec)

INTERSECTと同様にEXCEPTの後に［ALL | DISTINCT］を付けて重複行を制御することができます。デフォルト（ALLまたはDISTINCTの記述がない場合）はDISTINCTが選択されます。

また、INTERSECTやUNIONと違い、EXCEPTは交換可能ではありません。{Query_A} EXCEPT {Query_B}とした場合と、{Query_B} EXCEPT {Query_A}とした場合とでは結果が変わることに注意する必要があります。

INTERSECTとEXCEPTが存在する場合

INTERSECTはUNIONやEXCEPTよりも先に評価されます。そのため、{Query_A} EXCEPT {QUERY_B} INTERSECT {QUERY_C}とした場合、これは{Query_A} EXCEPT ({QUERY_B} INTERSECT {QUERY_C})のように評価されます。これを防ぐには、明示的に({Query_A} EXCEPT {QUERY_B}) INTERSECT {QUERY_C}とする必要があります。実際にEXPLAINを使って実行計画を出力することで、評価する順番を確認することができます。

mysql> explain SELECT * FROM scores s1 WHERE color = 'RED' EXCEPT SELECT * FROM scores s2 WHERE score > 30 INTERSECT SELECT * FROM scores s3 WHERE color = 'RED';
+----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type      | table          | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY          | s1             | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where     |
|  2 | PRIMARY          | s2             | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    33.33 | Using where     |
|  3 | INTERSECT        | s3             | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where     |
|  4 | INTERSECT RESULT | <intersect2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
|  5 | EXCEPT RESULT    | <except1,4>    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
5 rows in set, 1 warning (0.00 sec)

mysql> explain (SELECT * FROM scores s1 WHERE color = 'RED' EXCEPT SELECT * FROM scores s2 WHERE score > 30) INTERSECT SELECT * FROM scores s3 WHERE color = 'RED';
+----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type      | table          | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY          | s1             | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where     |
|  2 | EXCEPT           | s2             | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    33.33 | Using where     |
|  4 | EXCEPT RESULT    | <except1,2>    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
|  3 | INTERSECT        | s3             | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where     |
|  5 | INTERSECT RESULT | <intersect4,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
5 rows in set, 1 warning (0.01 sec)

JOINやサブクエリとどっちが早い？

出力結果だけを考えた場合、同じことはJOINやサブクエリを利用することで抽出することは可能です。ではどちらのクエリを利用した方が早いか？ですが、これは筆者が検証した感じだと、クエリによると考えます。

INTERSECTやEXCEPTを利用する場合に使うクエリブロック（Query_A,Bの部分）が適切なクエリで十分に件数が絞り込めていれば素早く結果を返すことが確認できました。しかし、クエリブロックそのもののクエリが遅かったり、クエリブロックの出力結果が大きい場合はINDEXを使ったJOINやサブクエリを使って記述したほうが早い結果となりました。

まとめ

今回はバージョン8.0.31で追加されたINTERSECTとEXCEPT句について簡単に説明しました。「⁠JOINやサブクエリとどっちが早い？」の項で説明した通り、出力結果だけを考えれば巨大な出力結果の場合はJOINやサブクエリを利用したほうが早いことがありますが、複数の出力結果の演算をするという意味では、可読性やクエリのメンテナンス性を考えて、INTERSECTやEXCEPT句を利用する場面もあるかしれません。みなさんもぜひ公式ドキュメントを参考に利用してみてください。

おすすめ記事

記事・ニュース一覧