MySQL の IN / NOT IN

ここでは MySQL の IN と NOT IN 演算子についてご説明します。

サンプルデータベースのテーブルを使いますので、実際にスクリプトを実行してみたい方は、こちらのスクリプトを実行して school_db データベースを作成しておいてください。

MySQL の IN 演算子

MySQL の IN 演算子は次のような構文で使用し、[A] がカッコ内の要素のいずれかに等しければ True を返す演算子です。

[A] IN ([B], [C], ..)

意味的には [A] = [B] OR [A] = [C] OR ... と同じです。

括弧の中にサブクエリーを指定することもできます。その時は結果セットがひとつのカラムを返すようにしなければなりません。

IN 演算子を使ってテーブルからデータを取得してみましょう。

次のような students テーブルがあります。

この students テーブルから student_id が 1, 3, 5 の学生のデータのみを取得したい時は次のようにできます。

SELECT 	*
FROM 	students
WHERE 	student_id IN (1, 3, 5);

[実行結果]

student_id が 1, 3, 5 の学生のデータのみが取得できていますね。

続いて、IN 演算子の括弧の中にサブクエリーを指定してみましょう。

次のような exam_results テーブルがあります。

性別が男性 (gender = 'M') の student_id の exam_results テーブルのレコードのみを取得したいとします。

そんな時は、まだ習っていない JOIN （結合）を使うことが多いですが、IN 演算子とサブクエリーでもデータを取得することができます。

性別が男性の student_id の exam_results テーブルのレコードを取得するクエリーは次の通りです。

SELECT 	*
FROM 	exam_results
WHERE 	student_id IN 
		(SELECT student_id
         FROM 	students
         WHERE 	gender = 'M');

[実行結果]

男性の学生の student_id は 1, 4, 6 なので、exam_results にレコードのある student_id が 1 と 4 のレコードが取得できました。

MySQL の NOT IN 演算子は次のような構文で使用し、[A] がカッコ内の要素のどれにも等しくなければ True を返す演算子です。

[A] NOT IN ([B], [C], ..)

意味的には [A] <> [B] AND [A] <> [C] AND ... と同じです。

IN と同様に括弧の中にサブクエリーを指定することもできます。その時は結果セットがひとつのカラムを返すようにしなければなりません。

NOT IN 演算子を使ってテーブルからデータを取得してみましょう。

先ほどとは反対に students テーブルから student_id が 1, 3, 5 以外の学生のデータのみを取得したい時は次のようにできます。

SELECT 	*
FROM 	students
WHERE 	student_id NOT IN (1, 3, 5);

[実行結果]

student_id が 1, 3, 5 以外の学生のデータのみが取得できていますね。

先ほどとは反対に性別が男性以外の student_id の exam_results テーブルのレコードを取得するクエリーは次の通りです。

SELECT 	*
FROM 	exam_results
WHERE 	student_id NOT IN 
		(SELECT student_id
         FROM 	students
         WHERE 	gender = 'M');

[実行結果]

男性ではない学生の student_id は 2, 3, 5 なので、exam_results にレコードのある student_id が 2, 3, 5 のレコードが取得できています。

NOT IN 演算子を使う時は、括弧の中の値にひとつでも NULL が存在すると、NOT IN の条件式が True にならないので注意が必要です。

具体的な例をあげてご説明します。

例えば、exam_results テーブルに student_id が存在しない students テーブルのデータ取得したいとします。

ぱっと考えると以下のクエリーで取得できそうな気がしてしまいます。

SELECT	*
FROM	students
WHERE	student_id NOT IN 
		(SELECT student_id 
		 FROM	exam_results);

ですが、このクエリーを実行してみると、取得されたレコード数は 0 です。

これは exam_results に以下のように student_id が NULL のレコードが存在しているためです。

そんな時は、サブクエリーの WHERE 句で student_id が NULL 以外のレコードを指定するか、後ほど別のページで詳しくご説明しますが NOT EXISTS や LEFT JOIN を使っても取得できます。

SELECT	*
FROM	students
WHERE	student_id NOT IN 
		(SELECT student_id 
		 FROM	exam_results
         WHERE  student_id IS NOT NULL);

「MySQL の NOT EXISTS の使い方」はこちらをご覧ください。

NOT IN を使う時は括弧中の値に NULL が入らないか、気をつけてお使いくださいね。

以上、MySQL の IN と NOT IN 演算子についてご説明しました。