空值(NULL)處理是 SQL 查詢中的高頻需求。各主流資料庫對空值處理函數的實現存在顯著差異。本文系統對比 SQL Server、Oracle、MySQL、PostgreSQL、Access 五大資料庫的函數語義、語法與最佳實踐。
| 資料庫 | 雙參數函數 | 多參數函數 | 標準 SQL 函數 | 語義 |
|---|---|---|---|---|
| SQL Server | ISNULL(expr, replacement) | ❌ | COALESCE(expr1, expr2, ...) | expr 為 NULL 返回 replacement |
| MySQL | IFNULL(expr, replacement) | ❌ | COALESCE(expr1, expr2, ...) | expr 為 NULL 返回 replacement |
| Oracle | NVL(expr, replacement) | ❌ | COALESCE(expr1, expr2, ...) | expr 為 NULL 返回 replacement |
| PostgreSQL | ❌ | ❌ | COALESCE(expr1, expr2, ...) | 返回第一個非 NULL 參數 |
| Access | ISNULL(expr, replacement) | ❌ | ❌ | expr 為 NULL 返回 replacement |
| SQLite | ❌ | ❌ | COALESCE(expr1, expr2, ...) | 返回第一個非 NULL 參數 |
ISNULL(expr, val) = 空值替換函數ISNULL(expr) = 判斷函數,返回 1/0 (TRUE/FALSE)SELECT ISNULL(NULL); -- 返回 1,非預期的替換值-- 所有主流資料庫均支援
COALESCE(expr1, expr2, expr3, ..., default_val)
-- 返回第一個非 NULL 的表達式值,全 NULL 返回 default_val
-- MySQL: 參數類型不一致時的隱性轉換
SELECT COALESCE(NULL, 1, 'abc'); -- 返回 '1' (字串,因 'abc' 優先級高)
SELECT COALESCE(NULL, 1, 2.5); -- 返回 2.5 (DECIMAL)
SELECT COALESCE(NULL, 'abc', 1); -- 返回 'abc' (字串)
-- SQL Server ISNULL: 返回類型強制跟隨第一個參數
SELECT ISNULL(NULL, 'abc'); -- 返回 'abc' (VARCHAR)
SELECT ISNULL(NULL, 1); -- 返回 1 (INT)
-- 若第一參數為 INT,第二參數為 VARCHAR 會報錯或截斷
-- 需求:單價 * (庫存 + 訂單中),訂單中可能為 NULL
-- SQL Server / Access
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products;
-- Oracle
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products;
-- MySQL / PostgreSQL / SQLite / 標準 SQL
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products;
-- 或 MySQL 專用
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products;
-- 標準 SQL (|| 操作符遇 NULL 則全 NULL)
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM users;
-- MySQL (CONCAT 忽略 NULL)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; -- NULL 參數會導致整體 NULL
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name FROM users;
-- SQL Server (+ 操作符遇 NULL 則全 NULL)
SELECT ISNULL(first_name, '') + ' ' + ISNULL(last_name, '') AS full_name FROM users;
-- 依優先級取值:用戶設定 → 部門預設 → 系統預設 → 硬編碼預設
SELECT COALESCE(
user_pref.theme,
dept_pref.theme,
sys_pref.theme,
'light'
) AS effective_theme
FROM users u
LEFT JOIN prefs user_pref ON u.id = user_pref.user_id
LEFT JOIN prefs dept_pref ON u.dept_id = dept_pref.dept_id
CROSS JOIN (SELECT theme FROM prefs WHERE scope='system') sys_pref;
SELECT
category,
AVG(COALESCE(price, 0)) AS avg_price_with_zero, -- NULL 視為 0
AVG(NULLIF(price, 0)) AS avg_price_exclude_zero, -- 0 視為 NULL
SUM(CASE WHEN price IS NULL THEN 1 ELSE 0 END) AS null_price_count
FROM products
GROUP BY category;
WHERE ISNULL(col, '') = '' 導致索引失效,改寫為 WHERE col IS NULL OR col = ''<=> -- NULL-safe equal: NULL <=> NULL 為 TRUE, NULL <=> 1 為 FALSE
SELECT * FROM t1 WHERE col <=> 'value'; -- 等同於 (col = 'value' OR (col IS NULL AND 'value' IS NULL))
跨資料庫開發時,建立「空值處理標準庫」:封裝 nvl()、coalesce() 等統一介面,底層根據方言路由至對應函數,實現 SQL 可移植性與業務邏輯分離。