MySQL の IN 句における NULL の扱いについて

技術メモ MySQL

MySQL の IN 句における NULL の扱いがよく分からなかったので
調査してみた時のメモ

まずは基本

  • 値が IN 句に含まれる場合は 1 を返す
mysql> SELECT 5 IN (1, 2, 3, 4, 5);
+----------------------+
| 5 IN (1, 2, 3, 4, 5) |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
  • 値が IN 句に含まれない場合は 0 を返す
mysql> SELECT 6 IN (1, 2, 3, 4, 5);
+----------------------+
| 6 IN (1, 2, 3, 4, 5) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

NULL 利用

  • 値が IN 句に含まれる場合は 1 を返す
mysql> SELECT 5 IN (1, 2, 3, 4, 5, NULL);
+----------------------------+
| 5 IN (1, 2, 3, 4, 5, NULL) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)
  • 値が NULL を含んだ IN 句に含まれない場合は NULL を返す
mysql> SELECT 6 IN (1, 2, 3, 4, 5, NULL);
+----------------------------+
| 6 IN (1, 2, 3, 4, 5, NULL) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)
  • NULL が IN 句に含まれない場合は NULL を返す
mysql> SELECT NULL IN (1, 2, 3, 4, 5);
+-------------------------+
| NULL IN (1, 2, 3, 4, 5) |
+-------------------------+
|                    NULL |
+-------------------------+
1 row in set (0.00 sec)
  • NULL が IN 句に含まれる場合は NULL を返す
mysql> SELECT NULL IN (1, 2, 3, 4, 5, NULL);
+-------------------------------+
| NULL IN (1, 2, 3, 4, 5, NULL) |
+-------------------------------+
|                          NULL |
+-------------------------------+
1 row in set (0.00 sec)

ダメだ。意味が分からない。。


なお、リファレンスにはこう書いてあった

expr が、IN リストのどれかの値と等しい場合は 1 を戻し、それ以外では 0 を戻します。すべての値が定数の場合、expr のタイプに基づいて評価し、分類します。その際の項目の検索は、バイナリ検索を使って行われます。これはつまり、IN は、IN 値のリストがすべて定数で構成されている場合、非常に速いということです。もしくは、項11.1.2. 「式評価でのタイプ変換」 にあるルールによってタイプ変換が実施されますが、すべての引数に適用されます。

mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

引用符で括られた値 ( ストリングなど ) と括られていない値 ( 数字など ) の比較ルールは異なるため、IN リストの引用符で括られた値と、括られていない値を決して混同しないでください。タイプの混同は、上記の理由により、結果の矛盾の原因になることがあります。例えば、IN 式を次のようには書かないでください :

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

正しい書き方はこのようになります :

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

IN リストの値の数は、max_allowed_packet 値によってのみ制限されます。

SQL の標準に準拠するため、IN は、左側の式が NULL である場合だけでなく、リストに一致するものがない場合、また、リストの式のひとつが NULL である場合にも、NULL を戻します。

IN() 構文は、ある種の副問い合わせを書くのにも使用できます。項12.2.8.3. 「ANY、IN、そして SOME を持つサブクエリ」 を参照してください。

http://dev.mysql.com/doc/refman/5.1/ja/comparison-operators.html#operator_in