SQL COALESCE 函数 - SQL 教程

摘要：在本教程中，您将学习如何使用 SQL COALESCE() 函数来有效地处理 NULL。

SQL COALESCE 函数简介 #

在 SQL 中，COALESCE() 函数接受一个或多个参数，并返回第一个非 NULL 的参数。

以下是 COALESCE 函数的语法

COALESCE(argument1, argument2,...);Code language: SQL (Structured Query Language) (sql)

COALESCE 函数从左到右计算其参数，并返回第一个非 NULL 的参数。

如果所有输入参数都为 NULL，则 COALESCE 函数将返回 NULL。

基本的 SQL COALESCE 函数示例 #

以下示例展示了如何对数字 1、2 和 3 使用 COALESCE 函数

SELECT
  COALESCE(1, 2, 3) AS result;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 result
--------
      1Code language: SQL (Structured Query Language) (sql)

结果是 1，因为它是第一个非 NULL 的参数。

以下语句返回字符串 SQL，因为它是第一个非 NULL 的参数。

SELECT
  COALESCE(NULL, 'SQL', 'Tutorial') AS result;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 result
--------
 SQLCode language: SQL (Structured Query Language) (sql)

短路求值 #

如果除以零，您将会遇到一个错误

SELECT 1/0;Code language: SQL (Structured Query Language) (sql)

试一试

输出

ERROR:  division by zeroCode language: SQL (Structured Query Language) (sql)

但以下语句返回的是 1 而不是错误

SELECT
  COALESCE(1, 1 / 0) result;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 result
--------
      1Code language: SQL (Structured Query Language) (sql)

原因是 COALESCE 函数使用了短路求值。这意味着 COALESCE 函数在遇到第一个非 NULL 参数后，不会再计算其余的参数。

使用 SQL COALESCE 函数替代 NULL #

首先，创建一个新表，名为 bonuses，用于存储员工奖金

CREATE TABLE bonuses (
  employee_id INT PRIMARY KEY,
  amount DECIMAL(10, 2) NULL
);Code language: SQL (Structured Query Language) (sql)

试一试

有些员工可能没有奖金，因此 amount 列可以为 NULL。

其次，向 bonuses 表中插入一些行

INSERT INTO
  bonuses (employee_id, amount)
VALUES
  (101, 1000.00),
  (102, NULL),
  (103, 1500.00),
  (104, NULL),
  (105, 2000.00);Code language: SQL (Structured Query Language) (sql)

试一试

第三，在计算包括工资和奖金在内的总薪酬时，使用 COALESCE 函数将 NULL 替换为零

SELECT
  e.first_name,
  e.last_name,
  e.salary,
  e.salary + COALESCE(b.amount, 0) AS total_compensation
FROM
  employees e
  LEFT JOIN bonuses b ON e.employee_id = b.employee_id;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 first_name  |  last_name  |  salary  | total_compensation
-------------+-------------+----------+--------------------
 Neena       | Kochhar     | 17000.00 |           18000.00
 Lex         | De Haan     | 17000.00 |           17000.00
 Alexander   | Hunold      |  9000.00 |           10500.00
 Bruce       | Ernst       |  6000.00 |            6000.00
...Code language: SQL (Structured Query Language) (sql)

SQL COALESCE 和 CASE 表达式 #

COALESCE 函数在功能上等同于以下的 CASE 表达式

CASE
  WHEN (argument1 IS NOT NULL) THEN argument1
  WHEN (argument2 IS NOT NULL) THEN argument2
  ELSE argument3
ENDCode language: SQL (Structured Query Language) (sql)

以下查询使用了 CASE 表达式而不是 COALESCE 函数

SELECT
  e.first_name,
  e.last_name,
  e.salary,
  e.salary + CASE
    WHEN b.amount IS NULL THEN 0
    ELSE b.amount
  END AS total_compensation
FROM
  employees e
  LEFT JOIN bonuses b ON e.employee_id = b.employee_id;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 first_name  |  last_name  |  salary  | total_compensation
-------------+-------------+----------+--------------------
 Neena       | Kochhar     | 17000.00 |           18000.00
 Lex         | De Haan     | 17000.00 |           17000.00
 Alexander   | Hunold      |  9000.00 |           10500.00
 Bruce       | Ernst       |  6000.00 |            6000.00
...Code language: SQL (Structured Query Language) (sql)

摘要 #

使用 SQL COALESCE 函数从参数列表中返回第一个非 NULL 的参数。

测验 #

数据库 #

本教程是否有帮助？