阅读 139

SQL 查询中的 NULL 值

本文通过各种 SQL 小例子,解释 NULL 值的用途和带来的问题。

英语原文地址:mitchum.blog/null-values…

作者:MITCHUM

翻译:高行行

参考翻译文章:blog.csdn.net/lnotime/art…

小结:

  • SQL 里的 NULL 和其他编程语言里的 NULL 是完全不同的东西
  • 在 SQL 中 NULL 为未知

翻译水平有限,可能存在翻译不准确的地方,尽情谅解。

今天的帖子是关于 SQL 中的 NULL 值的,由我的朋友兼数据库向导 Kaley 提供。如果你想了解有关 SQL,Oracle 数据库以及使查询运行更快的更多信息,请访问他的网站


这是一个使很多萌新开发人员陷入困境的话题-SQL 查询中 NULL 值的概念。

每当你向数据库发出SQL查询时……你想知道一列中是否包含 NULL 值……编写查询以查到结果的正确方式是什么?

你应该使用这样的查询吗?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN = NULL
复制代码

要么!你应该使用这样的查询吗?

SELECT * FROM SOME_TABLE
WHERE SOME_COLUMN IS NULL
复制代码

…答案是,你应该使用第二个查询(SOME_COLUMN IS NULL)。

下图为实际的查询例子 🌰

为什么呢?

为什么其他的比较都不用 IS 关键字呢?

如果我们想知道一个字段是否等于 1,我们可以使用如下的 WHERE 子句:

WHERE SOME_COLUMN = 1
复制代码

那么为什么我们在IS关键字上使用 NULL 值呢?为什么我们需要区别对待 NULL ?

答案是这样的:在 SQL 中,NULL 表示“未知”的概念 _ _(因此 NULL 值表示“未知”值)。

1. Null 为未知

在大多数数据库中,NULL 和空字符串(由双撇号 "" 或 '' 表示)之间存在差异。

但是,并非所有数据库都这样:例如,Oracle 数据库不允许你使用空字符串。任何时候 Oracle 数据库看到一个空字符串,它都会自动将空字符串转换为 NULL 值。

但是,对于大多数其他数据库,NULL 值与空字符串的处理方式不同:

  • 空字符串被视为没有值的已知值**。**
  • 将 NULL 值视为未知值

举个例子,就好像问:美国总统西奥多·罗斯福的中间名是什么?

  • 一种答案可能是:“嗯,我不知道西奥多·罗斯福的中间名是什么。”(此想法可以由 Theodore Roosevelt 的记录的 MIDDLE_NAME 列中的 NULL 值表示,即中间名字段为 NULL)
  • 另一种答案可能是**“西奥多·罗斯福总统实际上没有中间名。他的父母从未给他起过中间名,我知道的事实就是西奥多·罗斯福(Theodore Roosevelt)没有中间名。 **(你可以通过在 MIDDLE_NAME 列中输入一个空字符串或 '' 来表示,即中间名字段为空字符串)

Oracle 数据库是最显著的例外,其中这两个值实际上都将由 NULL 表示-除 Oracle 以外的大多数数据库对 NULL 和空字符串的处理方式都非常不同。

只要你记得 NULL 值代表一个未知值,那么这将有助于你编写 SQL 查询,并帮助你解决使用 NULL 值可能遇到的一些棘手情况。

例如,如果你要使用这样的 WHERE 子句查询:

SELECT * FROM SOME_TABLE
WHERE 1 = 1
复制代码

该查询将返回行(假设 SOME_TABLE 不是空表!),因为表达式“ 1 = 1” 可证明是 true 的……它可以被证明是正确的。

如果我要说:

SELECT * FROM SOME_TABLE
WHERE 1 = 0
复制代码

然后数据库将看到此情况,并将“ 1 = 0”评估为 false(这意味着该查询将_永远不会_返回任何行)。

但是如果我要说:

SELECT * FROM SOME_TABLE
WHERE 1 = NULL
复制代码

数据库基本上是这样的:“我不知道这两个值(1 和我们的黑盒 NULL 值)是否相等”……因此它不返回任何记录。

2. 三值逻辑

当 SQL 查询中有 WHERE 子句时,它可以具有三种不同结果之一:

  • true(它将返回行)
  • false(不会返回行)
  • NULL(未知也不会返回行)

你可能会想,“好吧,既然数据库对这两个值的处理完全相同,我为什么要关心 false 和 null 之间的区别?”

好吧,让我告诉你哪里可能遇到麻烦:让我们介绍一下 NOT() 条件。

如果你要说:

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 1)
复制代码

然后,数据库首先要求值 1 = 1,然后说:“好吧,那显然是对的。”

但是随后它将对其应用 NOT() 条件。“当 true 被 NOT() 修饰时,它变成了 false……所以 NOT() 条件导致我们的 WHERE 子句在这里是 false 的。”

因此,上面的查询不会返回任何记录。

但是,如果你要说:

SELECT * FROM SOME_TABLE
WHERE NOT(1 = 0)
复制代码

然后,数据库首先计算表达式 1 = 0,并说:“那显然是 false 的。”

但是然后它将应用 NOT() 条件,这将给我们相反的结果,因此它变为 true

因此此查询将返回记录!

如果我发出以下查询怎么办?

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)
复制代码

数据库首先要评估 1 = NULL。(请记住,它将把 NULL 当作一个未知值!)

它会说:“我不能说 1 是否等于 NULL,因为我不知道 NULL(未知)值是什么。”

因此,它不会产生 true 的结果也不会产生 false 的结果 – 而是会产生 NULL(未知)结果。

NULL 结果将由 NOT() 运算符修饰。

每当你使用 NULL 并将其置于 NOT() 条件时……结果就是另一个 NULL!(未知的反面是……嗯……另一个未知)。

因此,NOT() 运算符对 null 条件不做任何事情。

所以这些查询中的……

SELECT * FROM SOME_TABLE
WHERE NOT(1 = NULL)
复制代码
SELECT * FROM SOME_TABLE
WHERE 1 = NULL
复制代码

…将不返回任何记录…即使它们是相反的!

3. NULL 和 NOT IN

如果我使用 WHERE 子句发出这样的查询:

SELECT * FROM SOME_TABLE
WHERE 1 IN (1, 2, 3, 4, NULL)
复制代码

…那么显然 WHERE 子句将是 true 的,由于 1 在我们的 IN 列表中,所以该查询将返回记录…

但是如果我要说:

SELECT * FROM SOME_TABLE
WHERE 1 NOT IN (1, 2, 3, 4, NULL)
复制代码

那么显然这将是 false 的,该查询将永远不会返回记录,因为数字 1 出现在我们的 IN 列表中,并且我们说“ NOT IN”…

现在,如果我要说这样的话怎么办?

SELECT * FROM SOME_TABLE
WHERE 5 NOT IN (1, 2, 3, 4, NULL)
复制代码

此 WHERE 子句将永远不会返回任何记录,因为它不是真正的可证明(它不能被证明是 true 的)。数字 5 没有明确出现在“ IN”列表中 - 但是 5 可能在我们的“黑盒” NULL 值内(数据库不一定知道 NULL 的值是什么)。

这将产生 NULL 结果(表示未知结果),因此 WHERE 子句永远不会返回任何记录。

这就是为什么将 NULL 值等效为未知值很重要的原因 - 每当你编写复杂的SQL查询时,它都会为你提供帮助。

希望你现在已经准备好处理 SQL 查询中的 NULL 值!有关 SQL,Oracle 数据库以及使查询运行更快的更多信息,请访问  blog.tuningsql.com

资料

实际例子 🌰的 SQL 文件

CREATE TABLE `user` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(32) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`user`(`id`, `name`, `age`, `sex`, `password`) VALUES (2, '小白', 1, '0', '333');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `sex`, `password`) VALUES (3, 'white', 12, '0', '111');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `sex`, `password`) VALUES (4, 'white', NULL, '0', '222');
复制代码

参考文章

神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !

关注下面的标签,发现更多相似文章
评论