COALESCE(expr1, expr2, ..., exprN) 回傳參數清單中**第一個非 NULL** 的運算式。本文展示兩大進階場景:動態姓名拼接與多薪資制統一計算,並示範如何封裝為函數與計算欄優化。
| FirstName | MiddleName | LastName |
|---|---|---|
| John | A. | MacDonald |
| Franklin | D. | Roosevelt |
| Madonna | NULL | NULL |
| Cher | NULL | NULL |
| Mary | NULL | Weilage |
SELECT FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + COALESCE(LastName, '') FROM Persons;
問題: 無中間名/姓氏時會產生多餘空格 (如 James Bond 雙空格)。
SELECT LTRIM(FirstName + ' ' + COALESCE(MiddleName, '') + ' ') + COALESCE(LastName, '') FROM Persons;
結果:James Bond、John A. MacDonald、Madonna、Cher、Mary Weilage。
CREATE FUNCTION dbo.GetFullName
(
@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;
GO
-- 調用
SELECT dbo.GetFullName('James', NULL, 'Bond'); -- James Bond
SELECT dbo.GetFullName('Cher', NULL, NULL); -- Cher
SELECT dbo.GetFullName('John', 'F.', 'Kennedy'); -- John F. Kennedy
| EmpID | HourlyWage | HoursPerWeek | PieceRate | PiecesThisWeek | WeeklySalary | Commission |
|---|---|---|---|---|---|---|
| 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.00 |
SELECT EmployeeID,
COALESCE(HourlyWage * HoursPerWeek, 0) +
COALESCE(PieceRate * PiecesThisWeek, 0) +
COALESCE(WeeklySalary + Commission, 0) AS TotalPayment
FROM Payroll;
EmployeeID TotalPayment 1 720.00 2 1600.00 3 800.00 4 1100.00
ALTER TABLE Payroll
ADD TotalPayment AS
COALESCE(HourlyWage * HoursPerWeek, 0) +
COALESCE(PieceRate * PiecesThisWeek, 0) +
COALESCE(WeeklySalary + Commission, 0) PERSISTED;
-- 可直接建立索引
CREATE INDEX IX_Payroll_TotalPayment ON Payroll(TotalPayment);
此後 SELECT * FROM Payroll 即含預計算欄位,查詢零運算開銷。
| 特性 | COALESCE | ISNULL | CASE |
|---|---|---|---|
| 參數數量 | 不限 (≥2) | 固定 2 | 不限 |
| 標準相容 | ANSI SQL 標準 | T-SQL 專有 | ANSI SQL |
| 回傳型別 | 所有運算式最高優先級 | 第一個運算式型別 | 所有結果最高優先級 |
| 短路求值 | 是 | 是 | 是 |
| 可作為計算欄 | 是 | 是 | 是 (需 PERSISTED) |
WHERE Col = @Val OR (Col IS NULL AND @Val IS NULL) 或應用層處理。