MySQL 学习笔记（11）— NULL 与逻辑运算符、NULL 空值比较、空值排序、空值函数、GROUP BY 子句和 DISTINCT运算符的空值

最新推荐文章于 2025-03-05 11:52:11 发布

wohu007

最新推荐文章于 2025-03-05 11:52:11 发布

阅读量3.4k

点赞数 1

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/wohu1104/article/details/105418756

版权

MySQL 专栏收录该内容

19 篇文章

订阅专栏

参考链接：

https://gitbook.cn/gitchat/column/undefined/topic/5db92b4ea9c3a53bc3800efc

1. 空值与三值逻辑

SQL 中的空值（ NULL ）是一个特殊的值，代表了缺失/未知的数据或者不适用的情况。对于大多数的编程语言而言，逻辑运算的结果只有两种情况：真（ Ture ）或者假（ False ）。但是对于 SQL 而言，逻辑运算结果存在三种情况：真、假或者未知（ Unknown ）。对于 SQL 查询中的 WHERE 条件，只有结果为真的数据才会返回，结果为假或者未知都不会返回。

因此， SQL 中的逻辑运算符 AND 、 OR 以及 NOT 的结果也存在三种情况。

1.1 AND 操作符的逻辑真值表

AND 操作符	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

AND 操作符只有当两边的结果都为真时，最终结果才为真；否则结果为假或者未知，意味着不返回结果。

1.2 OR 操作符的逻辑真值表

OR 操作符	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

OR 操作符只要有一个结果为真，最终结果就为真；否则结果为假或者未知，意味着不返回结果。

1.3 NOT 操作符的逻辑真值表

	NOT 操作符
TRUE	FALSE
FALSE	TRUE
NULL	NULL

NOT 操作符用于取反操作，对于未知结果取反结果还是未知。

2. 空值比较与运算

任何值与 NULL 值进行比较时，结果无法确认是真还是假。以下比较运算的结果都是未知：

NULL = 0
NULL != 0
NULL > 1
NULL = NULL
NULL != NULL

NULL 等于 NULL 的运算结果未知， NULL 不等于 NULL 的运算结果也未知。因此，为了判断某个值是否为空，SQL 提供了 IS NULL 和 IS NOT NULL 谓词：

另外，当表达式或者函数涉及 NULL 值时，通常结果也是 NULL 值。例如，以下运算的结果都为空：

SELECT 100 + NULL, UPPER(NULL)
  FROM employee
 WHERE emp_id = 1;

100 + NULL|UPPER(NULL)|
----------|-----------|
          |           |

不过也存在一些例外，比如：

SELECT CONCAT('hello', NULL)
  FROM employee
 WHERE emp_id = 1;

该查询只有在 MySQL 返回 NULL ， CONCAT 函数在 Oracle 、 SQL Server 以及 PostgreSQL 中将 NULL 当作空字符串处理，返回字符串“hello”。由于不同数据库采取了不同的实现，因此在使用时这些函数时需要小心。

3. 空值排序

由于 SQL 标准没有提出明确要求空值的排序规则，导致在不同的数据库中存在两种空值排序方式：

Oracle 和 PostgreSQL ，升序时空值排在最后，降序时空值排在最前；同时支持使用 NULLS FIRST 和 NULLS LAST 指定空值的顺序；
MySQL 和 SQL Server ，升序时空值排在最前，降序时空值排在最后。

4. 空值函数

SQL 中定义了两个与空值转换相关的函数： NULLIF 和 COALESCE 。

4.1 NULLIF

NULLIF(expr1, expr2) 函数接受 2 个参数，如果第一个参数等于第二个参数，返回 NULL ；否则，返回第一个参数的值。

SELECT NULLIF(1, 2),
       NULLIF(3, 3)
  FROM employee
 WHERE emp_id = 1;

NULLIF(1, 2)|NULLIF(1, 1)|
------------|------------|
           1|      [NULL]|

因为 1 不等于 2，该查询的第一列结果为 1；因为 3 等于 3，第二列结果为 NULL 。

NULLIF 函数的一个常见用途是防止除零错误：

-- 除零错误
SELECT *
  FROM employee
 WHERE 1 / 0 = 1;

-- 避免除零错误
SELECT *
  FROM employee
 WHERE 1 / NULLIF(0 , 0) = 1;

第一个查询中被除数为 0，出现除零错误（MySQL 可能不会提示错误）；第二个查询使用 NULLIF 函数将被除数 0 转换为 NULL ，整个结果为 NULL 。

4.2 COALESCE

COALESCE(expr1, expr2, expr3, ...) 函数接受一个参数列表，并且返回第一个非空的参数；如果所有参数都为空，返回 NULL 。

以下示例用于查询员工的总收入：

SELECT emp_name,
       salary*12 + bonus AS "年收入",
       salary*12 + COALESCE(bonus, 0) AS "年收入"
  FROM employee
 WHERE emp_id <= 6;

emp_name|年收入     |年收入    |
--------|---------|---------|
刘备      |370000.00|370000.00|
关羽      |322000.00|322000.00|
张飞      |298000.00|298000.00|
诸葛亮    |296000.00|296000.00|
黄忠      |   [NULL]| 96000.00|
魏延      |   [NULL]| 90000.00|

该查询中的第三列利用 COALESCE 函数将奖金为空的数据转换为 0，得到了正确的年收入。

4.3 专有函数

除了 SQL 标准中定义的表达式之外，许多数据库还实现了一些类似的扩展函数：

Oracle 提供了 NVL(expr1, expr2) 以及 NVL2(expr1, expr2, expr3) 函数；
MySQL 提供了 IFNULL(expr1, expr2) 以及 IF(expr1, expr2, expr3) 函数；
SQL Server 提供了 ISNULL(expr1, expr2) 函数。

MySQL 提供了 IFNULL(expr1, expr2) 函数。该函数用于返回第一个非空的参数值，相当于只有两个参数的 COALESCE 函数。以下查询也返回员工的总收入：

-- MySQL 实现
SELECT emp_name,
       salary*12 + IFNULL(bonus, 0) AS "年收入"
  FROM employee
 WHERE emp_id <= 6;

MySQL 提供了类似于 NVL2 的 IF(expr1, expr2, expr3) 函数。它接受三个参数，如果第一个参数为真（expr1 <> 0 并且 expr1 不为空），返回第二个参数的值；否则，返回第三个参数的值。以下查询同样返回员工的总收入：

-- MySQL 实现
SELECT emp_name,
       IF(bonus, salary*12 + bonus, salary*12) AS "年收入"
  FROM employee
 WHERE emp_id <= 6;

5. 分组与聚合函数中的空值

GROUP BY 子句、 DISTINCT 运算符将所有的空值视为相同，因此将它们分为一组。虽然许多员工没有奖金，以下查询的结果中只有一个 NULL 值：

SELECT DISTINCT bonus
  FROM employee;

SELECT bonus
  FROM employee
 GROUP BY bonus;

该查询的结果如下：

bonus   |
--------|
10000.00|
 8000.00|
  [NULL]|
 5000.00|
 6000.00|
 2000.00|
 1500.00|

6. NOT IN 中的空值

IN 操作符用于查询位于列表之中的数据， NOT IN 用于查询不位于列表中的数据。以下是一个 IN 操作符的示例：

SELECT emp_id
  FROM employee
 WHERE emp_id IN (1, 2, 3, NULL);

emp_id|
------|
     1|
     2|
     3|

该查询的结果返回了 3 条记录，因为他们的员工编号位于列表之中。接下来使用 NOT IN 查询不在列表中的数据：

SELECT emp_id
  FROM employee
 WHERE emp_id NOT IN (1, 2, 3, NULL);

emp_id|
------|

该查询没有返回任何结果。原因在于 IN 操作符使用等号（ = ）依次与列表中的数据进行比较，该查询等价于以下形式：

SELECT emp_id
  FROM employee
 WHERE emp_id != 1
   AND emp_id != 2
   AND emp_id != 3
   AND emp_id != NULL;

任何值与 NULL 比较的结果都是未知；因此没有数据满足该条件，也就没有返回结果。

为了避免各种情况下空值可能带来的问题，可以利用 SQL 提供的空值处理函数将 NULL 值转换为其他数据。