Câu lệnh CASE WHEN trong SQL với SQLite: THEN, ELSE và IIF

CASE là if/else của SQL

CASE là cách bạn đưa logic điều kiện vào bên trong một truy vấn. Nó duyệt lần lượt các nhánh WHEN, chọn nhánh đầu tiên khớp, và trả về giá trị sau THEN. Nếu không nhánh nào khớp, nó trả về giá trị của ELSE, hoặc NULL nếu bạn không viết ELSE.

Từ quan trọng ở đây là biểu thức (expression). CASE tạo ra một giá trị, nên nó đặt được ở bất cứ đâu cho phép một giá trị: một cột trong SELECT, một khóa của ORDER BY, vế phải của một phép so sánh, một đối số của hàm.

SELECT
    'apple' AS item,
    CASE
        WHEN 5 > 3 THEN 'yes'
        ELSE 'no'
    END AS bigger;

Toàn bộ hình dạng chỉ có vậy: CASE, một hoặc nhiều WHEN ... THEN ..., ELSE không bắt buộc, rồi END. END là bắt buộc: quên nó là lỗi gõ phổ biến nhất.

Một ví dụ thực tế

Giả sử bạn có một bảng đơn hàng và muốn gắn nhãn mỗi hàng theo quy mô. Hãy tạo nhanh một bảng nhỏ để chạy được truy vấn:

WITH orders(id, total) AS (
    VALUES
        (1, 12.00),
        (2, 89.50),
        (3, 250.00),
        (4, 5.25),
        (5, 1200.00)
)
SELECT
    id,
    total,
    CASE
        WHEN total < 10 THEN 'tiny'
        WHEN total < 100 THEN 'small'
        WHEN total < 500 THEN 'medium'
        ELSE 'large'
    END AS size_bucket
FROM orders;

Các nhánh được kiểm tra từ trên xuống. Nhánh khớp đầu tiên thắng, nên hãy sắp xếp từ cụ thể nhất đến tổng quát nhất. ELSE bắt mọi thứ không khớp: nếu không có nó, 1200.00 sẽ trả về NULL thay vì 'large'.

Dạng tìm kiếm và dạng đơn giản

Những gì bạn thấy ở trên là dạng tìm kiếm (searched): mỗi WHEN có điều kiện boolean riêng. Có một dạng ngắn hơn khi bạn so sánh một biểu thức với nhiều hằng số, gọi là dạng đơn giản (simple):

WITH users(id, status) AS (
    VALUES
        (1, 'A'),
        (2, 'B'),
        (3, 'C'),
        (4, 'A')
)
SELECT
    id,
    status,
    CASE status
        WHEN 'A' THEN 'active'
        WHEN 'B' THEN 'banned'
        WHEN 'C' THEN 'closed'
        ELSE 'unknown'
    END AS status_label
FROM users;

Biểu thức sau CASE được đánh giá một lần và so sánh bằng = với từng giá trị WHEN. Gọn hơn khi bạn tra cứu so khớp bằng trên một cột duy nhất.

Có một điểm cần lưu ý: CASE dạng đơn giản dùng =, và NULL = NULL không đúng trong SQL. Nếu status có thể là NULL, các nhánh 'A'/'B'/'C' sẽ không khớp với nó, và bạn sẽ rơi vào ELSE. Để xử lý NULL rõ ràng, hãy chuyển sang dạng tìm kiếm và dùng WHEN status IS NULL THEN ....

CASE trong ORDER BY

ORDER BY nhận bất kỳ biểu thức nào, nên CASE dùng được ở đó. Rất hữu ích khi bạn muốn một thứ tự sắp xếp tùy chỉnh không theo thứ tự chữ cái hay thứ tự số:

WITH tickets(id, priority) AS (
    VALUES
        (1, 'low'),
        (2, 'high'),
        (3, 'medium'),
        (4, 'high'),
        (5, 'low')
)
SELECT id, priority
FROM tickets
ORDER BY
    CASE priority
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'low' THEN 3
    END,
    id;

Theo thứ tự chữ cái thì 'high' < 'low' < 'medium', hoàn toàn vô dụng khi cần phân loại mức ưu tiên. Ánh xạ mỗi mức ưu tiên thành một con số qua CASE cho bạn đúng thứ tự mong muốn. Phần , id ở cuối phân định các hàng bằng nhau một cách ổn định.

CASE trong WHERE

Bạn có thể đặt CASE bên trong WHERE, nhưng phần lớn thời gian bạn không cần: chuỗi AND/OR rõ ràng hơn. Nó phát huy tác dụng khi chính điều kiện phụ thuộc vào một giá trị khác:

WITH products(id, name, price, on_sale) AS (
    VALUES
        (1, 'mug', 12.00, 1),
        (2, 'shirt', 25.00, 0),
        (3, 'cap', 18.00, 1),
        (4, 'bag', 45.00, 0)
)
SELECT id, name, price
FROM products
WHERE price < CASE WHEN on_sale = 1 THEN 20 ELSE 30 END;

Hàng đang giảm giá đạt điều kiện khi dưới 20, hàng thường khi dưới 30. Bản thân ngưỡng so sánh đã là có điều kiện. Không có CASE, bạn sẽ phải viết (on_sale = 1 AND price < 20) OR (on_sale = 0 AND price < 30): cùng kết quả, nhưng rối hơn.

CASE bên trong hàm tổng hợp

Đây là chỗ CASE thật sự đáng giá. Kết hợp nó với SUM hoặc COUNT để tính tổng trên một phần các hàng chỉ trong một lần duyệt, tức là phiên bản SQL của "đếm xem có bao nhiêu hàng khớp":

WITH orders(id, total, status) AS (
    VALUES
        (1, 50.00, 'paid'),
        (2, 30.00, 'pending'),
        (3, 75.00, 'paid'),
        (4, 20.00, 'cancelled'),
        (5, 100.00, 'paid')
)
SELECT
    COUNT(*) AS all_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_revenue
FROM orders;

CASE trả về 1 cho các hàng khớp và 0 cho các hàng còn lại, nên SUM trở thành phép đếm có điều kiện. Mẹo tương tự cũng dùng được cho doanh thu: trả về total ở các hàng khớp và 0 ở mọi hàng khác. Quét bảng một lần, được nhiều phép tổng hợp có điều kiện.

IIF: lối tắt cho hai nhánh

Với một điều kiện duy nhất có hai kết quả, SQLite có IIF(cond, when_true, when_false). Đây thuần túy là cách viết tắt của CASE WHEN cond THEN when_true ELSE when_false END:

WITH accounts(id, balance) AS (
    VALUES
        (1, 100.00),
        (2, -25.00),
        (3, 0.00),
        (4, 1500.00)
)
SELECT
    id,
    balance,
    IIF(balance < 0, 'overdrawn', 'ok') AS state
FROM accounts;

Dùng IIF khi logic chỉ có hai khả năng và viết trên một dòng sẽ dễ đọc hơn. Chuyển sang CASE khi bạn có từ ba nhánh trở lên, cần xử lý NULL riêng, hoặc muốn thứ tự xét lần lượt của nhiều mệnh đề WHEN.

Những lỗi đáng biết

Vài điều hay khiến người ta vấp:

• Quên END. CASE mở một khối; END đóng nó lại. SQLite sẽ báo lỗi phân tích cú pháp ở một chỗ khá xa so với lỗi thật.
• Không có ELSE nghĩa là NULL. Nếu không nhánh WHEN nào khớp và bạn bỏ qua ELSE, kết quả là NULL. Đôi khi đó là điều bạn muốn; thường thì không.
• Thứ tự các nhánh rất quan trọng. Với dạng tìm kiếm, nhánh WHEN khớp đầu tiên sẽ thắng. Đặt WHEN total < 500 trước WHEN total < 100 nghĩa là nhánh thứ hai không bao giờ được chạy tới.
• Trộn kiểu dữ liệu. Mỗi nhánh có thể trả về một kiểu khác nhau, và SQLite sẽ không phàn nàn, nhưng code xử lý phía sau thì có thể. Hãy cố giữ mọi nhánh trả về các kiểu tương thích (toàn văn bản, hoặc toàn số).
• CASE dạng đơn giản và NULL. Như đã nói: dạng đơn giản dùng =, vốn không bao giờ khớp với NULL. Hãy dùng dạng tìm kiếm khi có NULL.

Tiếp theo: hàm xử lý chuỗi

CASE cho phép bạn rẽ nhánh theo giá trị; chương tiếp theo bắt đầu với việc biến đổi giá trị. Các hàm xử lý chuỗi, UPPER, LOWER, SUBSTR, REPLACE, mẫu LIKE, đảm nhận công việc hằng ngày là làm sạch và định dạng lại các cột văn bản. Đó là nội dung tiếp theo.