複数のテーブルにわたる NULL により MySQL の「NOT IN」が失敗するのはなぜですか? それを修正するにはどうすればよいですか?

3 つのテーブルにわたる MySQL "NOT IN" クエリ: 落とし穴と解決策

MySQL では、"NOT IN" 演算子は一般的に次の目的で使用されます。指定したテーブルに存在しないレコードを取得します。ただし、複数のテーブルを操作する場合は、潜在的な落とし穴に注意することが重要です。

「NOT IN」が予期しない結果につながる可能性があるシナリオの 1 つは、検索対象のテーブルに NULL 値が含まれている可能性がある場合です。この問題は、「グレード」テーブルと「評価」テーブルのデータを比較するクエリで発生しました。クエリは、「評価」のどのエントリにも一致しない「グレード」のレコードを識別することを目的としていましたが、名前「JOHN」が両方のテーブルに存在しない場合、結果は返されませんでした。

これを回避するにはこの問題が発生した場合は、「NOT IN」の代わりに NOT EXISTS や左結合などの代替方法を使用することをお勧めします。これらのアプローチでは、NULL 値によってクエリが中断される可能性はありません。

さらに、WHERE 句を使用してテーブルを結合する従来の構文ではなく、明示的な結合を使用することをお勧めします。明示的な結合により、より明確になり、潜在的なパフォーマンスの問題が回避されます。

NULL 値を扱う場合の "NOT IN" の欠点をさらに詳しく説明するために、次の例を考えてみましょう:

テーブル構造:

CREATE TABLE mStatus (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(10) NOT NULL
);
INSERT INTO mStatus (status) VALUES ('single'),('married'),('divorced'),('widow');

CREATE TABLE people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fullName VARCHAR(100) NOT NULL,
  status VARCHAR(10) NULL
);

チャンク1:

TRUNCATE TABLE people;
INSERT INTO people (fullName, status) VALUES ('John Henry','single');
SELECT * FROM mStatus WHERE status NOT IN (SELECT status FROM people);

期待される結果: 3 行 (期待通り)

チャンク 2:

TRUNCATE TABLE people;
INSERT INTO people (fullName, status) VALUES ('John Henry','single'),('Kim Billings',NULL);
SELECT * FROM mStatus WHERE status NOT IN (SELECT status FROM people);

予想外結果: 行は返されません

この予期しない動作は、MySQL で使用される 3 値のロジックが原因で発生します。 NULL 値が含まれる場合、クエリは次のように変換されます:

status NOT IN ('single', 'married', 'widowed', NULL)

。これは次と同等です:

NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)

式 "status=NULL" は UNKNOWN と評価されるため、式全体は次のようになります。 UNKNOWN、すべての行がフィルタリングされます

解決策:

この問題を回避するには、左結合などの代替アプローチを使用するか、NOT EXISTS:

SELECT s.status
FROM mStatus s
LEFT JOIN people p ON p.status=s.status
WHERE p.status IS NULL

または

SELECT s.status
FROM mStatus s
WHERE NOT EXISTS (SELECT 1 FROM people p WHERE p.status=s.status)

以上が複数のテーブルにわたる NULL により MySQL の「NOT IN」が失敗するのはなぜですか? それを修正するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。