SQL COALESCE 函數：有效處理 NULL 值

摘要：本教學將向您介紹 SQL COALESCE 函數，並展示如何在實際情境中應用此函數。

SQL COALESCE 函數簡介

COALESCE 函數接受多個參數，並返回第一個非 NULL 的參數。以下說明 COALESCE 函數的語法。

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

COALESCE 函數從左到右評估其參數。它會停止評估，直到找到第一個非 NULL 的參數。這意味著所有剩餘的參數都不會被評估。

如果所有參數都是 NULL，則 COALESCE 函數會返回 NULL。

以下語句返回 1，因為 1 是第一個非 NULL 的參數。

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

以下語句返回 Not NULL，因為它是第一個不評估為 NULL 的字串參數。

SELECT COALESCE(NULL,'Not NULL','OK'); -- return Not NULLCode language: SQL (Structured Query Language) (sql)

如果您使用以下語句

SELECT 1/0; -- division by zeroCode language: SQL (Structured Query Language) (sql)

您將會得到除以零的錯誤。

但是，以下語句返回 1 並且不會產生任何錯誤

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

這是因為 COALESCE 函數是短路評估。在找到第一個非 NULL 的參數後，它會停止評估剩餘的參數。

幾乎所有關聯式資料庫系統都支援 COALESCE 函數，例如 MySQL、PostgreSQL、Oracle、Microsoft SQL Server、Sybase。

請注意，COALESCE 函數是 NVL 函數中最通用的函數，可以用來替代 NVL 函數。

SQL COALESCE 範例

假設我們有一個具有以下結構和資料的 products 表格

CREATE TABLE products (
	ID INT PRIMARY KEY,
	product_name VARCHAR(255) NOT NULL,
	product_summary VARCHAR(255),
	product_description VARCHAR(4000) NOT NULL,
	price NUMERIC (11, 2) NOT NULL,
	discount NUMERIC (11, 2),
	CHECK (net_price >= discount)
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO products (
	ID,
	product_name,
	product_summary,
	product_description,
	price,
	discount
)
VALUES
	(
		1,
		'McLaren 675LT',
		'Inspired by the McLaren F1 GTR Longtail',
		'Performance is like strikin and the seven-speed dual-clutch gearbox is twice as fast now.',
		349500,
		1000
	),
	(
		2,
		'Rolls-Royce Wraith Coupe',
		NULL,
		'Inspired by the words of Sir Henry Royce, this Rolls-Royce Wraith Coupe is an imperceptible force',
		304000,
		NULL
	),
	(
		3,
		'2016 Lamborghini Aventador Convertible',
		NULL,
		'Based on V12, this superveloce has been developed as the Lamborghini with the sportiest DNA',
		271000,
		500
	);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

使用 SQL COALESCE 替換 NULL 值

當處理資料庫表格中的資料時，您經常會使用 COALESCE 函數來為 NULL 值替換預設值。

假設您必須在網頁上顯示 products 表格中的所有資訊。某些產品可能沒有摘要，但其他產品可能有。

在這種情況下，您可以使用 COALESCE 函數返回產品摘要，如果沒有提供產品摘要，則從產品描述中取得前 50 個字元。

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		LEFT (product_description, 50)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

您可以使用 CONCAT 函數將 (…) 新增到摘錄的末尾，使其對於使用者來說更有意義，他們閱讀的文字只是摘錄，如果他們點擊「閱讀更多」連結，則會有更多內容。

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		CONCAT(
			LEFT (product_description, 50),
			'...'
		)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

在運算式中使用 SQL COALESCE 函數

假設您需要計算所有產品的淨價，並且您提出了以下 查詢

SELECT 
    id, 
    product_name, 
    (price - discount) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Rolls-Royce Wraith Coupe 的淨價為 NULL。這是因為此產品的折扣為 NULL，當您在計算中使用此 NULL 值時，它會產生 NULL 值。

要解決此問題，您可以將 discount 欄位中的所有 NULL 值更新為 0。

UPDATE products 
SET 
    discount = 0
WHERE
    discount IS NULL;Code language: SQL (Structured Query Language) (sql)

或者您可以使用 COALESCE 函數，如下所示

SELECT 
    id, 
    product_name, 
    price,
    discount,
    (price - COALESCE(discount,0)) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

現在已正確計算淨價。

SQL COALESCE 和 CASE 運算式

COALESCE 函數是 CASE 運算式的語法糖。這表示運算式

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

可以使用以下 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 運算式重寫從價格和折扣計算淨價的查詢，如下所示

SELECT 
    id,
    product_name,
    price,
    discount,
    (price - 
      CASE
        WHEN discount IS NOT NULL THEN discount
        ELSE 0
    END) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

該查詢返回與使用 COALESCE 函數的查詢相同的結果。

在本教學中，您已學習如何使用 SQL COALESCE 函數來處理資料庫表格中的 NULL 值。