CASE + COALESCE + NULLIF — switch/fallback/safe-divide trong SQL

Dashboard TaskFlow cần hiển thị assignee_name cho mỗi task — fallback "Unassigned" nếu NULL. Trong app code bạn viết if (!assignee) return "Unassigned"; rồi trả về client. Nhưng khi cần logic này trong một JOIN phức tạp với 10 điều kiện GROUP BY, round-trip server chỉ để xử lý fallback là lãng phí.

Trong SQL chuẩn không có IF statement như ngôn ngữ lập trình thông thường (SQL chỉ có statement-level IF trong stored procedure của từng engine, như PL/pgSQL của PostgreSQL hay T-SQL của SQL Server). Thay vào đó, SQL cung cấp ba expression cho conditional logic ngay tại query layer: CASE, COALESCE, và NULLIF. Bài này map cách dùng và pattern thực chiến của cả ba.

1. Analogy — Switch/match expression của ngôn ngữ lập trình

Ba expression này không phải SQL đặc thù — chúng tương đương trực tiếp với construct quen thuộc trong code.

2. Cơ chế đánh giá — CASE và COALESCE

Hai expression này là một bậc thang, hai cách đọc — nhưng chỉ giống nhau ở tầng kết quả, còn tầng "có tính tiếp hay không" thì khác (mục 7 quay lại chỗ này):

3. CASE — switch của SQL

CASE có hai dạng: searched form (dùng predicate tùy ý) và simple form (so sánh equality với một column).

-- Searched form: linh hoat, dung predicate bat ky
SELECT
  id,
  title,
  CASE
    WHEN status = 'todo'  THEN '⏸ Pending'
    WHEN status = 'doing' THEN '▶ In progress'
    WHEN status = 'done'  THEN '✓ Done'
    ELSE '? Unknown'
  END AS status_label
FROM tasks;

-- Simple form: so sanh equality voi mot column, ngan hon
SELECT
  id,
  CASE status
    WHEN 'todo'  THEN 1
    WHEN 'doing' THEN 2
    WHEN 'done'  THEN 3
  END AS status_order
FROM tasks
ORDER BY status_order;

Searched form mạnh hơn vì branch có thể dùng bất kỳ predicate nào (<, BETWEEN, IS NULL, subquery). Simple form chỉ so sánh equality với một column nhưng ngắn hơn khi đó là đủ.

Hệ quản trị tuân thủ SQL chuẩn (PostgreSQL, SQL Server) yêu cầu tất cả các branch của CASE phải trả về cùng type. Nếu mix type thì cần cast tường minh:

-- ERROR theo SQL chuan: integer vs text, khong tu convert
-- CASE WHEN id > 5 THEN id ELSE 'small' END

-- Fix: cast ve cung type
-- PostgreSQL: id::text hoac CAST(id AS TEXT)
-- SQL chuan (portable): CAST(id AS VARCHAR)
SELECT
  CASE WHEN id > 5 THEN CAST(id AS VARCHAR) ELSE 'small' END AS size_label
FROM tasks;

CASE không có ELSE thì trả NULL khi không match branch nào — tương đương ELSE NULL.

4. COALESCE — fallback chain

COALESCE(a, b, c, ...) trả về giá trị non-NULL đầu tiên trong danh sách. Nếu tất cả NULL thì trả NULL.

-- Fallback chain: nickname -> name -> email -> 'Anonymous'
SELECT COALESCE(nickname, name, email, 'Anonymous') AS display_name
FROM users;
-- Neu nickname NULL -> thu name; name NULL -> thu email; email NULL -> 'Anonymous'

-- Trong UPDATE: giu gia tri cu neu input NULL ($1)
UPDATE tasks
SET assignee_id = COALESCE($1, assignee_id)
WHERE id = $2;
-- Neu $1 IS NULL -> COALESCE tra assignee_id (giu nguyen)
-- Neu $1 co gia tri -> COALESCE tra $1 (cap nhat moi)

-- Trong WHERE: treat NULL nhu gia tri mac dinh de so sanh
SELECT * FROM tasks
WHERE COALESCE(priority, 0) < 3;
-- Row co priority NULL duoc xem la priority = 0, van xuat hien
-- Khac voi: WHERE priority < 3 (bo qua NULL row vi NULL < 3 = UNKNOWN)

Cross-vendor note: MySQL/SQLite có IFNULL(a, b) (chỉ 2 args); MSSQL có ISNULL(a, b) (chỉ 2 args). COALESCE là ANSI SQL standard, portable nhất và hỗ trợ n args.

Thử ngẫmfallback chain COALESCE(nickname, name, email, 'Anonymous') đang chạy đúng. Nếu bạn đổi thứ tự thành COALESCE(email, nickname, name, 'Anonymous'), dashboard hiển thị đổi ở những user nào?

5. NULLIF — safe-divide pattern

NULLIF(a, b) trả NULL khi a = b, ngược lại trả a. Đây là pattern chính để tránh divide-by-zero:

SELECT NULLIF(0, 0);    -- NULL
SELECT NULLIF(5, 0);    -- 5
SELECT NULLIF('', '');  -- NULL (chuyen empty string thanh NULL)

Khi visitors = 0, phép chia revenue / visitors ném error. NULLIF biến 0 thành NULL trước khi chia:

SELECT
  campaign_id,
  revenue,
  visitors,
  revenue / NULLIF(visitors, 0) AS revenue_per_visitor
FROM campaigns;
-- visitors = 0 -> NULLIF tra NULL -> NULL / anything = NULL (khong error)
-- visitors > 0 -> NULLIF tra visitors -> phep chia binh thuong

Điểm dễ hiểu sai: NULLIF không "bắt lỗi" phép chia. Nó đổi mẫu số sang giá trị mà phép chia có định nghĩa — chia cho 0 là lỗi, chia cho NULL thì không:

Kết hợp COALESCE để thay NULL bằng giá trị mặc định khi muốn có số cụ thể thay vì NULL:

-- Tra 0 thay vi NULL khi visitors = 0
SELECT
  campaign_id,
  COALESCE(revenue / NULLIF(visitors, 0), 0) AS revenue_per_visitor
FROM campaigns;

6. Pitfall — type mismatch trong CASE branches

-- ERROR theo SQL chuan: integer va text khong co implicit cast
SELECT CASE WHEN id > 5 THEN id ELSE 'small' END FROM tasks;
-- ERROR: CASE/WHEN could not convert type integer to text (PostgreSQL)

-- Fix: cast explicit ve cung type (CAST la SQL chuan)
SELECT CASE WHEN id > 5 THEN CAST(id AS VARCHAR) ELSE 'small' END FROM tasks;

-- Co the error tuy vendor: COALESCE(numeric_col, 'unknown')
-- Safe: cast truoc (CAST la SQL chuan)
SELECT COALESCE(CAST(score AS VARCHAR), 'N/A') AS display_score FROM results;

7. Pitfall — COALESCE không guarantee short-circuit

-- Nguy hiem neu da dua vao short-circuit implicit:
SELECT COALESCE(simple_col, expensive_function(other_col)) FROM t;
-- simple_col non-NULL nhung expensive_function co the van chay tuy vendor

-- Guaranteed: expensive_function chi chay khi simple_col IS NULL
SELECT
  CASE WHEN simple_col IS NOT NULL THEN simple_col
       ELSE expensive_function(other_col)
  END
FROM t;

Thử ngẫmreport doanh thu của bạn đang gọi COALESCE(cached_total, recompute_expensive_total()) cho 50.000 dòng mỗi đêm. Pitfall vừa nêu ở trên đáng lo tới mức nào ở quy mô đó?

8. Applied — TaskFlow scenarios

-- Scenario 1: status icon mapping (simple CASE)
SELECT
  id,
  title,
  CASE status
    WHEN 'todo'  THEN '⏸'
    WHEN 'doing' THEN '▶'
    WHEN 'done'  THEN '✓'
    ELSE '?'
  END AS icon
FROM tasks;

-- Scenario 2: assignee fallback sau LEFT JOIN
SELECT
  t.id,
  t.title,
  COALESCE(u.name, '(Unassigned)') AS assignee
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id;
-- Cac task chua assign: u.name = NULL -> COALESCE tra '(Unassigned)'

-- Scenario 3: completion rate per project (safe divide)
SELECT
  project_id,
  COUNT(*) FILTER (WHERE status = 'done')                              AS done_count,
  COUNT(*)                                                              AS total,
  COUNT(*) FILTER (WHERE status = 'done') * 100.0 / NULLIF(COUNT(*), 0) AS completion_pct
FROM tasks
GROUP BY project_id;
-- Project rong (COUNT(*) = 0) -> NULLIF tra NULL -> NULL pct thay vi error

-- Scenario 4: bucketize task age bang CASE searched form
-- INTERVAL '1 day' la SQL chuan; MySQL co the dung INTERVAL 1 DAY (khong co quotes)
SELECT
  id,
  title,
  CASE
    WHEN CURRENT_TIMESTAMP - created_at < INTERVAL '1 day'  THEN 'today'
    WHEN CURRENT_TIMESTAMP - created_at < INTERVAL '7 days' THEN 'this week'
    WHEN CURRENT_TIMESTAMP - created_at < INTERVAL '30 days' THEN 'this month'
    ELSE 'older'
  END AS age_bucket
FROM tasks
ORDER BY created_at DESC;

9. Deep Dive — Conditional expressions

10. Liên hệ các bài khác

• Bài 02 — WHERE + NULL three-valued logic: COALESCE và NULLIF trực tiếp xử lý NULL — phần 2 giải thích tại sao NULLIF(a, 0) tạo ra NULL thay vì 0.
• Bài 03 — ORDER BY + pagination: COALESCE(col, fallback) thường dùng để xử lý NULL trong ORDER BY — kết hợp với NULLS FIRST/LAST.
• Bài 04 — DISTINCT vs GROUP BY: CASE thường xuất hiện trong SELECT list cùng GROUP BY để tính conditional aggregate; type strict mode cùng engine.
• Module 3 — GROUP BY + HAVING: FILTER (WHERE ...) là cú pháp aggregate thay thế CASE WHEN — cùng mục đích lọc điều kiện khi tính nhưng khác cú pháp.

11. Tóm tắt

• CASE searched form dùng predicate tùy ý; simple form ngắn hơn cho equality comparison với một column.
• COALESCE(a, b, c) là ANSI standard fallback chain — trả giá trị non-NULL đầu tiên; portable hơn IFNULL/ISNULL hai-args của MySQL/MSSQL.
• NULLIF(a, b) trả NULL khi a = b — pattern cốt lõi là value / NULLIF(divisor, 0) để tránh divide-by-zero error.
• COALESCE(x / NULLIF(y, 0), 0) kết hợp cả hai: safe-divide và thay NULL bằng default.
• CASE branches phải cùng type theo SQL chuẩn — cast tường minh (CAST(x AS type)) nếu cần; MySQL lax hơn nên code migrate sang engine strict có thể break.
• Forward link: Module 3 — JOIN, aggregation & window đi vào FILTER clause trong aggregate (khác cú pháp CASE nhưng cùng mục đích lọc điều kiện khi tính). Module 7 — Transactions & consistency đi vào stored procedure và control flow mức engine — dùng khi cần IF/ELSIF/ELSE statement thật sự, không chỉ expression.

12. Tự kiểm tra

Bài tiếp theo: Mini-challenge: dashboard query TaskFlow