SQL Server COALESCE() 函數進階應用與技巧

COALESCE() 函數接受一系列運算式，回傳第一個非 NULL 的值；若所有運算式皆為 NULL，則回傳 NULL。這特性讓它在處理「可選欄位組合」、「多來源資料取捨」、「動態計算」等情境極為實用。以下透過兩個實戰案例展示其靈活用法。

案例一：優雅拼接人名（避免多餘空格）

假設 Persons 表有 FirstName、MiddleName、LastName 三欄，資料如下：

• John A. MacDonald
• Franklin D. Roosevelt
• Madonna（無中間名、姓氏）
• Cher（僅單名）
• Mary Weilage（無中間名）

基本寫法

SELECT FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + COALESCE(LastName, '')
FROM Persons;

但這會導致 James Bond 變成 James Bond（兩個空格），因為 MiddleName 為 NULL 時，'' + ' ' + '' 仍產生一個空格。

改良版：封裝為函數並修正空格

CREATE FUNCTION dbo.WholeName
(
    @first NVARCHAR(50),
    @middle NVARCHAR(50),
    @last NVARCHAR(50)
)
RETURNS NVARCHAR(150)
AS
BEGIN
    DECLARE @Result NVARCHAR(150);
    SELECT @Result = LTRIM(@first + ' ' + COALESCE(@middle, '') + ' ') + COALESCE(@last, '');
    RETURN @Result;
END;

呼叫範例：

SELECT dbo.WholeName('James', NULL, 'Bond')   -- James Bond
UNION SELECT dbo.WholeName('Cher', NULL, NULL) -- Cher
UNION SELECT dbo.WholeName('John', 'F.', 'Kennedy'); -- John F. Kennedy

案例二：彈性薪資計算（多種薪資結構統一查詢）

員工薪資結構不一：時薪、計件、週薪+提成。建立示範表 PayDay：

EmployeeID | HourlyWage | HoursPerWeek | AmountPerPiece | PiecesThisWeek | WeeklySalary | CommissionThisWeek
1          | 18.00      | 40           | NULL           | NULL           | NULL         | NULL
2          | NULL       | NULL         | 4.00           | 400            | NULL         | NULL
3          | NULL       | NULL         | NULL           | NULL           | 800.00       | NULL
4          | NULL       | NULL         | NULL           | NULL           | 500.00       | 600

單一查詢計算總薪資

SELECT EmployeeID,
       COALESCE(HourlyWage * HoursPerWeek, 0) +
       COALESCE(AmountPerPiece * PiecesThisWeek, 0) +
       COALESCE(WeeklySalary + CommissionThisWeek, 0) AS Payment
FROM PayDay;

結果：

EmployeeID | Payment
1          | 720.00
2          | 1600.00
3          | 800.00
4          | 1100.00

進階：建立計算欄位（Computed Column）自動維護

ALTER TABLE PayDay
ADD Payment AS
    COALESCE(HourlyWage * HoursPerWeek, 0) +
    COALESCE(AmountPerPiece * PiecesThisWeek, 0) +
    COALESCE(WeeklySalary + CommissionThisWeek, 0);

之後直接 SELECT * FROM PayDay 即可看到即時計算的 Payment，無需在每個查詢重複邏輯，且效能優於視圖或函數調用。

總結

• COALESCE() 可簡化「取第一個非空值」的邏輯，程式碼更乾淨。
• 搭配 LTRIM/RTRIM 可解決字串拼接的空格殘留問題。
• 用於計算欄位可將業務邏輯下沉至資料層，提升查詢效能與維護性。
• 實務上常見於動態查詢、報表彙總、多來源資料整合等場合。