NOT IN を使う時は NULL に特に注意！

NOT IN を使う時は NULL に特に注意！

以前、「NULL の取り扱い方 」 で、カラムの値が NULL の時の取り扱いの注意点について書きました。

今回は NOT IN で条件を指定する際には、特に NULL には注意が必要です、というお話です。

ANSI_NULLS は ON の前提です。 ANSI_NULLS オプションについては、「NULL の取り扱い方 」 をご覧ください。

NOT IN で指定したサブクエリーに NULL が存在すると。。。

NOT IN で指定したリストやサブクエリーの値に NULL が存在すると、常に空の結果セットが返ってきてしまいます。

実際に例を見てみましょう。

次のような Student テーブルと TestResult テーブルがあります。

TestResult テーブルに存在していない Student のレコードを取得したいとします。

その時に次のようにクエリーを書いてしまうと、空の結果セットが返ってきて期待通りの結果にはなりません。

SELECT	*
FROM	Student
WHERE	StudentID NOT IN 
		(SELECT StudentID 
		 FROM	TestResult);

[実行結果]

理由は NOT IN は、リストやサブクエリーで指定した全ての値との StudentID <> [値] が TRUE となるレコードのみを返すのですが、StudentID <> NULL は常に FALSE になるからです。

ですので、NOT IN で指定するカラムに NULL が入る可能性がある時は、次のように WHERE 句で IS NOT NULL を指定する必要があります。

SELECT	*
FROM	Student
WHERE	StudentID NOT IN 
		(SELECT StudentID 
		 FROM	TestResult
		 WHERE	StudentID IS NOT NULL);

[実行結果]

私は、NOT IN も使いますが、NULL を気にしなくて良い、必要であれば複数のカラムで条件を指定することができる、パフォーマンスが NOT IN と同じか良くなることが多い、などの理由で NOT IN の代わりに NOT EXISTS を使うことのほうが多いです。

SELECT	*
FROM	Student AS S
WHERE	NOT EXISTS
		(SELECT * 
		 FROM	TestResult AS TR
		 WHERE	TR.StudentID = S.StudentID);

[実行結果]