为什么 MySQL 的'NOT IN”会因跨多个表的 NULL 而失败，以及如何修复它？

MySQL 跨三表的“NOT IN”查询：陷阱和解决方案

在 MySQL 中，“NOT IN”运算符通常用于检索指定表中不存在的记录。但是，在使用多个表时，重要的是要意识到其潜在的陷阱。

“NOT IN”可能导致意外结果的一种情况是正在搜索的表可能包含 NULL 值。在比较“等级”和“评估”表中的数据的查询中遇到此问题。该查询旨在识别“grade”中与“evaluation”中的任何条目都不匹配的记录，但如果两个表中均不存在名称“JOHN”，则无法返回任何结果。

要避免这种情况问题，建议使用替代方法，例如 NOT EXISTS 或左连接，而不是“NOT IN”。这些方法不会受到 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 使用的三值逻辑引起的。当涉及 NULL 值时，查询将转换为：

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

，相当于：

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

由于表达式“status=NULL”的计算结果为 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)

以上是为什么 MySQL 的'NOT IN”会因跨多个表的 NULL 而失败，以及如何修复它？的详细内容。更多信息请关注PHP中文网其他相关文章！