🔖

SQLの NOT IN と NULL：データが取得できない原因とその回避法

に公開
SQL
初心者向け
備忘録
tech

はじめに

SQLで NOT IN を使って「特定の条件に当てはまらないデータを抽出しよう」としたのに、
なぜかデータが返ってこない…そんな経験はありませんか？

この原因の多くは、NULL の存在によるものです。
NOT IN は便利ですが、NULL が含まれると意図しない結果になる可能性があります。

本記事では、具体的なテーブルを例にとりながら、
NOT IN と NULL の関係、そして安全に使うための方法を紹介します。

1. NOT IN の基本：例で理解する

例えば、users テーブルがあるとします。

-- users テーブル
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
| 3  | Carol    |
| 4  | David    |
+----+----------+

ここで、「id が 2, 3 ではないユーザーを取得したい」ときは以下のように書きます。

SELECT * FROM users
WHERE id NOT IN (2, 3);

結果：

+----+--------+
| id | name   |
+----+--------+
| 1  | Alice  |
| 4  | David  |
+----+--------+

このように、NOT IN を使うと指定した値以外の行を簡単に取得できます。

2. 注意点：NULL が含まれると全て除外される

次に orders テーブルがあるとします。

-- orders テーブル
+----+---------+
| id | user_id |
+----+---------+
| 1  | 2       |
| 2  | NULL    |
| 3  | 3       |
+----+---------+

ここで「orders テーブルに登場していない users を取得したい」と考え、
次のように書いたとします。

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

しかし、結果は0件になります。

🔍 結果が0件になる理由

orders.user_id に NULL が含まれているため、比較できなくなってしまうのが原因です。

SQLでは以下のようなルールがあります。

  1. NULL は「値が不明」なので、比較ができません。

    • たとえば id = NULL や id != NULL のような比較は、どちらも「不明（UNKNOWN）」 という扱いになります。

  2. NOT IN は、中に NULL が1つでもあると、すべての比較が「不明」扱いになります。

    • つまり id NOT IN (2, NULL) のような状態になると、どの id に対しても「比較できない」と判断されてしまいます。

  3. WHERE 句では「不明」は false と同じ扱いになります。

    • そのため、全行が除外されてしまうのです。

💡「1件でも NULL が混じっていたら全部が不明扱いになる」──これが NOT IN の落とし穴です。

3. 回避策：安全に使う方法

✅ 方法1：サブクエリで NULL を除外する

💡 補足： サブクエリとは、SELECT 文の中にさらに別の SELECT を書く「入れ子のクエリ」のことです。
以下の例では、(SELECT user_id FROM orders ...) の部分がサブクエリです。

NULL を取り除いた上で NOT IN を使えば、期待通り動作します。

SELECT * FROM users
WHERE id NOT IN (
  SELECT user_id FROM orders
  WHERE user_id IS NOT NULL
);

結果：

+----+--------+
| id | name   |
+----+--------+
| 1  | Alice  |
| 4  | David  |
+----+--------+

NULL を取り除いたことで、id = 2, 3 だけが除外され、正しく動作します。

✅ 方法2：NOT EXISTS を使う

より安全なのは NOT EXISTS を使う方法です。NULL の影響を受けず、確実に除外できます。

以下のクエリでは、orders テーブルに「同じ user_id を持つ行が存在しない」ユーザーのみを取得しています。

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

💡 補足：

  • users u は users AS u の省略形です。u は テーブルに付けた短縮名（エイリアス） で、後のクエリ内で users を u と書けるようになります。
  • SELECT 1 は「特定の値（ここでは1）を返すだけ」の書き方で、実際に取得する値は重要ではないという意味です。NOT EXISTS は「データが存在するかどうか」を見ているので、SELECT 1 でも SELECT * でも動作には影響ありません。

結果：

+----+--------+
| id | name   |
+----+--------+
| 1  | Alice  |
| 4  | David  |
+----+--------+

4. まとめ

パターン NULL の影響 推奨度
NOT IN ある（注意） △ 注意が必要
NOT IN（NULL除外） 回避可能 ◯ 問題なし
NOT EXISTS ない（安全） ◎ 安心して使える

おわりに

NOT IN を使うときに NULL が紛れ込んでいると、思わぬ全件除外が起こることがあります。
特に実務では、サブクエリ側の値に NULL が混じることは珍しくありません。

意図しない結果を防ぐためにも

  • NULL を除外する
  • NOT EXISTS を使う

といった方法を使って、安全にクエリを書いていきましょう。

本記事が参考になれば幸いです。

🔗 関連リンク

SQLの IS NULL と = NULL の違いを解説

Discussion