用 COALESCE() 處理計算裡的 NULL

今天我們又要深入聊聊 NULL 的處理，還有一個超實用的 function —— COALESCE()。這個 function 可以很優雅地搞定你資料裡的 NULL 值。

來看個例子：你有一張員工資料表，但有些員工的薪水資訊是空的。如果我們想要幫所有人加薪會怎樣？肯定不會有好結果，因為你沒辦法對 NULL 做運算。如果你想把 NULL 薪水換成 0 呢？這時候 COALESCE() 就派上用場啦。

COALESCE() 是一個會回傳 傳進來的參數裡第一個不是 NULL 的值 的 function。如果所有值都是 NULL，那它就回傳 NULL。簡單說，就是：「給我第一個正常的值，拜託！」

語法

COALESCE(value1, value2, ..., value_n)

value1、value2、...、value_n 就是你要傳給 function 的參數。它會回傳第一個不是 NULL 的值。

COALESCE() 的使用範例

來看幾個例子。

範例 1：把 NULL 換成 0

假設我們有一張 salaries 表：

我們想算出薪水總和——這很簡單：

SELECT SUM(salary) AS total_salary
FROM salaries;

SUM() function 會自動忽略 NULL，所以沒問題。

但如果我們想算每個人都多發 1000 的薪水總和呢？

SELECT SUM(salary+1000) AS total_salary
FROM salaries;

這時結果就怪怪的了。最好的做法是直接用 COALESCE 把 NULL 換成 0。來看看怎麼做：

SELECT SUM(COALESCE(salary, 0)) AS total_salary
FROM salaries;

結果：

這樣比較穩也比較安全。

範例 2：把 NULL 換成預設值

假設我們有一張 students 表，裡面有名字和地址：

我們想把地址裡的 NULL 換成「未填寫」：

SELECT name, COALESCE(address, '未填寫') AS resolved_address
FROM students;

查詢結果：

範例 3：用多個值來替換

有時候 NULL 不只要換成一個值，而是要依序找一串值。比如說，我們想選擇名字、暱稱，或如果都沒有就用「沒名字」。users 表：

查詢：

SELECT user_id,
       COALESCE(first_name, short_name, '沒名字') AS display_name
FROM users;

結果：

COALESCE() 的實戰應用

實務上 COALESCE() 就像救生圈一樣，專門處理資料不完美的狀況。

來看看它怎麼幫你解決各種問題。

範例 1：替換文字欄位的值

原始 customers 表：

查詢：

SELECT name, COALESCE(address, '未填寫') AS address
FROM customers;

結果：

範例 2：報表資料預處理

原始 sales 表：

查詢：

SELECT SUM(COALESCE(price, 0)) AS total_sales
FROM sales;

結果：

用 COALESCE() 常見的錯誤

雖然 COALESCE() 看起來很簡單又萬用，但還是有幾個小陷阱要注意。

資料型態不一致。傳給 COALESCE() 的所有參數型態要能互相轉換。例如不能把字串和數字混在一起。

-- 錯誤
SELECT COALESCE(salary, '未填寫') FROM employees; 
-- salary 是數字欄位，'未填寫' 是文字。

忽略參數順序。COALESCE() 會回傳第一個不是 NULL 的值，所以參數順序很重要。