MySQL Bugs: #1309: EXCEPT statementにもあるように、MySQL 8.
今回は8.
準備
実際に利用する前に、今回検証で利用するデータを準備しましょう。今回利用するデータは第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_
のように記述し、Query_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の後には続けて
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_
のように記述し、Query_
たとえば、INTERSECTと同様に1つのクエリがcolor = 'RED'
、もう1つの結果がscore > 30
の場合ではid = 9が共通するので、Query_
からその行を排除して出力されます。
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の後に
また、INTERSECTやUNIONと違い、EXCEPTは交換可能ではありません。{Query_
とした場合と、{Query_
とした場合とでは結果が変わることに注意する必要があります。
INTERSECTとEXCEPTが存在する場合
INTERSECTはUNIONやEXCEPTよりも先に評価されます。そのため、{Query_
とした場合、これは{Query_
のように評価されます。これを防ぐには、明示的に({Query_
とする必要があります。実際に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を利用する場合に使うクエリブロック
まとめ
今回はバージョン8.