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 的引數。

測驗 #

資料庫 #

本教程是否有幫助？