mysql - WHERE 条件与 NULL 无关，消除具有 NULL 值的行

Question

CREATE TABLE players (
  `userID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`userID`));

CREATE TABLE opponents (
  `userID` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`userID`));

CREATE TABLE relationship (
  `user1_userID` INT NOT NULL,
  `user2_userID` INT NOT NULL,
  `blocked` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`user1_userID`, `user2_userID`));

INSERT INTO players (`name`) 
VALUES ('alpha'), ('beta'), ('gamma'), ('delta');

INSERT INTO opponents (`name`) 
VALUES ('alpha'), ('beta');

INSERT INTO relationship (`user1_userID`, `user2_userID`, `blocked`)
VALUES (1,2, 1), (1,3, 1), (1,4, 1), (2, 1, 1), (2,3, 1), (3,4, 1);

SELECT o.userID, o.name, ur.*
FROM opponents o
LEFT JOIN players p
    ON (o.userID != p.userID)
LEFT JOIN relationship ur
    ON ((ur.user1_userID = p.userID AND ur.user2_userID = o.userID) OR 
       (ur.user1_userID = o.userID AND ur.user2_userID = p.userID)) 
-- WHERE (ur.blocked != 1)

如果我不设置 WHERE 条件，我会看到关系列的 NULL 行，即 beta 尚未阻止 delta。

但是当我取消注释 WHERE 条件时，我期望只保留这一行。但它也会被淘汰。为什么会发生这种情况以及如何正确编写查询？谢谢。

Answer 1

在 SQL 中，与某些编程语言不同，您可以使用等于 ( = ) 求值器来测试 NULL ，在 ANSI SQL你不能而且必须IS NULL或对面IS NOT NULL评估空值。这意味着如果您使用 =评估并且一行有 NULL value 甚至不会被考虑，至少 MySQL InnoDB 就是这种情况，一些RMBDS处理它们略有不同。

现在，对于您的具体情况，您可以像这样更改查询以获得所需的结果

SELECT o.userID, o.name, ur.*
FROM opponents o
LEFT JOIN players p
    ON (o.userID != p.userID)
LEFT JOIN relationship ur
    ON ((ur.user1_userID = p.userID AND ur.user2_userID = o.userID) OR 
       (ur.user1_userID = o.userID AND ur.user2_userID = p.userID)) 
WHERE (ur.blocked != 1 OR ur.blocked IS NULL)

已更新

因为我想有时我不是最好的解释者，归根结底我只是互联网上的一些人，这里有一些 mysql 文档的链接，应该有助于澄清一些事情。希望能帮助回答“真正的问题”

Comparison Functions and Oporators

Working With NULL VALUES

Problems With NULL

编辑

我想更好地解释我自己，值NULL无法通过 bool 运算符进行计算，因为 NULL是没有值，因为它没有值，所以既不能是 1 也不能是 0。所以在任何 bool 计算中它永远不会返回 false，例如 NULL=1和NULL!=1都是假的。现在我还想提到一些可能的替代解决方案，因为在数据库中，如果您想从数据库中获取准确的信息，正确处理空值非常重要。

在 MySQL除了 != 之外，您还可以使用 2 个函数这会给你想要的结果。

COALESCE(ur.blocked,0) != 1

COALESCE(value, ...)可以给定许多值，并将返回从左开始并评估右的第一个非 NULL 值。因此，在您的情况下，将评估 NULL如0 ，不等于 1并会返回您丢失的记录。

IFNULL(ur.blocked,0) != 1

IFNULL(Value1, Value2)如果不是NULL，该函数将返回第一个如果第一个值为NULL，则返回第二个值

希望这能帮助您处理NULL future 的值(value)观。