SQL コンピュータ

【MySQL】not inを使う場合はNULLに気を付けよう

2022年11月21日

ほかのRDBMSも同様だと思うがMySQLで検証してる時に発生したのでメモ

not inを使うとデータ取得できない場合がある

SQLのWhere句としてAA not in (select …）と使う場合があります。
通常であれば、データ取得することができるのだが、IN句内の値にNULLがあるとデータ取得ができなくなります。

検証

準備

docker環境でMySQL8.0.23を準備し、下記のようなテーブルとデータを用意しました。

create table test1(id int,name1 varchar(255));
create table test2(id int,name2 varchar(255));

insert into test1 value (1,'テスト１＿１');
insert into test1 value (2,'テスト１＿２');
insert into test1 value (3,'テスト１＿３');
insert into test1 value (null,'テスト２＿null');
insert into test1 value (4,'テスト１＿４');
insert into test2 value (1,'テスト２＿１');
insert into test2 value (2,'テスト２＿２');
insert into test2 value (3,'テスト２＿３');
insert into test2 value (null,'テスト２＿null');

各テーブルを取得すると下記のように取得できます。

idname1
1テスト１＿１
2テスト１＿２
3テスト１＿３
(null)テスト２＿null
4テスト１＿４
select * from test1;

idname2
1テスト２＿１
2テスト２＿２
3テスト２＿３
(null)テスト２＿null
select * from test2;

検証１（in句の副問い合わせ）

IN句の副問い合わせでtest2テーブルにデータを取得します。

select * from test1 where id  in(select id from test2 );
idname1
1テスト１＿１
2テスト１＿２
3テスト１＿３
検証１結果

データ取得可能ですが、「id：NULL」のデータは取得できませんでした。

検証２（not in句の副問い合わせ）

検証１のin句をnot in句に変更して実行します。

select * from test1 where id  not in(select id from test2 );
idname1
検証結果２

本来ならば「id = 4」のデータが取得できるはずなのに、すべて取得できない形になりました。

検証３（not in句の副問い合わせ２）

検証２の副問い合わせ内で検索条件：id is not nullを追加します。

select * from test1 where id  not in(select id from test2 where id is not null );
idname1
4テスト１＿４
検証結果３

NULL以外を検索条件に付与すると取得できるようです。

原因

MySQL上においてNULLはunknownなデータとして扱われます。
そしてMySQL上ではNULLで評価した場合は「＝」でも「＜＞」でも「＜」でも「＞」でもNULLとして扱います。
MySQL：3.3.4.6 NULL 値の操作

SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

そして、IN句では()の中身を1つずつ評価していく形になります。

select * from test1 where id in(select id from test2);
↓イメージとして下記と同じ
select * from test1 where id in (1,2,3,null);
↓イメージとして下記と同じ
select * from test1 where id = 1 or id = 2 or id = 3 or id = null;

「id = null」の評価結果は「NULL（不明）」となり、検索条件としてヒットできなくなります。
そのため、「id = 1 or id = 2 or id = 3」のデータ分のみ取得できる状態になります。

次にNOT IN句の場合ですと

select * from test1 where id not in(select id from test2);
↓イメージとして下記と同じ
select * from test1 where id not in (1,2,3,null);
↓イメージとして下記と同じ
select * from test1 where id <> 1 and id <> 2 and id <> 3 and id <> null;

という評価になります。
各条件がAND条件となっているおり、「id <> NULL」の評価結果は「NULL（不明）」となります。
そのため、検索結果自体が「1以外 and 2以外 and 3以外 and NULL（不明）」となるため、データ取得ができなくなります。

そのため、NOT IN句でデータ取得する場合はIN句よりも注意しないといけないです。

NULLデータを評価する場合はEXISTSを使う

NOT IN句は条件に当てはまるデータを抽出して比較するに対し、NOT EXISTSはデータの存在結果に対してTRUE・FALSEを返すという評価方式が異なります。
データ存在に対して評価を行うEXISTSはNULLデータの取り扱いも可能となるわけです。

select * from test1 where not exists (select 'x' from test2 where test1.id = test2.id);
idname1
(null)テスト２＿null
4テスト１＿４
NOT EXISTSの結果

評価方式が異なるので積極的にEXISTSを使うべきというわけでもないのですが、NULLデータが入る可能性のあるカラムでNOT INは使わない方がいいです。

補足

EXISTSでNULLデータも抽出条件を行いたい場合は、「or xxx is null」条件を追加すれば取得できます。

select * from test1 where exists (select 'x' from test2 where test1.id = test2.id or test1.id is null);
idname1
1テスト１＿１
2テスト１＿２
3テスト１＿３
(null)テスト２＿null
EXISTSにis null条件追加した場合

雑感

IN句とEXISTS句は評価方法が違うので一概には言えないのですが、カラムにNULLデータを許容しているのならば極力EXISTSを使った方が不具合リスクは減らせれるのでいいのではと思ってます。（ORACLEでは1000件までしか使えなかったりしてるせいでもありますが。）
テーブルカラムの型やNULL制約を見てSQL分を作るようにしていきましょう。

-SQL, コンピュータ
-, , ,