MySQLで(他のDBMSは知らない)ある値のリストxを使ってテーブルからIN(x), NOT IN(x)を使ってデータを取っても2つのクエリで全件を取ってこれないという話は有名だが、MySQL Server Blogにantijoin optimization
も含めた解説記事が出たので、これを機に(is null
, is true
, in()
などとNULLに対する比較演算を整理してみる。
reference: https://mysqlserverteam.com/a-must-know-about-not-in-in-sql-more-antijoin-optimization/
結論
- Nullable(Nullを含む)カラムを
IN(x)
句に指定してデータを取るとき、その補集合はNOT IN(x)
ではなく、IN(x) IS NOT TRUE
- Nullableなカラムを結合条件にしたサブクエリでは上記のように指定しないとantijoin optimizationも効かない
- 多くのDBMSでは純粋な(シノニムでない)BOOLEAN型はサポートしてない(MySQL, Oracle 20c, SQL Server)が、ANSI SQL99には定義されている。PostgreSQLにもある
おさらい
まずは基本的なNULLとの比較やMySQLにおけるbooleanの扱いを整理する.
ここで、T, FはTRUE, FALSEの略で、Tは続く例においてはWHERE句で結果が返ってくるものと同じ
条件(intとの比較) | NULL | 0 | 1 | 2 |
---|---|---|---|---|
IS NULL | T | F | F | F |
IS NOT NULL | F | T | T | T |
IS TRUE | F | F | T | T |
IS NOT TRUE | T | T | F | F |
= TRUE | F | F | T | F |
!= TRUE | F | T | F | T |
一つ一つ説明しないがだいたい以下の点は抑えておく
- WHEREは条件がTRUEの行だけを返す (NULLは含まない)
- NULLとの比較は
IS (NOT) NULL
- ドキュメントをみると
IS
,IS NOT
,IS NULL
,IS NOT NULL
は別の比較演算子 - MySQLにはBoolean型はない
TRUE
,FALSE
(小文字可)は定数(constants)でそれぞれ1
,0
- https://dev.mysql.com/doc/refman/8.0/en/boolean-literals.html
- ANSI SQL-99からは
BOOLEAN
型があるっぽい(原文は見てない)
例 (上記の真理値表を試すクエリ)
mysql> create table nulltest (c1 int null); Query OK, 0 rows affected (0.03 sec) mysql> insert into nulltest (c1) values (null), (0), (1), (2); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from nulltest; +------+ | c1 | +------+ | NULL | | 0 | | 1 | | 2 | +------+ 4 rows in set (0.01 sec) -- 条件式をテスト (sample) mysql> select * from nulltest where c1 is null; +------+ | c1 | +------+ | NULL | +------+ 1 row in set (0.00 sec) ...
これらに対してselect * from nulltest where c1 ...
とすれば実験可能
IN句におけるNULL
条件(intとの比較) | NULL | 0 | 1 | 2 |
---|---|---|---|---|
IN (NULL) | F | F | F | F |
NOT IN (NULL) | F | F | F | F |
IN (0,1) | F | T | T | F |
NOT IN (0,1) | F | F | F | T |
IN (0,1) IS NOT TRUE | T | F | F | T |
NOT IN (0,1) IS NOT TRUE | T | T | T | F |
- NOT IN(x)は
(x)
の中がtrueのものだけを返す - Nullableなテーブルの行すべてを全集合としたとき、
IN(x)
の補集合はIN(x) IS NOT TRUE
antijoin optimizationとNULL
そもそもantijoin optmizationとは
NOT IN(サブクエリ)
, NOT EXISTS(サブクエリ)
, IN(サブクエリ) IS NOT TRUE
, EXISTS(サブクエリ) IS NOT TRUE
の条件を排除して、内部的にantijoinに書き換える最適化
詳細についてはMySQL Server BlogのAntijoin in MySQL 8を参照
antijoin optimizationを確認
以下の2点を簡単に検証してみる
- Not NULLなカラムであればNOT IN(subquery)でantijoin optimizationが効く
- NullableなカラムでもIN(subquery) IS NOT TRUEであればantijoin optimizationが効く