MySQL NOT IN句の解説と使い方

By /

MySQL NOT IN句の徹底解説：使い方、注意点、そして代替手段

はじめに

データベースを操作する際、特定の条件に一致するデータを選択することは非常に一般的です。MySQLのSELECT文では、WHERE句を使用してこの条件を指定します。例えば、「価格が1000円以上の商品」や「登録日が今日の顧客」といった条件です。

さらに、特定の「リスト」に含まれる値を持つ行を選択したい場合があります。このような場合はIN句が非常に便利です。「商品カテゴリが ‘Electronics’ または ‘Books’ の商品」を選択する場合などがこれにあたります。

sql
SELECT *
FROM products
WHERE category IN ('Electronics', 'Books');

では、「特定のリストに含まれない値を持つ行」を選択したい場合はどうでしょうか？例えば、「商品カテゴリが ‘Electronics’ または ‘Books’ ではない 商品」や、「注文履歴のある顧客のリストに含まれない顧客」を選択したいといった要件です。このような「除外条件」を指定する際に活躍するのが、本記事の主題であるNOT IN句です。

NOT IN句は、直感的に理解しやすく便利な構文ですが、特にNULL値の扱いやパフォーマンス面で注意すべき点があります。また、同じ目的を達成するための代替手段もいくつか存在し、状況によってはそちらの方が適している場合もあります。

本記事では、MySQLにおけるNOT IN句の基本的な使い方から、サブクエリとの連携、NULL値が引き起こす問題とその回避策、パフォーマンスに関する考慮事項、そしてLEFT JOIN + IS NULLやNOT EXISTSといった代替手段との比較まで、NOT IN句に関するあらゆる側面を詳細に解説します。この記事を読むことで、NOT IN句を安全かつ効果的に使いこなすための知識が身につくでしょう。

MySQLにおけるNOT IN句の基本

NOT IN句は、指定したカラムの値が、カッコ内にリストされた値のいずれにも一致しない行を選択するために使用されます。これは、IN句の否定形と考えることができます。

構文:

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

ここで、
– column_list: 取得したいカラムを指定します。*は全てのカラムを意味します。
– table_name: データを取得するテーブルの名前です。
– column_name: NOT IN句で条件を指定したいカラムの名前です。
– (value1, value2, value3, ...): 除外したい値のリストです。これらの値は、column_nameのデータ型と互換性がある必要があります。リストはコンマ区切りで指定します。

基本的な使用例（静的な値リスト）:

簡単な例として、以下のようなproductsテーブルがあると仮定します。

sql
-- productsテーブルの構造
+------------+--------------+----------+------------+
| product_id | product_name | category | price |
+------------+--------------+----------+------------+
| 101 | Laptop | Electronics | 1200.00 |
| 102 | Keyboard | Electronics | 75.00 |
| 103 | Desk Chair | Furniture | 150.00 |
| 104 | Monitor | Electronics | 300.00 |
| 105 | Dining Table | Furniture | 500.00 |
| 106 | Novel | Books | 15.00 |
+------------+--------------+----------+------------+

ここで、「商品カテゴリが ‘Electronics’ または ‘Furniture’ ではない 商品」を選択したいとします。この場合、NOT IN句を使用して以下のようにクエリを書くことができます。

例1: 特定のカテゴリIDではない製品を選択

sql
SELECT product_id, product_name, category, price
FROM products
WHERE category NOT IN ('Electronics', 'Furniture');

このクエリは、categoryカラムの値が 'Electronics' でも 'Furniture' でもない行を選択します。実行結果は以下のようになるはずです。

sql
+------------+--------------+----------+-------+
| product_id | product_name | category | price |
+------------+--------------+----------+-------+
| 106 | Novel | Books | 15.00 |
+------------+--------------+----------+-------+

これは、'Books'カテゴリの商品のみが 'Electronics' か 'Furniture' のいずれにも含まれないためです。

IN句との対比:

比較のために、同じテーブルに対してIN句を使用する例を見てみましょう。

sql
SELECT product_id, product_name, category, price
FROM products
WHERE category IN ('Electronics', 'Furniture');

このクエリは、「商品カテゴリが ‘Electronics’ または ‘Furniture’ である 商品」を選択します。実行結果は以下のようになります。

sql
+------------+--------------+----------+----------+
| product_id | product_name | category | price |
+------------+--------------+----------+----------+
| 101 | Laptop | Electronics | 1200.00 |
| 102 | Keyboard | Electronics | 75.00 |
| 103 | Desk Chair | Furniture | 150.00 |
| 104 | Monitor | Electronics | 300.00 |
| 105 | Dining Table | Furniture | 500.00 |
+------------+--------------+----------+----------+

このように、NOT IN句はIN句で指定した条件の「補集合」を選択するために使用されることが分かります。

NOT IN句の様々な使い方

NOT IN句は、先ほどの例のような静的な値のリストだけでなく、より動的で複雑な条件指定にも利用できます。特に強力なのが、サブクエリと組み合わせる使い方です。

1. 静的な値リスト

これは前述の基本構文で見た通りです。直接値を指定する方法で、除外したい値のリストが固定である場合や、比較的小さい場合に適しています。値は数値、文字列、日付など、比較対象のカラムのデータ型と互換性があれば指定できます。

“`sql
— 数値の例: 特定のIDのユーザーを除外
SELECT *
FROM users
WHERE user_id NOT IN (101, 105, 203);

— 文字列の例: 特定の都市からの顧客を除外
SELECT *
FROM customers
WHERE city NOT IN (‘Tokyo’, ‘Osaka’, ‘Nagoya’);

— 日付の例: 特定の日付のトランザクションを除外 (MySQLの日付フォーマットに注意)
SELECT *
FROM transactions
WHERE transaction_date NOT IN (‘2023-01-15’, ‘2023-02-20’);
“`

静的なリストを使う場合、リストのサイズが非常に大きくなると、クエリの可読性が低下したり、パフォーマンスに影響が出たりする可能性があります。数百、数千といった要素を持つリストを指定する場合は、後述のサブクエリや他の方法を検討した方が良い場合があります。

2. サブクエリとの連携

NOT IN句のカッコ内には、静的な値リストの代わりにサブクエリを指定することができます。サブクエリは、メインクエリが実行される前に実行され、結果として1つのカラムからなる値のリストを返します。メインクエリは、そのサブクエリが返したリストに含まれない値を持つ行を選択します。

この使い方は、除外したいリストが他のテーブルのデータに基づいている場合や、複雑な条件で動的に生成される場合に非常に有効です。

例2: 注文履歴のない顧客を選択

以下の2つのテーブルがあると仮定します。

customersテーブル:

sql
+-----------+------------+--------+
| customer_id | customer_name | city |
+-----------+------------+--------+
| 1 | Alice | Tokyo |
| 2 | Bob | Osaka |
| 3 | Charlie | Tokyo |
| 4 | David | Nagoya |
| 5 | Eve | Osaka |
+-----------+------------+--------+

ordersテーブル:

sql
+---------+-------------+------------+----------+
| order_id | customer_id | order_date | amount |
+---------+-------------+------------+----------+
| 1001 | 1 | 2023-01-10 | 50.00 |
| 1002 | 3 | 2023-01-12 | 120.00 |
| 1003 | 1 | 2023-01-15 | 30.00 |
| 1004 | 5 | 2023-01-20 | 200.00 |
+---------+-------------+------------+----------+

ここで、「これまでに一度も注文したことのない顧客」を選択したいとします。これは、customersテーブルには存在するが、ordersテーブルのcustomer_idリストには存在しない顧客を検索することと同じです。NOT IN句とサブクエリを使ってこれを実現できます。

まず、注文履歴のある顧客IDのリストをサブクエリで取得します。

sql
SELECT customer_id FROM orders;
-- このサブクエリは (1, 3, 5) というリストを返します。

次に、このリストをNOT IN句で使用して、注文履歴のない顧客を選択するメインクエリを書きます。

sql
SELECT customer_id, customer_name, city
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

このクエリは、customersテーブルのcustomer_idが、サブクエリが返したリスト(1, 3, 5)に含まれない行を選択します。実行結果は以下のようになります。

sql
+-----------+---------------+--------+
| customer_id | customer_name | city |
+-----------+---------------+--------+
| 2 | Bob | Osaka |
| 4 | David | Nagoya |
+-----------+---------------+--------+

Bob (ID=2) と David (ID=4) はordersテーブルにcustomer_idが存在しないため、注文履歴がないと判断され、結果に含まれています。

サブクエリを使用するメリット:

  • 動的なリスト: 除外したいリストが固定ではなく、他のテーブルのデータや複雑な条件によって決定される場合に非常に便利です。
  • データの一貫性: サブクエリは常に最新のデータに基づいてリストを生成するため、静的なリストを使うよりもデータの一貫性が保たれます。
  • 表現力: 複雑な除外条件を簡潔に記述できます。

サブクエリを使用するデメリット:

  • パフォーマンス: サブクエリの実行に時間がかかる場合、メインクエリ全体のパフォーマンスが低下する可能性があります。特に、サブクエリが大量のデータを返す場合や、インデックスが適切に利用されない場合に問題になりやすいです。
  • NULL値問題: サブクエリがNULL値を返す場合や、比較対象のカラムにNULL値が含まれる場合に、予期しない結果を引き起こす可能性があります。これは後述のセクションで詳しく解説します。

3. 他の条件との組み合わせ

NOT IN句は、WHERE句内の他の条件（AND, OR, 比較演算子など）と自由に組み合わせることができます。

例3: 特定の地域かつ特定の製品カテゴリではない注文

以下のordersテーブルがあるとします。（例2のordersテーブルに製品カテゴリ情報を加えたものを想定）

sql
-- ordersテーブル (拡張版)
+---------+-------------+------------+----------+----------+
| order_id | customer_id | order_date | amount | category |
+---------+-------------+------------+----------+----------+
| 1001 | 1 | 2023-01-10 | 50.00 | Books |
| 1002 | 3 | 2023-01-12 | 120.00 | Electronics |
| 1003 | 1 | 2023-01-15 | 30.00 | Books |
| 1004 | 5 | 2023-01-20 | 200.00 | Furniture |
| 1005 | 2 | 2023-01-22 | 80.00 | Electronics |
+---------+-------------+------------+----------+----------+

ここで、「customer_id が 1 または 3 の顧客からの注文で、かつ カテゴリが ‘Books’ または ‘Furniture’ ではない 注文」を選択したいとします。

sql
SELECT order_id, customer_id, amount, category
FROM orders
WHERE customer_id IN (1, 3) -- customer_id が 1 または 3 の注文
AND category NOT IN ('Books', 'Furniture'); -- category が 'Books' または 'Furniture' ではない注文

このクエリは、まず customer_id が 1 または 3 の注文に絞り込みます。該当するのは order_id 1001, 1002, 1003 です。次に、その中から category が ‘Books’ または ‘Furniture’ ではないものを選びます。
– order_id 1001: customer_id=1, category=’Books’ -> category NOT IN 条件を満たさない
– order_id 1002: customer_id=3, category=’Electronics’ -> category NOT IN 条件を満たす
– order_id 1003: customer_id=1, category=’Books’ -> category NOT IN 条件を満たさない

したがって、実行結果は以下のようになります。

sql
+---------+-------------+--------+-------------+
| order_id | customer_id | amount | category |
+---------+-------------+--------+-------------+
| 1002 | 3 | 120.00 | Electronics |
+---------+-------------+--------+-------------+

このように、NOT IN句はANDやORといった論理演算子を使って、他の様々な条件と組み合わせて使用できます。これにより、より複雑で具体的なフィルタリング要件を満たすクエリを作成することが可能です。

NOT IN句とNULL値の問題

NOT IN句を使う上で、最も注意が必要なのがNULL値の扱いです。SQLにおけるNULLは「未知」の値であり、他の値と比較する際に特別な振る舞いをします。この性質が、NOT IN句では予期しない結果を引き起こす可能性があるのです。

NOT IN (value1, value2, ...)という条件は、論理的には (column_name <> value1 AND column_name <> value2 AND ...) とほぼ等価です。この等価性が崩れるのがNULLが絡む場合です。

SQLの比較演算において、NULLと他の値を比較した場合（例えば value = NULL や value <> NULL）、結果は真でも偽でもなく、常にUNKNOWN（不明）となります。そして、WHERE句は条件がTRUEと評価された行のみを選択します。FALSEやUNKNOWNと評価された行は選択されません。

このUNKNOWNという結果が、NOT IN句の振る舞いに影響を与えます。具体的なケースを見てみましょう。

問題ケース1: NOT INリストにNULLが含まれる場合

WHERE column_name NOT IN (value1, value2, NULL) のように、NOT IN句のカッコ内のリストにNULL値が含まれている場合です。

例えば、以下のsample_dataテーブルがあるとします。

sql
+----+-------+
| id | value |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | NULL |
+----+-------+

ここで、「valueが ‘A’, ‘B’, NULL のいずれでもない行」を選択したいとします。直感的には、valueが ‘C’ の行 (id=3) が選択されると期待するかもしれません。クエリは以下のようになります。

sql
SELECT id, value
FROM sample_data
WHERE value NOT IN ('A', 'B', NULL);

このクエリを実行すると、驚くべきことに結果は何も返されません。なぜでしょうか？

WHERE value NOT IN ('A', 'B', NULL)という条件は、内部的には NOT (value = 'A' OR value = 'B' OR value = NULL) のように評価されます。（厳密には少し異なりますが、考え方としてはこれで十分です。）

各行について、この条件がどのように評価されるかを見てみましょう。

  • id=1, value=’A’ の場合:

    • ('A' = 'A' OR 'A' = 'B' OR 'A' = NULL)
    • ('A' = 'A') は TRUE
    • ('A' = 'B') は FALSE
    • ('A' = NULL) は UNKNOWN
    • TRUE OR FALSE OR UNKNOWN は TRUE （OR演算子において、一つでもTRUEがあれば結果はTRUE）
    • NOT (TRUE) は FALSE
    • WHERE句の条件がFALSEになるため、この行は選択されません。これは期待通りの動作です（リストに含まれるため）。

  • id=2, value=’B’ の場合:

    • ('B' = 'A' OR 'B' = 'B' OR 'B' = NULL)
    • ('B' = 'A') は FALSE
    • ('B' = 'B') は TRUE
    • ('B' = NULL) は UNKNOWN
    • FALSE OR TRUE OR UNKNOWN は TRUE
    • NOT (TRUE) は FALSE
    • WHERE句の条件がFALSEになるため、この行は選択されません。これも期待通りです。

  • id=3, value=’C’ の場合:

    • ('C' = 'A' OR 'C' = 'B' OR 'C' = NULL)
    • ('C' = 'A') は FALSE
    • ('C' = 'B') は FALSE
    • ('C' = NULL) は UNKNOWN
    • FALSE OR FALSE OR UNKNOWN は UNKNOWN （OR演算子において、一つでもTRUEがなく、一つでもUNKNOWNがあれば結果はUNKNOWN）
    • NOT (UNKNOWN) は UNKNOWN
    • WHERE句の条件がUNKNOWNになるため、この行は選択されません。これが問題です！ 直感的にはvalue=’C’はリスト(‘A’, ‘B’, NULL)に含まれないので選択されるべきですが、NULLとの比較がUNKNOWNになるせいで最終的な条件もUNKNOWNとなり、結果として選ばれません。

  • id=4, value=NULL の場合:

    • (NULL = 'A' OR NULL = 'B' OR NULL = NULL)
    • NULL = 'A' は UNKNOWN
    • NULL = 'B' は UNKNOWN
    • NULL = NULL は UNKNOWN （NULLとNULLの比較もUNKNOWNです）
    • UNKNOWN OR UNKNOWN OR UNKNOWN は UNKNOWN
    • NOT (UNKNOWN) は UNKNOWN
    • WHERE句の条件がUNKNOWNになるため、この行は選択されません。これも直感とは異なるかもしれません。

結論: NOT IN句のリストにNULLが含まれている場合、比較対象のカラムの値がリスト中の非NULL値のいずれとも一致しないとき、最終的な条件がUNKNOWNと評価される可能性があり、該当する行が選択されなくなります。これは意図しない結果（しばしば「何も返ってこない」）につながります。

問題ケース2: 比較対象のカラムにNULLが含まれる場合

WHERE nullable_column NOT IN (value1, value2) のように、NOT IN句の対象となるカラム自体がNULLを許容し、実際にNULL値を持つ行が存在する場合です。

例として、前述のsample_dataテーブルを使用します。

sql
+----+-------+
| id | value |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | NULL |
+----+-------+

ここで、「valueが ‘A’ または ‘B’ ではない行」を選択したいとします。クエリは以下のようになります。

sql
SELECT id, value
FROM sample_data
WHERE value NOT IN ('A', 'B');

このクエリは、valueが ‘C’ の行 (id=3) と、valueが NULL の行 (id=4) を返すことを期待するかもしれません。しかし、実行結果は以下のようになります。

sql
+----+-------+
| id | value |
+----+-------+
| 3 | C |
+----+-------+

valueがNULLの行 (id=4) が選択されません。なぜでしょうか？

WHERE value NOT IN ('A', 'B') という条件は、内部的には NOT (value = 'A' OR value = 'B') のように評価されます。

valueがNULLの行 (id=4) について、この条件がどのように評価されるかを見てみましょう。

  • id=4, value=NULL の場合:
    • NOT (NULL = 'A' OR NULL = 'B')
    • NULL = 'A' は UNKNOWN
    • NULL = 'B' は UNKNOWN
    • UNKNOWN OR UNKNOWN は UNKNOWN
    • NOT (UNKNOWN) は UNKNOWN
    • WHERE句の条件がUNKNOWNになるため、この行は選択されません。

結論: NOT IN句の対象となるカラムの値がNULLの場合、その行はNOT IN条件をUNKNOWNと評価するため、選択されません。多くの場合、「対象の値がNULLである行も除外リストに含まれない（つまり選択対象となる）」と期待するかもしれませんが、NOT IN句の仕様上、そのようにはなりません。これはNULLを比較対象から除外したい場合には都合が良いかもしれませんが、意図しないフィルタリングを引き起こす可能性もあります。

NULL値問題の回避策

NOT IN句におけるNULL値の問題を回避し、期待通りの結果を得るためには、以下のいずれかの方法を検討する必要があります。

  1. 比較対象のカラムがNULLでないことを明示的に指定する (IS NOT NULL)
    これが最もシンプルで推奨される方法です。WHERE句に AND column_name IS NOT NULL という条件を追加します。これにより、NULL値を持つ行はNOT IN句の評価の前に除外されるため、NULL値が原因でUNKNOWNになる問題を回避できます。

    sql
    SELECT id, value
    FROM sample_data
    WHERE value NOT IN ('A', 'B') -- 問題ケース2のクエリ
    AND value IS NOT NULL; -- この条件を追加
    このクエリを実行すると、valueが’C’の行(id=3)のみが選択されます。valueがNULLの行(id=4)はvalue IS NOT NULLで除外されます。これは問題ケース2において、valueが’A’や’B’ではない、かつNULLではない行を選択するという、より厳密な意図に合致します。

    もし、NOT INリストにNULLが含まれていて（問題ケース1）、かつリスト中の非NULL値以外すべて（NULL値自身を除く）を選択したい場合は、NOT INリストからNULLを除外し、かつ比較対象がNULLでないことを指定します。

    sql
    -- sample_data テーブルに対して、value が 'A' でも 'B' でもなく、かつ NULL でもない行を選択
    SELECT id, value
    FROM sample_data
    WHERE value NOT IN ('A', 'B') -- NULLをリストから削除
    AND value IS NOT NULL; -- IS NOT NULLを追加
    このクエリも結果は id=3, value=’C’ の行になります。これが多くのケースで期待される「リスト中の非NULL値のいずれにも一致しない」という条件を正確に表現する方法です。

  2. サブクエリを使う場合、サブクエリの結果からNULLを除外する
    NOT IN (SELECT subquery_column FROM ...) のようにサブクエリを使用している場合、サブクエリがNULLを返す可能性があります。このNULLがメインクエリのNOT INリストに含まれると、問題ケース1で見たような予期しない結果につながります。これを避けるには、サブクエリの中でNULLを除外します。

    sql
    -- 例2 (注文履歴のない顧客) のサブクエリが NULL を返す可能性がある場合
    -- orders テーブルの customer_id カラムが NULL を許容し、実際に NULL が含まれていると仮定
    SELECT customer_id, customer_name, city
    FROM customers
    WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL); -- サブクエリ内で NULL を除外
    サブクエリがNULLを返さないことが保証されていれば、この対応は不要です。しかし、サブクエリの対象カラムがNULLを許容する場合は、安全のためにIS NOT NULLを追加することを強く推奨します。

NULL値問題はNOT IN句の最も一般的な落とし穴です。この挙動を理解し、適切にIS NOT NULL句を使用することで、意図通りの正確な結果を得ることができます。

NOT IN句のパフォーマンスに関する考慮事項

NOT IN句は、特に大量のデータを扱う場合や、複雑なサブクエリと組み合わせて使用する場合に、パフォーマンスのボトルネックとなる可能性があります。パフォーマンスに影響を与える要因はいくつかあり、クエリを最適化するためにはそれらを理解することが重要です。

  1. リストまたはサブクエリの結果セットのサイズ:
    NOT IN句が比較する値のリスト（静的リストまたはサブクエリの結果セット）が大きいほど、クエリの実行にかかる時間は増加する傾向があります。データベースシステムは、メインクエリの各行に対して、そのカラムの値がリスト中の全ての値と一致しないかを確認する必要があります。リストのサイズが大きければ大きいほど、この比較回数が増加します。

  2. サブクエリのパフォーマンス:
    NOT IN (SELECT ...) の形式でサブクエリを使用する場合、サブクエリ自体の実行速度が全体のパフォーマンスに大きく影響します。サブクエリが遅い場合、メインクエリがどれだけ効率的であっても、全体のクエリは遅くなります。サブクエリ内のテーブルに適切なインデックスが張られているか、サブクエリ自体が最適化されているかを確認することが重要です。MySQLのオプティマイザは、NOT IN句を含むサブクエリを、より効率的な結合（JOIN）などに内部的に書き換えることがありますが、常に最適に変換されるとは限りません。

  3. インデックスの活用:
    NOT IN句の対象となるカラムにインデックスが張られている場合、MySQLはインデックスを利用して比較対象の値を素早く検索しようとします。これにより、パフォーマンスが向上する可能性があります。しかし、インデックスが常に最大限に活用されるとは限りません。特に、比較対象の値が多すぎる場合や、後述するNULL値が存在する場合、インデックスの使用効率が低下する可能性があります。

  4. NULL値とパフォーマンス:
    前述のNULL値の問題は、パフォーマンスにも影響を与える可能性があります。WHERE column_name NOT IN (...) だけでは、column_nameがNULLの行も考慮の対象となり、UNKNOWNとなる比較が発生します。これはインデックスの使用を妨げたり、処理を複雑にしたりする可能性があります。WHERE column_name NOT IN (...) AND column_name IS NOT NULL のように明示的にNULLを除外することで、オプティマイザがより効率的な実行計画を選択できるようになり、パフォーマンスが改善する場合があります。IS NOT NULL条件は、インデックスにおいてNULL以外の値を効率的に絞り込む手助けをすることがあります。

  5. 代替手段との比較:
    多くの場合、特に大きなテーブルや複雑な条件を扱う際に、NOT IN句よりもLEFT JOIN + IS NULLやNOT EXISTSの方がパフォーマンスに優れる傾向があります。これらの代替手段は、内部的な処理方式がNOT INとは異なるため、特定のシナリオでより効率的な実行計画を生成しやすいからです。パフォーマンスが重要な要件である場合は、これらの代替手段も検討し、EXPLAINステートメントを使用して実際の実行計画とパフォーマンスを比較することが不可欠です。

EXPLAINステートメントの活用:

クエリのパフォーマンスを分析し、ボトルネックを特定するためには、MySQLのEXPLAINステートメントを必ず使用してください。クエリの前にEXPLAINを追加して実行すると、MySQLがそのクエリをどのように実行するか（どのテーブルをどのような順序でアクセスするか、どのインデックスを使うか、何行スキャンするかなど）を示す実行計画が表示されます。

sql
EXPLAIN SELECT customer_id, customer_name, city
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

実行計画を分析することで、NOT IN句やサブクエリが非効率になっている箇所（例えば、大量の行をフルスキャンしている、適切なインデックスが使われていないなど）を発見し、クエリやデータベース構造（インデックス追加など）の改善点を見つけることができます。

NOT IN句の代替手段

NOT IN句と同じ目的、つまり「あるテーブルには存在するが、別のテーブル（または値のリスト）には存在しない行」を選択するために、MySQLでは他にいくつかの方法が利用できます。これらの代替手段は、NOT IN句のNULL値問題を回避できたり、パフォーマンス面で優れていたりすることがあります。ここでは、代表的な代替手段であるLEFT JOINとIS NULL、およびNOT EXISTSについて詳しく解説します。

1. LEFT JOIN と IS NULL

この方法は、一方のテーブル（左側のテーブル）には存在するが、もう一方のテーブル（右側のテーブル）には一致する行が存在しないレコードを見つけるためによく使用されます。

原理:

LEFT JOINは、左側のテーブルの全ての行と、右側のテーブルの結合条件に一致する行を結合します。左側のテーブルの行に対して、右側のテーブルに一致する行がない場合、右側のカラムにはNULLがセットされます。このNULLになった右側のカラムを利用して、「左側のテーブルには存在するが、右側のテーブルには存在しない」行を特定します。

構文:

sql
SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.column_name = t2.column_name
WHERE t2.column_name IS NULL;

ここで、
– table1: 存在することを確認したいテーブル（NOT INの対象となるメインテーブルに相当）
– table2: 存在しないことを確認したいテーブル（NOT INのサブクエリのテーブルに相当）
– t1.column_name = t2.column_name: 結合条件として使用するカラム

例6: LEFT JOIN + IS NULL を使って注文履歴のない顧客リストを取得

例2と同じ目的、「注文履歴のない顧客」を選択します。customersテーブルから、ordersテーブルにcustomer_idが存在しない顧客を見つけます。

sql
SELECT c.customer_id, c.customer_name, c.city
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id -- customer_id で結合
WHERE o.customer_id IS NULL; -- orders テーブルに一致する customer_id がない行を選択

このクエリは、customersテーブルの全ての行に対して、ordersテーブルをcustomer_idで結合しようとします。
– 顧客ID 1 (Alice): ordersテーブルに該当行あり。o.customer_id は NULL にならない。
– 顧客ID 2 (Bob): ordersテーブルに該当行なし。o.customer_id は NULL になる。
– 顧客ID 3 (Charlie): ordersテーブルに該当行あり。o.customer_id は NULL にならない。
– 顧客ID 4 (David): ordersテーブルに該当行なし。o.customer_id は NULL になる。
– 顧客ID 5 (Eve): ordersテーブルに該当行あり。o.customer_id は NULL にならない。

WHERE o.customer_id IS NULLによって、ordersテーブルに一致する行が見つからなかった（つまりo.customer_idがNULLになった）行、すなわち顧客ID 2と 4 の行が選択されます。

実行結果は例2のNOT INクエリと同じになります。

sql
+-----------+---------------+--------+
| customer_id | customer_name | city |
+-----------+---------------+--------+
| 2 | Bob | Osaka |
| 4 | David | Nagoya |
+-----------+---------------+--------+

LEFT JOIN + IS NULL の特徴:

  • NULL値の扱い: 結合条件に使用するカラム自体にNULLが含まれている場合でも、LEFT JOINは通常期待通りの結果を返します。結合条件 (ON c.customer_id = o.customer_id) において、NULLは他の値ともNULL自身とも一致しないため、customer_idがNULLの行は結合に失敗し、右側のカラムがNULLになります。ただし、WHERE o.customer_id IS NULLという条件では、結合に失敗した行のうち、ordersテーブル側のcustomer_idがNULLになった行のみが選択されます。通常、結合条件に使用するカラムは非NULL制約を持っていることが多いですが、もしo.customer_idがNULLを許容し、かつ一致しない行のo.customer_idがNULLであった場合でも、o.customer_id IS NULLの条件は真になります。これはNOT INのNULL問題とは異なり、より直感的な動作となることが多いです。
  • パフォーマンス: MySQLでは、LEFT JOINは最適化が進んでおり、特に大きなテーブル間で「存在しない」条件を探す場合にNOT INよりも高速な実行計画を生成することがあります。オプティマイザが効率的にインデックスを利用できる可能性が高いです。
  • 可読性: 結合の概念に慣れていないと、LEFT JOINとIS NULLの組み合わせが最初は少し分かりにくく感じるかもしれません。しかし、データベース開発者にとっては非常に一般的なパターンです。

2. NOT EXISTS

NOT EXISTSは、サブクエリが存在しない場合に真となる条件です。これは通常、相関サブクエリと組み合わせて使用されます。

原理:

WHERE NOT EXISTS (サブクエリ)という条件は、メインクエリの各行に対してサブクエリを実行し、そのサブクエリが1行でも結果を返せば条件は偽、結果を全く返さなければ条件は真となります。NOT EXISTSは、サブクエリが具体的な値を返すかどうかではなく、「存在するかどうか」のみを判定するため、サブクエリが返す値自体（NULLかどうか）は直接的な影響を与えません。

構文:

sql
SELECT t1.*
FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t1.column_name = t2.column_name);

ここで、
– table1: 存在することを確認したいテーブル
– table2: 存在しないことを確認したいテーブル
– t1.column_name = t2.column_name: メインクエリの行とサブクエリ内の行を関連付ける結合条件に似た条件。これが相関サブクエリである由縁です（サブクエリがメインクエリのカラムを参照している）。

サブクエリ内のSELECT 1は、実際に値を取得する必要はなく、「行が存在するかどうか」のみを知りたい場合に、リソースを節約するために慣習的に使用されます。SELECT *やSELECT t2.column_nameなど、何でも構いません。

例7: NOT EXISTS を使って注文履歴のない顧客リストを取得

例2と同じ目的、「注文履歴のない顧客」を選択します。customersテーブルから、ordersテーブルにcustomer_idが存在しない顧客を見つけます。

sql
SELECT c.customer_id, c.customer_name, c.city
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id);

このクエリは、customersテーブルの各行（例えば customer_id = 1 の行）に対して、サブクエリ SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id を実行します。
– c.customer_id = 1 の場合: サブクエリは SELECT 1 FROM orders o WHERE 1 = o.customer_id となり、ordersテーブルに customer_id が 1 の行が存在するため、結果を返します。EXISTSは真、NOT EXISTSは偽となります。
– c.customer_id = 2 の場合: サブクエリは SELECT 1 FROM orders o WHERE 2 = o.customer_id となり、ordersテーブルに customer_id が 2 の行が存在しないため、結果を返しません。EXISTSは偽、NOT EXISTSは真となります。
– … customer_id が 4 の場合も同様に NOT EXISTS は真となります。

結果として、NOT EXISTS条件が真になった行、すなわち顧客ID 2と 4 の行が選択されます。

実行結果は例2のNOT INクエリ、例6のLEFT JOIN + IS NULLクエリと同じになります。

sql
+-----------+---------------+--------+
| customer_id | customer_name | city |
+-----------+---------------+--------+
| 2 | Bob | Osaka |
| 4 | David | Nagoya |
+-----------+---------------+--------+

NOT EXISTS の特徴:

  • NULL値の扱い: EXISTS/NOT EXISTSはサブクエリが何かを返すかどうかの判定であり、サブクエリが返す値そのものを評価しないため、NOT IN句のようなNULL値問題は基本的に発生しません。サブクエリがNULLを含む行を返したとしても、それは「結果を返した」という事実には変わりなく、EXISTSは真となります。
  • パフォーマンス: NOT EXISTSは相関サブクエリを使うことが多いですが、MySQLはこれを効率的に処理できる場合があります。特に、サブクエリが条件を満たす最初の行を見つけた時点でそれ以上の検索を中止できるため、サブクエリが大量の行を返す可能性がある場合に有利になることがあります。多くのケースでNOT INより高速です。
  • 可読性: 相関サブクエリの概念に慣れが必要ですが、LEFT JOIN + IS NULLと同様に、一般的なパターンとして理解されています。

3つの方法の比較

特徴 NOT IN LEFT JOIN + IS NULL NOT EXISTS
目的 リストに含まれない行を選択 左テーブルにあり、右テーブルにない行 サブクエリが存在しない行を選択
NULL値問題 発生する（要注意！） 結合条件に依るが発生しにくい 発生しにくい
静的リスト 可能 不可能 不可能
サブクエリ 主に非相関サブクエリ 結合のためサブクエリではない 主に相関サブクエリ
パフォーマンス リスト/結果セットが大きいと低下しやすい 大規模データに強い傾向あり 大規模データに強い傾向あり
内部処理 比較の繰り返し、結合への変換など 外部結合後のフィルタリング 相関サブクエリの実行
可読性 シンプル（ただしNULL注意） やや慣れが必要 やや慣れが必要

どの方法を選ぶべきか？

  • リストが固定かつ短い場合: NOT IN句が最もシンプルで可読性が高いでしょう。ただし、NULL値の可能性がないか確認しましょう。
  • リストが動的（他のテーブルに基づく）でNULL値の可能性がある場合: LEFT JOIN + IS NULL または NOT EXISTS を強く推奨します。これらの方法はNULL値問題を自然に回避できます。
  • パフォーマンスが最優先の場合: データ量、インデックス、クエリの内容によって最適な方法は変わります。EXPLAINを使用してそれぞれの実行計画を確認し、最も効率的なものを選択してください。一般的にはLEFT JOIN + IS NULL や NOT EXISTS が大規模データで優位に立つことが多い傾向があります。
  • サブクエリの結果が非常に大きい可能性がある場合: NOT EXISTSが効率的になる可能性があります。
  • NULL値を厳密に扱いたい場合: LEFT JOIN + IS NULL や NOT EXISTS が適しています。NOT INを使う場合は、必ずIS NOT NULLを併用してください。

いずれの方法を選択するにしても、EXPLAINを使用して実行計画を確認し、実際のパフォーマンスを評価することが重要です。データベースのバージョンやデータ特性によって、最適な方法は変化する可能性があります。

NOT IN句を使う上でのベストプラクティスと注意点

これまでの解説を踏まえ、NOT IN句を安全かつ効果的に使用するためのベストプラクティスと注意点をまとめます。

  1. NULL値の扱いに細心の注意を払う: これがNOT IN句の最大の落とし穴です。

    • NOT INリストにNULLが含まれていないことを確認する。サブクエリを使用する場合は、サブクエリがNULLを返す可能性があるか確認し、必要に応じてWHERE subquery_column IS NOT NULLを追加して除外する。
    • NOT IN句の対象となるカラムがNULLを許容する場合、通常は AND column_name IS NOT NULL を追加して、NULL値を持つ行を明示的に除外する。これにより、意図しないフィルタリングやUNKNOWN評価による結果の欠落を防ぐことができる。
    • 原則として、NOT INを使用する場合は IS NOT NULL をセットで使う習慣をつけるのが安全です。

  2. サブクエリを使う場合は、サブクエリの結果セットを確認する:

    • サブクエリ単体で実行してみて、期待通りの値のリストが返されるか、特にNULLが含まれていないかを確認する。
    • サブクエリが大量のデータを返す場合、メインクエリのパフォーマンスに影響することを理解しておく。

  3. パフォーマンスが重要なクエリでは、代替手段と比較検討する:

    • 特に大規模なテーブルに対して「含まれない」条件を指定する場合、LEFT JOIN + IS NULL や NOT EXISTS の方が効率的である可能性が高い。
    • EXPLAINステートメントを使用して、NOT IN、LEFT JOIN + IS NULL、NOT EXISTSそれぞれの実行計画とコストを比較し、最適な方法を選択する。

  4. インデックスを適切に利用する:

    • NOT IN句の対象となるカラムや、サブクエリ、LEFT JOINの結合条件に使用されるカラムには、適切なインデックス（通常はB-treeインデックス）を作成することを検討する。これにより、値の検索や結合が高速化される可能性がある。
    • IS NOT NULL条件も、インデックスの利用を助けることがある。

  5. 静的なリストが非常に大きい場合は、工夫が必要:

    • もし除外したい静的な値のリストが数千、数万といったサイズになる場合、クエリ文字列が非常に長くなり、可読性や管理性が低下します。また、パフォーマンスも問題になる可能性があります。
    • このような場合は、一時テーブルにリストを格納し、その一時テーブルに対してNOT IN（または後述のLEFT JOINなど）を使用する、あるいはアプリケーション側でリストを処理するなどの工夫が必要になる場合があります。

  6. 可読性を考慮し、適切な方法を選ぶ:

    • シンプルで明確なクエリは、後々のメンテナンスが容易になります。
    • リストが短いならNOT IN、動的な除外条件でNULLが怖いならLEFT JOINやNOT EXISTSなど、状況に応じて最も意図が伝わりやすい方法を選択することも重要です。

これらの点を意識することで、NOT IN句を原因とする予期しないエラーやパフォーマンス問題を避け、データベース操作の信頼性と効率性を向上させることができます。

まとめ

本記事では、MySQLにおけるNOT IN句について、その基本的な使い方から、サブクエリとの連携、NULL値が引き起こす重要な問題とその回避策、パフォーマンスに関する考慮事項、そしてLEFT JOIN + IS NULLやNOT EXISTSといった代替手段との比較まで、幅広く詳細に解説しました。

NOT IN句は、「特定のリストに含まれない行を選択する」という要件をシンプルに記述できる便利な構文です。静的な値のリストに対しても、サブクエリを使って動的に生成されるリストに対しても使用できます。

しかし、NOT IN句を使用する上で最も注意すべき点は、NULL値の扱いです。 NOT INリストにNULLが含まれている場合、または比較対象のカラムがNULL値を持つ場合、予期せずUNKNOWNと評価され、本来取得すべき行が結果に含まれないという問題が発生する可能性があります。この問題に対処するためには、NOT IN句の対象となるカラムに対して IS NOT NULL 条件を併用するか、サブクエリがNULLを返さないようにサブクエリ内でフィルタリングすることが不可欠です。

また、パフォーマンスが重要な要件となる場合、特に扱うデータ量が多いケースでは、NOT IN句がボトルネックになる可能性があります。このような場合は、LEFT JOINとIS NULLを組み合わせる方法や、NOT EXISTSを使用する方法が、より効率的な実行計画を生成し、優れたパフォーマンスを発揮することが多いです。これらの代替手段は、NULL値問題が発生しにくいという利点も持ち合わせています。

どの方法を選択するにしても、MySQLのEXPLAINステートメントを利用してクエリの実行計画を確認し、実際のパフォーマンスを評価することが重要です。データ構造、データ量、クエリの具体的な内容によって最適な方法は異なります。

結論として、NOT IN句は適切に使用すれば強力なツールですが、NULL値の挙動を正確に理解し、必要に応じてIS NOT NULLを組み合わせることが不可欠です。より複雑なシナリオやパフォーマンスが求められる場合は、LEFT JOIN + IS NULLやNOT EXISTSといった代替手段も積極的に検討し、各方法の特性を理解した上で、状況に応じた最適なクエリを選択することが、効率的で信頼性の高いデータベース操作を実現するための鍵となります。

この記事が、あなたがMySQLでNOT IN句をより深く理解し、安全かつ効果的に活用するための一助となれば幸いです。

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

上部へスクロール