データのフィルタリング IN と NOT IN を使ってみよう

例えば、大学のデータベースで特定のコースを受講している学生を探したいとするよ。たとえば「プログラミング」「数学」「物理学」みたいな感じ。もちろん、こんな感じで長いクエリも書けるけど：

SELECT *
FROM students 
WHERE course = 'プログラミング'
   OR course = '数学'
   OR course = '物理学';

でも正直、こういう書き方はめんどくさいし、あんまりスマートじゃないよね。ラッキーなことに IN っていう便利なオペレーターがあって、同じクエリをもっと短く書けるんだ：

SELECT *
FROM students 
WHERE course IN ('プログラミング', '数学', '物理学');

なんか魔法みたいでしょ？たくさんの OR を書く代わりに、SQLに「このリストの中から探して」って伝えるだけ。逆に、含まれていないものを探したいときは NOT IN を使うよ — このリストにないもの全部探してって感じ。

IN オペレーターの書き方

基本の書き方はこんな感じ：

SELECT カラムたち
FROM テーブル
WHERE カラム IN (値1, 値2, 値3, ...);

じゃあ、いくつか例を見てみよう。

例1: 複数のコースを受講している学生

たとえば、students テーブルがあるとする：

「プログラミング」「数学」「物理学」を受講している学生を探したいときは IN を使う：

SELECT name, course
FROM students
WHERE course IN ('プログラミング', '数学', '物理学');

結果：

見ての通り、IN を使うとめっちゃシンプルになる。長い OR 条件を書かなくていいし、気になる値のリストを渡すだけでOK。

例2: 特定のコースを受講していない学生

今度は「プログラミング」「数学」「物理学」を受講していない学生を探したいとき。ここで NOT IN の出番：

SELECT name, course
FROM students
WHERE course NOT IN ('プログラミング', '数学', '物理学');

結果：

つまり、NOT IN は course カラムの値が指定したリストに含まれていない行を全部返してくれる。

IN と NOT IN をサブクエリで使う

IN と NOT IN は、2つのテーブル間でデータを比較したいときに特に便利。たとえば、こんな2つのテーブルがあるとする：

students テーブル：

courses テーブル：

「courses」テーブルに存在するコースに登録している学生を探したいときは、サブクエリと IN を組み合わせる：

SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
);

このクエリはこう動く：サブクエリ SELECT id FROM courses でコースIDのリストを作って、IN で course_id がその中にあるかチェックする。

結果：

Danが抜けてるのはなぜ？それは彼の course_id (104) が courses テーブルにないからだよ。

NULL の扱いの注意点

IN にはちょっとしたクセがある。値リストの中に NULL があると、クエリの結果に影響することがあるんだ。例を見てみよう。

grades テーブル：

「('A', 'B', 'C') のどれかの成績を持つ学生」を探すクエリはこう：

SELECT student_id
FROM grades
WHERE grade IN ('A', 'B', 'C');

結果：

grade カラムが NULL の行は無視される。なぜなら NULL はどんなリストにも含まれないから。

今度は NOT IN を使ってみる。たとえば：

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C');

「student_id = 2」の行が出てくると思うかもしれないけど、実は結果は空っぽ！なぜかというと、NULL はリストのどの値とも比較できなくて、結果が常に「不明（UNKNOWN）」になるから。これ、ちょっと混乱しやすいので、NOT IN を使うときは NULL の存在に注意しよう。こういう場合は NULL を明示的にチェックするのがベスト：

SELECT student_id
FROM grades
WHERE grade NOT IN ('A', 'B', 'C')
   OR grade IS NULL;

結果：

IN と NOT IN の使い方のコツ

IN でSQLを読みやすくしよう
カラムが特定の値リストに含まれているかチェックしたいときは、OR をたくさん書くより IN を使うのがオススメ。

NOT IN と NULL には注意
データに NULL があると、思わぬ結果になることがある。NOT IN を使うときは NULL を明示的に処理しよう。

サブクエリにはインデックスを使おう
IN をサブクエリと一緒に使うときは、サブクエリ側のカラムにインデックスがあるとパフォーマンスが良くなるよ。

実際の課題例

例えばネットショップのシステムで働いているとしよう。orders と users というテーブルがあって、「一度も注文したことがないユーザー」を探したい。

users テーブル：

orders テーブル：

NOT IN を使って解決しよう：

SELECT name
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

結果：

このクエリはこう動く：まずサブクエリ SELECT user_id FROM orders で注文したユーザーのIDリスト（1と3）を作る。それを NOT IN で除外して、注文したことがないユーザー（MelloとDan）だけを残すってわけ。