【MySQL】NOT INの使い方・注意点・LEFT JOINでの代替方法

【MySQL】NOT INの使い方・注意点・LEFT JOINでの代替方法を徹底解説

はじめに

データベースを操作する上で、「ある条件を満たすデータ集合に含まれていないデータ」を抽出したいという要件は頻繁に発生します。例えば、「まだ一度も注文したことのない顧客リスト」や、「特定のカテゴリーに属さない商品リスト」などです。このようなデータ抽出を行う際に、SQLの NOT IN 演算子を使うことが一般的です。

NOT IN は直感的で分かりやすい構文を持っているため、多くの開発者が最初に思いつく方法かもしれません。しかし、この NOT IN にはいくつかの重要な注意点があります。特に、NULL値の扱いとパフォーマンスの観点から問題を引き起こす可能性があり、場合によっては意図しない結果になったり、クエリの実行が著しく遅くなったりすることがあります。

この記事では、MySQLにおける NOT IN の基本的な使い方から始まり、その背後にある注意点、特に多くの開発者が経験するNULL値に関する落とし穴とパフォーマンスの問題について詳しく解説します。さらに、これらの問題を回避し、より堅牢でパフォーマンスに優れたクエリを実現するための代替手段として、LEFT JOIN ... IS NULL や NOT EXISTS を使う方法について、具体的な例を交えながら詳細に説明します。

この記事を読むことで、あなたは以下のことを習得できます。

NOT IN の正しい使い方と基本的な構文
NOT IN を使う上で絶対に知っておくべきNULL値の注意点とその理由
NOT IN がパフォーマンスに与える影響
NOT IN の代替となる LEFT JOIN ... IS NULL および NOT EXISTS の使い方
それぞれの方法のメリット・デメリット、そしてどのような状況でどの方法を選択すべきか

それでは、まずは NOT IN の基本的な使い方から見ていきましょう。

`NOT IN` の基本的な使い方

NOT IN 演算子は、あるカラムの値が、指定した値のリストやサブクエリの結果セットに含まれていない行を検索するために使用されます。

構文

NOT IN の基本的な構文は以下の通りです。

sql SELECT column_list FROM table_name WHERE column_name NOT IN (value1, value2, ...);

または、サブクエリを使用する場合：

sql SELECT column_list FROM table_name WHERE column_name NOT IN (SELECT column_name FROM another_table WHERE condition);

値のリストを指定する例

特定の user_id を持つユーザーを除外したい場合を考えます。

サンプルテーブル: users

user_id	user_name
1	Alice
2	Bob
3	Charlie
4	David

user_id が 2 および 4 のユーザー以外のリストを取得するには、以下のように記述します。

sql SELECT user_id, user_name FROM users WHERE user_id NOT IN (2, 4);

実行結果:

user_id	user_name
1	Alice
3	Charlie

この例では、NOT IN の後に括弧で囲まれた固定の値のリストを指定しています。これは非常にシンプルで分かりやすい使い方です。

サブクエリを指定する例

次に、別のテーブルの値に基づいて除外する場合を考えます。
例えば、「まだ一度も注文したことのないユーザー」を抽出したいとします。

サンプルテーブル: users

user_id	user_name
1	Alice
2	Bob
3	Charlie
4	David
5	Eve

サンプルテーブル: orders

order_id	user_id	amount
101	1	5000
102	3	8000
103	1	3000
104	4	12000

注文テーブル (orders) に user_id が存在するユーザーは注文経験があると考えられます。したがって、orders テーブルの user_id リストに含まれていないユーザーを抽出すれば、「まだ注文したことのないユーザー」を取得できます。

sql SELECT user_id, user_name FROM users WHERE user_id NOT IN (SELECT user_id FROM orders);

サブクエリ (SELECT user_id FROM orders) の結果:

user_id
1
3
1
4

重複を排除すると {1, 3, 4} となります。

メインクエリ WHERE user_id NOT IN (1, 3, 4) の実行結果:

user_id	user_name
2	Bob
5	Eve

Bob (user_id 2) と Eve (user_id 5) は orders テーブルに user_id が存在しないため、結果に含まれます。

このように、NOT IN を使うことで、別のテーブルやクエリの結果に基づいて簡単に除外条件を指定できます。しかし、ここからが本題です。この便利な NOT IN には、落とし穴が存在します。

`NOT IN` の注意点

NOT IN を使う上で最も重要な注意点は、NULL値の扱いとパフォーマンスです。これらの問題を理解しないと、意図しない結果を得たり、クエリが極端に遅くなったりする可能性があります。

1. NULL値の問題：`NOT IN (..., NULL, ...)` は常に空の結果セットを返す可能性がある

これは NOT IN を使う上で最も一般的で、かつ破壊的な問題です。NOT IN の比較対象リスト（括弧の中）に一つでも NULL 値が含まれている場合、NOT IN を含む WHERE 句の条件は常に Unknown (または NULL) と評価され、結果として一致する行が一つも見つからなくなる可能性があります。

なぜそうなるのでしょうか？ SQLにおける比較演算子とNULL値の振る舞いを理解する必要があります。

SQLでは、NULLは「不明な値」を表します。したがって、NULLを含む比較（例: value = NULL, value != NULL, value > NULL など）は、原則として真でも偽でもなく、「Unknown」と評価されます。WHERE句は、条件が真と評価された行のみを結果セットに含めます。Unknown と評価された行は結果セットに含まれません。

IN 演算子は、実際には一連のOR条件に展開されて評価されると考えられます。
column_name IN (value1, value2, value3) は論理的に column_name = value1 OR column_name = value2 OR column_name = value3 と等価です。

同様に、NOT IN 演算子は一連のAND条件と否定に展開されると考えられます。
column_name NOT IN (value1, value2, value3) は論理的に column_name != value1 AND column_name != value2 AND column_name != value3 と等価です。

ここで、NOT IN のリストに NULL が含まれている場合を考えます。
例えば、column_name NOT IN (value1, value2, NULL) は論理的に column_name != value1 AND column_name != value2 AND column_name != NULL と等価になります。

ここで問題となるのが最後の条件 column_name != NULL です。先述の通り、SQLにおいて != NULL (や = NULL) の比較は常に Unknown と評価されます。
したがって、column_name != value1 AND column_name != value2 AND Unknown という条件になります。

論理演算のルールとして、True AND Unknown は Unknown、False AND Unknown は False、Unknown AND Unknown は Unknown となります。つまり、どれか一つの条件でも Unknown となると、全体の条件が真（True）と評価されることはありません。結果として、WHERE句の条件が常に真とならないため、どの行も結果セットに含まれず、クエリの結果が空になってしまうのです。

具体的な例：

先ほどの「まだ注文したことのないユーザー」を抽出する例で、もし orders テーブルの user_id カラムに NULL 値が含まれている場合を考えます。

サンプルテーブル: users (変更なし)

user_id	user_name
1	Alice
2	Bob
3	Charlie
4	David
5	Eve

サンプルテーブル: orders (NULL値が含まれる場合)

order_id	user_id	amount
101	1	5000
102	3	8000
103	NULL	3000
104	4	12000

ここで、以下のクエリを実行します。

sql SELECT user_id, user_name FROM users WHERE user_id NOT IN (SELECT user_id FROM orders);

サブクエリ (SELECT user_id FROM orders) の結果セットは {1, 3, NULL, 4} (重複排除前) となります。重複を排除しても NULL は残りますので、実質的な比較対象リストは {1, 3, 4, NULL} となります。

WHERE user_id NOT IN (1, 3, 4, NULL) は論理的に以下と等価です。
WHERE user_id != 1 AND user_id != 3 AND user_id != 4 AND user_id != NULL

ユーザー Bob (user_id = 2) の場合を考えます。
2 != 1 (True)
2 != 3 (True)
2 != 4 (True)
2 != NULL (Unknown)

これらの条件をANDで結合すると True AND True AND True AND Unknown となり、結果は Unknown と評価されます。WHERE句は Unknown の行を結果セットに含めないため、Bob は抽出されません。これは Eve (user_id = 5) や、その他のすべてのユーザーについても同様です。

結果:

空の結果セットが返されます。

これは NOT IN を使う際に非常に危険な挙動です。サブクエリの対象となるカラムに NULL が含まれる可能性が少しでもある場合、この問題によって期待とは全く異なる（通常は空の）結果が得られてしまうため、非常に注意が必要です。

NULL値問題の回避策

NOT IN を使い続けたい場合は、サブクエリの結果セットから NULL 値を確実に除外する必要があります。これは、サブクエリ内で WHERE column_name IS NOT NULL 条件を追加することで実現できます。

sql SELECT user_id, user_name FROM users WHERE user_id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL); -- NULLを除外

この修正により、サブクエリ (SELECT user_id FROM orders WHERE user_id IS NOT NULL) の結果セットは {1, 3, 4} となり、NULL が含まれなくなります。したがって、メインクエリは WHERE user_id NOT IN (1, 3, 4) となり、期待通り Bob (user_id 2) と Eve (user_id 5) が抽出されます。

この回避策は効果的ですが、NOT IN を使う限り、サブクエリの結果に NULL が含まれないことを常に意識し、IS NOT NULL を追加するのを忘れないようにする必要があります。これが、NOT IN を「注意して使うべき」と言われる理由の一つです。

2. パフォーマンスの問題

NOT IN は、特にサブクエリの結果セットが大きい場合や、対象のテーブルが大きい場合に、パフォーマンスが劣化する傾向があります。

パフォーマンスの問題は、データベースが NOT IN 句をどのように実行するかに関係しています。オプティマイザはさまざまな実行計画を立てますが、NOT IN (特にサブクエリを使用する場合) は、効率的な実行計画を立てにくい場合があります。

考えられる実行方法の一つとして、データベースがまずサブクエリを実行して結果セットをメモリ上に保持し、次に外部クエリの各行に対して、その結果セットに含まれていないかを一つずつチェックするという方法があります。このチェックは、サブクエリの結果セットのサイズに比例してコストが増大します。特に、サブクエリの結果セットが非常に大きい場合、この比較処理は非常に多くのCPUリソースと時間を消費する可能性があります。

また、NOT IN はインデックスを効率的に利用しにくい場合があります。例えば、WHERE column_name = value1 OR column_name = value2 OR ... のような IN 条件は、value1, value2,… のそれぞれに対してインデックスルックアップを行って効率的に行を特定できます。しかし、NOT IN の場合は WHERE column_name != value1 AND column_name != value2 AND ... という否定の条件になり、特定の値を「含まない」という条件は、インデックスを使って高速に絞り込むのが難しい場合があります。結果として、フルテーブルスキャンが発生しやすくなることがあります。

サブクエリの種類による影響:

NOT IN で使用されるサブクエリは、通常、外部クエリの各行に依存しない「非相関サブクエリ」です。非相関サブクエリは一度だけ実行され、その結果が外部クエリで使用されるため、これはパフォーマンス上は有利な点です。しかし、前述の「サブクエリ結果セットとの比較」の効率が悪いため、結果的に性能問題につながることがあります。

対照的に、後述する NOT EXISTS で使用されるサブクエリは、外部クエリの各行に依存する「相関サブクエリ」となることが一般的です。相関サブクエリは外部クエリの行ごとに実行されるため、サブクエリ自体の実行回数は増えますが、サブクエリ内部では外部クエリの現在の行を使って絞り込みができるため、サブクエリの対象となるデータ量が少なくなり、効率的な実行が可能になる場合があります。

これらの理由から、NOT IN はサブクエリの結果セットが小さい場合や、対象のテーブルが小さい場合には問題にならないかもしれませんが、大規模なデータセットを扱う場合にはパフォーマンス上のボトルネックになる可能性があります。

`LEFT JOIN` と `IS NULL` を使った代替方法

NOT IN の NULL 値の問題とパフォーマンスの問題を回避するための、最も一般的で推奨される代替手段の一つが、LEFT JOIN と IS NULL を組み合わせる方法です。

この方法の基本的な考え方は、「一方のテーブルには存在するが、もう一方のテーブルには存在しない行」を特定することです。これは、LEFT JOIN を使用して両方のテーブルを結合し、結合できなかった側のテーブルのカラムが NULL になることを利用して判断します。

構文

sql SELECT T1.column_list FROM table1 T1 LEFT JOIN table2 T2 ON T1.column = T2.column WHERE T2.column IS NULL;

ここで、table1 は「存在すべきデータ」を持つテーブル、table2 は「存在しないことを確認したいデータ」を持つテーブルとします。T1.column = T2.column は、両方のテーブルを関連付けるキーとなるカラムでの結合条件です。

LEFT JOIN table2 T2 ON T1.column = T2.column は、table1 のすべての行と、結合条件 T1.column = T2.column に一致する table2 の行を結合します。table1 の行に対応する table2 の行が見つからなかった場合、結果セットには table1 の行と、table2 のすべてのカラムに NULL が設定された状態の行が含まれます。

WHERE T2.column IS NULL という条件は、この LEFT JOIN の結果から、「table2 のカラム（結合条件に使ったカラムなど、NULLになりうるカラムであればどれでも良い）が NULL である行」のみを抽出します。これはまさに、「table1 には存在するが、table2 には対応する行が存在しない」ことを意味します。

具体的な例：まだ注文したことのないユーザー

先ほどの「まだ注文したことのないユーザー」を抽出する例を、LEFT JOIN ... IS NULL を使って書き換えてみます。

サンプルテーブル: users (T1)
サンプルテーブル: orders (T2)

抽出したいのは users テーブルに存在するが、orders テーブルには対応する user_id が存在しないユーザーです。

sql SELECT u.user_id, u.user_name FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.user_id IS NULL;

クエリの実行ステップと考え方:

FROM users u: users テーブルからすべての行を取得します。
LEFT JOIN orders o ON u.user_id = o.user_id: users テーブルの各行に対して、orders テーブルから user_id が一致する行を探して結合します。
- users.user_id = 1 (Alice): orders テーブルには user_id = 1 の行が2つあります (order_id 101, 103)。Aliceの行はこれらの注文行と結合されます。
- users.user_id = 2 (Bob): orders テーブルには user_id = 2 の行がありません。Bobの行は orders テーブルの対応するカラムがすべて NULL となって結合されます。
- users.user_id = 3 (Charlie): orders テーブルには user_id = 3 の行が1つあります (order_id 102)。Charlieの行はその注文行と結合されます。
- users.user_id = 4 (David): orders テーブルには user_id = 4 の行が1つあります (order_id 104)。Davidの行はその注文行と結合されます。
- users.user_id = 5 (Eve): orders テーブルには user_id = 5 の行がありません。Eveの行は orders テーブルの対応するカラムがすべて NULL となって結合されます。
(注: 結合結果は、もし一致する注文が複数あれば、ユーザーの行が複数回現れますが、WHERE 句で絞り込むため最終結果には影響しません。)
WHERE o.user_id IS NULL: 結合結果のうち、orders テーブル側の user_id (エイリアス o.user_id) が NULL である行のみを選択します。これは、orders テーブルに一致する行が見つからなかった (LEFT JOIN によって NULL が設定された) 行に対応します。

WHERE句で絞り込まれる行:

Bob (user_id = 2): o.user_id は NULL です。条件に一致します。
Eve (user_id = 5): o.user_id は NULL です。条件に一致します。
Alice, Charlie, David: o.user_id はそれぞれ 1, 3, 4 であり、NULL ではありません。条件に一致しません。

実行結果:

user_id	user_name
2	Bob
5	Eve

期待通りの結果が得られました。

`LEFT JOIN ... IS NULL` のメリット

NULL値の問題を回避できる: LEFT JOIN ... IS NULL の方法では、orders テーブルの元の user_id カラムに NULL 値が含まれていても問題ありません。LEFT JOIN の ON 句は NULL = NULL を偽と評価するため、元のテーブルの NULL 値が結合の成功・失敗に影響することはありません。WHERE o.user_id IS NULL は、LEFT JOIN によって 結合できなかったために NULL になった行を判定するため、意図した通りに機能します。
パフォーマンスに優れることが多い: データベースシステムは結合処理（JOIN）を効率的に実行するための高度な最適化技術を持っています。LEFT JOIN は多くの場合、インデックスを効果的に利用して高速に実行できます。特に、結合キー（この例では user_id）にインデックスが張られている場合、データベースはインデックスを使って効率的に一致する行を見つけたり、一致しない行（すなわち IS NULL に該当する行）を特定したりできます。NOT IN のようにサブクエリの結果セット全体との比較を行うよりも、結合処理の方が効率的な実行計画を選択しやすい傾向があります。

`LEFT JOIN ... IS NULL` のデメリット

構文が少し複雑に感じられる場合がある: NOT IN に比べて、結合条件と WHERE 句を組み合わせるため、最初は少し分かりにくく感じるかもしれません。
結合キーが複数ある場合: 結合条件が複数のカラムにわたる場合でも使用可能ですが、条件が複雑になる可能性があります。
重複の問題: もし table1 の単一行に対して table2 に複数の一致する行がある場合、LEFT JOIN の中間結果には table1 の行が複数回現れます。しかし、WHERE T2.column IS NULL で絞り込むため、最終的な結果セットには table1 の「一致しなかった」行が重複なく含まれます（table1 自体に重複がなければ）。もし table1 に重複があり、その重複行がいずれも table2 と一致しない場合、その重複はそのまま結果に現れます。これは SELECT DISTINCT を使用することで回避できますが、一般的には主キーなど重複のないカラムを対象にすることが多いでしょう。

`NOT EXISTS` を使った代替方法

NOT IN の代替手段としては、LEFT JOIN ... IS NULL と並んで NOT EXISTS も非常に有力な選択肢です。

EXISTS 演算子は、サブクエリが一つ以上の行を返すかどうか（存在するかの真偽）をチェックします。NOT EXISTS はその逆で、サブクエリが一つも行を返さないかどうかをチェックします。

構文

NOT EXISTS は通常、相関サブクエリと共に使用されます。

sql SELECT T1.column_list FROM table1 T1 WHERE NOT EXISTS (SELECT 1 FROM table2 T2 WHERE T1.column = T2.column AND additional_condition);

ここで、table1 はメインのテーブル、table2 は存在しないことを確認したいデータがあるテーブルです。
(SELECT 1 FROM table2 T2 WHERE T1.column = T2.column AND additional_condition) が相関サブクエリです。このサブクエリは、外部クエリ (table1 から取得された T1 の現在の行) の値 (T1.column) を使用して table2 を検索します。

WHERE NOT EXISTS (...) は、「括弧内のサブクエリが、現在の T1 の行に対して、一つも行を返さない（つまり、条件を満たす T2 の行が存在しない）場合に真となる」という意味になります。

サブクエリ内で SELECT 1 としているのは慣習的なものです。EXISTS/NOT EXISTS はサブクエリが返す値には関心がなく、単に 行が存在するかどうか だけをチェックするため、SELECT * や他のカラムを指定しても結果は変わりませんし、SELECT 1 とすることで不要なデータ取得を避けることができます。

具体的な例：まだ注文したことのないユーザー

「まだ注文したことのないユーザー」を抽出する例を、NOT EXISTS を使って書き換えてみます。

サンプルテーブル: users (T1)
サンプルテーブル: orders (T2)

sql SELECT u.user_id, u.user_name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE u.user_id = o.user_id);

クエリの実行ステップと考え方:

FROM users u: users テーブルから1行ずつ取り出し、処理を開始します。
WHERE NOT EXISTS (...): 取り出した users の現在の行 (u) に対して、NOT EXISTS 句内のサブクエリを実行します。
(SELECT 1 FROM orders o WHERE u.user_id = o.user_id): このサブクエリは、現在の users の行の user_id と同じ user_id を持つ行が orders テーブルに存在するかどうかをチェックします。
- users.user_id = 1 (Alice) の場合: サブクエリ SELECT 1 FROM orders o WHERE 1 = o.user_id を実行します。orders テーブルには user_id = 1 の行が存在します。サブクエリは行を返すため、EXISTS は真、NOT EXISTS は偽となります。Alice の行は結果に含まれません。
- users.user_id = 2 (Bob) の場合: サブクエリ SELECT 1 FROM orders o WHERE 2 = o.user_id を実行します。orders テーブルには user_id = 2 の行が存在しません。サブクエリは行を返さないため、EXISTS は偽、NOT EXISTS は真となります。Bob の行は結果に含まれます。
- users.user_id = 5 (Eve) の場合: サブクエリ SELECT 1 FROM orders o WHERE 5 = o.user_id を実行します。orders テーブルには user_id = 5 の行が存在しません。サブクエリは行を返さないため、EXISTS は偽、NOT EXISTS は真となります。Eve の行は結果に含まれます。

実行結果:

user_id	user_name
2	Bob
5	Eve

これも期待通りの結果が得られました。

`NOT EXISTS` のメリット

NULL値の問題を回避できる: EXISTS/NOT EXISTS はサブクエリの結果セット内の個々の値ではなく、行の存在 だけをチェックします。したがって、サブクエリが参照するカラムに NULL 値が含まれていても、そのことが EXISTS/NOT EXISTS の評価に直接的な悪影響を与えることはありません。NOT IN のような NULL に起因する問題は発生しません。
パフォーマンスに優れることが多い: NOT EXISTS は相関サブクエリを使用するため、一見すると外部クエリの各行に対してサブクエリが実行される点が非効率に見えるかもしれません。しかし、データベースオプティマイザは NOT EXISTS (および EXISTS) を効率的に処理するための最適化を行います。特に、サブクエリ内で条件を満たす最初の1行が見つかった時点で、それ以上の検索を停止して真であると判断できる（EXISTS の場合）または、最後まで検索して1行も見つからなかった場合に真であると判断できる（NOT EXISTS の場合）という性質は、特にサブクエリの対象となるテーブルが大きい場合に効率的です。また、結合条件に使用されるカラムにインデックスが適切に設定されていれば、サブクエリの実行は非常に高速になります。
柔軟性: サブクエリ内に複雑な条件 (additional_condition の部分) を追加して、より複雑な「存在しないこと」の条件を指定しやすいという利点もあります。例えば、「過去1年間注文がないユーザー」のような条件も、サブクエリに WHERE order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR) のような条件を追加することで簡単に実現できます。

`NOT EXISTS` のデメリット

構文が少し分かりにくい場合がある: 特に相関サブクエリに慣れていない場合、最初はクエリの読み解きに時間がかかるかもしれません。外部クエリの各行とサブクエリがどのように連携しているかを理解する必要があります。

それぞれの方法の比較と使い分け

ここまで、NOT IN、LEFT JOIN ... IS NULL、そして NOT EXISTS の3つの方法を見てきました。それぞれの特徴、メリット、デメリットを理解した上で、状況に応じて最適な方法を選択することが重要です。

特徴/方法	`NOT IN`	`LEFT JOIN ... IS NULL`	`NOT EXISTS`
NULL値の扱い	サブクエリにNULLが含まれると問題発生	NULL値の影響なし（堅牢）	NULL値の影響なし（堅牢）
パフォーマンス	サブクエリが大きいと劣化傾向あり、インデックス利用に限界	結合最適化が効きやすく、インデックス利用効率が良い傾向	相関サブクエリだが、効率的な実行計画を立てやすい傾向
構文の分かりやすさ	シンプルで直感的	JOINとWHEREを組み合わせる	相関サブクエリを含む
柔軟性	値のリストまたは単純なサブクエリ	結合条件に基づく除外	サブクエリ内の複雑な条件も可能
使い分けのヒント	・比較リストが固定値で NULL なし・サブクエリ結果が小さく、NULL なしが保証される	・パフォーマンスが重要・NULL値の考慮が必要・結合キーが明確	・パフォーマンスが重要・NULL値の考慮が必要・複雑な条件での除外が必要

どの方法を選ぶべきか？

多くの場合、LEFT JOIN ... IS NULL または NOT EXISTS が推奨されます。その理由は、NULL値の問題が発生しないことと、一般的にパフォーマンスに優れる傾向があるためです。

NULL値の混入が絶対にありえない、かつ比較対象が固定値や非常に小さいリストである場合: NOT IN はシンプルで読みやすいため、適しているかもしれません。ただし、実務ではサブクエリを使うことが多いため、NULLのリスクを考えると常に IS NOT NULL を追加する必要があることに注意が必要です。
NULL値の考慮が必要で、パフォーマンスも重視する場合:
- LEFT JOIN ... IS NULL: 2つのテーブル間の単純な関連に基づいて除外する場合に非常に効果的です。結合条件がシンプルで、結合キーに適切なインデックスが設定されている場合に、オプティマイザが効率的な実行計画を立てやすい傾向があります。クエリの形が「左側のテーブルの行で、右側のテーブルに対応するものがないもの」と明確に対応するため、理解しやすい場合があります。
- NOT EXISTS: より複雑な条件で「存在しないこと」を判定したい場合に柔軟に対応できます。相関サブクエリ内の条件を細かく指定できます。大規模なテーブルや、サブクエリ内で効率的に絞り込める条件がある場合に、特にパフォーマンスが有利になることがあります。

迷ったら LEFT JOIN ... IS NULL または NOT EXISTS を使う のが良いでしょう。これらの方法は NOT IN の持つ潜在的な問題を回避できるため、より安全で堅牢なクエリを作成できます。

どちらの代替手段が良いかは、テーブルのサイズ、データの分布、インデックスの状況、および具体的なクエリの複雑さによって異なります。最も確実なのは、それぞれの方法でクエリを記述し、EXPLAIN ステートメントを使って実行計画を比較検討することです。

実践的な例と考慮事項

1. インデックスの重要性

NOT IN、LEFT JOIN、NOT EXISTS のいずれの方法を使うにしても、パフォーマンスを最適化するためには適切なインデックスが不可欠です。

NOT IN の場合：サブクエリの対象となるカラム（例: orders テーブルの user_id）にインデックスがあると、サブクエリ自体の実行は速くなりますが、外部クエリの NOT IN 条件でそのインデックスが十分に活用されない場合があります。
LEFT JOIN ... IS NULL の場合：ON 句で結合条件に使用するカラム（例: users テーブルの user_id と orders テーブルの user_id）の両方にインデックスがあると、結合処理が非常に効率的に行われ、一致しない行（NULLになる行）を高速に特定できます。
NOT EXISTS の場合：相関サブクエリの WHERE 句で外部クエリと関連付けるカラム（例: orders テーブルの user_id）にインデックスがあると、サブクエリが外部の行ごとに実行される際に、そのインデックスを使って効率的にチェックを行えます。

一般的に、LEFT JOIN や NOT EXISTS で結合/関連付けのキーとして使用するカラムには、インデックスを張っておくことを強く推奨します。

2. `EXPLAIN` を使ったクエリ実行計画の分析

どの方法が自分の環境やデータに対して最もパフォーマンスが良いかを知るためには、EXPLAIN ステートメントを使ってクエリの実行計画を確認することが重要です。

“`sql
EXPLAIN SELECT user_id, user_name
FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

EXPLAIN SELECT u.user_id, u.user_name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;

EXPLAIN SELECT u.user_id, u.user_name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE u.user_id = o.user_id);
“`

EXPLAIN の出力を見ることで、各クエリがどのテーブルをどの順序で、どのような方法（フルスキャン、インデックス利用など）でアクセスしているか、何行を調査しているか（rows 列）、JOINの種類（type 列）、使用しているキー（key 列）、WHERE句がどのように処理されているか（Extra 列）などを確認できます。

例えば、type が ALL となっている場合はフルテーブルスキャンが発生している可能性が高く、index や ref となっている場合はインデックスが効率的に使われていることを示します。Extra 列に Using where, Using index, Using join buffer, Using where; Not exists といった情報が表示されます。

LEFT JOIN ... IS NULL では、users をスキャンし、orders をインデックスを使ってルックアップし、o.user_id IS NULL で絞り込む、といった計画になることが多いでしょう。NOT EXISTS では、users をスキャンし、その行ごとに orders を相関サブクエリとしてインデックスを使ってルックアップし、存在しない場合に残す、といった計画になることが多いです。

EXPLAIN の出力は複雑ですが、特に type, rows, Extra の列を比較することで、どのクエリがより効率的であるかのヒントを得ることができます。

3. その他の考慮事項

可読性: シンプルなケースでは NOT IN が最も読みやすいと感じるかもしれません。しかし、NULL値の問題や潜在的なパフォーマンス問題を考えると、LEFT JOIN ... IS NULL や NOT EXISTS を積極的に使用し、それらに慣れる方が結果的に堅牢なコードにつながります。
DBMSごとの違い: SQLの標準として NOT IN, LEFT JOIN, EXISTS/NOT EXISTS はいずれも存在しますが、具体的なオプティマイザの実装やパフォーマンス特性はデータベースシステム（MySQL, PostgreSQL, SQL Server, Oracleなど）によって異なります。この記事はMySQLに焦点を当てていますが、基本的な考え方は他の多くのRDBMSにも適用可能です。ただし、最適な方法を選択する際には、対象のDBMSの特性を理解し、EXPLAIN などを使って実際の動作を確認することが推奨されます。

まとめ

この記事では、MySQLにおける NOT IN の使い方から始め、その主要な注意点である NULL 値の問題とパフォーマンスの問題について詳しく解説しました。

NOT IN は直感的で便利ですが、サブクエリの結果セットに NULL が含まれると、条件が常に Unknown と評価され、意図しない結果（通常は空の結果セット）となる という重大な問題を抱えています。これを回避するには、サブクエリに IS NOT NULL 条件を追加する必要があります。
NOT IN は、特にサブクエリの結果セットが大きい場合や、対象テーブルが大きい場合に、パフォーマンスが劣化しやすい傾向があります。インデックスが効率的に利用されにくい場合があります。

これらの NOT IN の問題を克服するための代替手段として、以下の2つの方法を紹介しました。

LEFT JOIN ... IS NULL: 一方のテーブルには存在するが、他方のテーブルには存在しない行を特定する標準的な方法です。NULL 値の問題を回避でき、結合処理の最適化により高いパフォーマンスが期待できます。
NOT EXISTS: 相関サブクエリと組み合わせて、「指定した条件を満たす行が存在しない」ことを判定する方法です。NULL 値の問題を回避でき、相関サブクエリの効率的な実行（特に最初の不一致での停止）により、こちらも高いパフォーマンスが期待できます。また、サブクエリ内に複雑な条件を指定しやすい柔軟性があります。

一般的には、NOT IN の潜在的な問題を考慮すると、LEFT JOIN ... IS NULL または NOT EXISTS を使用する方が、より堅牢でパフォーマンスに優れたクエリを作成できます。どちらを選択するかは、具体的な要件、テーブル構造、データ量、および個人の好みやチームのコーディング規約によります。

最も重要なのは、これらの方法それぞれの挙動（特にNULL値の扱い）とパフォーマンス特性を理解し、EXPLAIN を活用して実際のクエリの実行計画を確認しながら、自身の環境に最適な方法を選択することです。

安全で効率的なクエリを作成するために、NOT IN の利用には十分注意し、必要に応じて LEFT JOIN ... IS NULL や NOT EXISTS といった代替手段を積極的に活用してください。