您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 左連接無匹配行時設置默認值：COALESCE 與 CASE 實戰模式

編輯：MySQL 綜合教學

SQL 左連接無匹配行時設置默認值：COALESCE 與 CASE 實戰模式

左連接 (LEFT JOIN) 時，右表無匹配行會返回 NULL。業務常需將這些 NULL 轉為業務默認值 (如狀態碼 999 表示「無關聯」)。本文對比 COALESCE、IFNULL、CASE 三種寫法的適用場景。

核心場景


SELECT 
  t.task_name,
  t.status AS task_status,
  COALESCE(r.task_ref_id, 999) AS task_ref_id,
  COALESCE(r.status, 999) AS ref_status
FROM map_task t
LEFT JOIN map_task_ref r ON r.task_id = t.task_id
WHERE task_status = 1;

三種寫法對比

函數/語法	標準性	參數數量	適用場景	示例
`COALESCE(expr1, expr2, ...)`	ANSI SQL 標準	≥2 (返回首個非 NULL)	多備選值、跨數據庫遷移	`COALESCE(col1, col2, 999)`
`IFNULL(expr1, expr2)` / `ISNULL(expr1, expr2)`	MySQL / SQL Server 專用	固定 2	簡單雙值切換、MySQL 專用項目	`IFNULL(col, 999)`
`CASE WHEN expr IS NULL THEN def ELSE expr END`	標準	靈活	複雜邏輯、非 NULL 判斷 (如空字符串、0)	`CASE WHEN r.id IS NULL THEN 999 ELSE r.id END`

進階：INSERT 時默認值不生效的常見坑


-- ❌ 錯誤：顯式傳入 NULL/空字符串，默認值不會生效
INSERT INTO stu_info (id, name, address) VALUES (1, 'Tom', NULL);
INSERT INTO stu_info (id, name, address) VALUES (1, 'Tom', '');

-- ✅ 正確：完全省略該列，或指定 DEFAULT 關鍵字
INSERT INTO stu_info (id, name) VALUES (1, 'Tom');
INSERT INTO stu_info (id, name, address) VALUES (1, 'Tom', DEFAULT);

原理：默認值僅在列缺失時由服務器填充；顯式傳值 (哪怕是 NULL) 視為用戶意圖，不再應用默認值。

建表時正確設置默認值


CREATE TABLE stu_info (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  address VARCHAR(200) NOT NULL DEFAULT '地址不詳'  -- 非空 + 默認值
);

若修改現有表：ALTER TABLE stu_info MODIFY address VARCHAR(200) NOT NULL DEFAULT '地址不詳';

最佳實踐建議

查詢層統一使用 COALESCE (標準、可讀、可擴展)。
寫入層不傳遞 需要默認值的列，交由數據庫引擎處理。
業務語義明確的「無關聯/未知」狀態，建議定義具體代碼值 (如 999, -1) 而非 NULL，便於索引、統計、前端展示。
避免在 WHERE/JOIN ON 中直接比較 NULL，使用 IS NULL/IS NOT NULL 或 COALESCE 轉換後再比較。