tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

NOT IN (Subquery)などにおけるNULL

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

一つ一つ説明しないがだいたい以下の点は抑えておく

例 (上記の真理値表を試すクエリ)

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が効く

NOT NULLなカラムに対するNOT IN

gist.github.com

Nullableなカラムに対するNOT IN

gist.github.com