Hàm ⑥ — CASE trong thực tế — ORDER BY / UPDATE / NULL

Dữ liệu dùng trong bài này — staff và customer

CASE đã học đến giờ là một biểu thức đa dụng mà bạn có thể đặt không chỉ trong danh sách cột SELECT mà còn bên trong `ORDER BY` và trong mệnh đề SET của `UPDATE`. Bài này lần lượt đi qua hai ứng dụng đó và cạm bẫy khi xử lý NULL với CASE.

Đối tượng là bảng staff (10 nhân viên) ở nửa đầu và bảng customer (8 khách hàng / có chứa NULL) ở nửa sau. Nửa đầu sắp xếp city theo một thứ tự tùy ý; nửa sau dùng một CASE hiển thị email NULL thành "Not registered", và cuối cùng chạy một UPDATE CASE viết lại các giá trị country thành các nhóm vùng.

Trước khi bắt đầu các bài thực hành, hãy xác nhận định nghĩa cột và dữ liệu mẫu của hai bảng dùng ở đây — staff và customer.

① Chạy PRAGMA table_info(staff); và PRAGMA table_info(customer); để kiểm tra định nghĩa cột của cả hai bảng.

② Chạy SELECT * FROM staff LIMIT 5; và SELECT * FROM customer LIMIT 5; để xem trước 5 hàng đầu.

Dùng CASE trong ORDER BY — Gán một thứ tự tùy ý

Bên phải ORDER BY có thể chứa không chỉ tên cột mà còn cả một biểu thức CASE. Nhờ đó bạn có thể gán một thứ tự tùy ý mà thứ tự bảng chữ cái của chuỗi hay độ lớn của số không tạo ra được. Dùng nó cho các ưu tiên nghiệp vụ như "Tokyo → Osaka → Kyoto → còn lại", thứ tự trạng thái như "Open → In progress → Done", hay một thứ tự xếp hạng.

Viết là ORDER BY CASE WHEN đk THEN số ... ELSE số END — CASE trả về một số để sắp xếp và bạn sắp xếp theo số đó. ASC / DESC gắn được y như với một cột. Các hàng cùng số được sắp theo cột ORDER BY kế tiếp (cách nhau bằng dấu phẩy), nên kết hợp khóa sắp xếp + khóa phụ cũng quyết định được thứ tự bên trong một nhóm.

-- 1) Sắp xếp city theo thứ tự 'Osaka → Kyoto → Tokyo → còn lại'
SELECT name, city, salary FROM staff
ORDER BY
  CASE city
    WHEN 'Osaka' THEN 1
    WHEN 'Kyoto' THEN 2
    WHEN 'Tokyo' THEN 3
    ELSE 9
  END,
  salary DESC;  -- trong cùng một city, lương cao nhất trước

-- 2) ORDER BY CASE + DESC cũng đảo ngược được thứ tự
--   nếu CASE gán 1, 2, 3 thì DESC xếp chúng thành 3, 2, 1

Hãy hình dung yêu cầu "hiển thị nhân viên theo quy mô văn phòng (Tokyo → Osaka → Kyoto → còn lại)". (Lời giải hiện ra khi bạn chạy đúng.)

① Từ bảng staff, lấy 2 cột name, city.

② Với ORDER BY CASE, gán "Tokyo là 1, Osaka là 2, Kyoto là 3, mọi thứ khác là 9" và sắp xếp theo số đó. Làm thành một sắp xếp đa cấp, nơi các hàng trong cùng một city được sắp theo name tăng dần.

Cạm bẫy CASE + NULL — Dùng `IS NULL`, không dùng `= NULL`

Cũng như trong WHERE, `= NULL` / `<> NULL` cũng không dùng được bên trong CASE. Một phép so sánh với NULL luôn cho ra NULL (không xác định), và khi một mệnh đề CASE WHEN trả về NULL nhánh đó không được chọn, nên kết quả khác với ý định của bạn.

Để kiểm tra NULL, dùng `cột IS NULL` / `cột IS NOT NULL`. Đây là cùng quy tắc với kiểm tra NULL trong WHERE đã học trước, và nó chung cho mọi nơi viết được điều kiện — CASE / IIF / WHERE / ON. Dạng đơn giản (CASE cột WHEN ... THEN) không kiểm tra NULL được, nên khi cần xử lý NULL luôn dùng dạng tìm kiếm (CASE WHEN cột IS NULL THEN ...).

-- Sai: WHEN NULL không lấy được các hàng NULL ('Unknown' không bao giờ hiện)
SELECT name, age,
  CASE age
    WHEN NULL THEN 'Unknown'
    ELSE age
  END AS age_display
FROM customer;

-- Đúng: dạng tìm kiếm + IS NULL
SELECT name, age,
  CASE
    WHEN age IS NULL THEN 'Unknown'
    ELSE age
  END AS age_display
FROM customer;

-- Lưu ý: COALESCE(age, 'Unknown') cho cùng kết quả
--   với phép thay NULL 2 giá trị đơn giản, COALESCE ngắn hơn

Hãy hình dung yêu cầu "trên màn hình danh sách khách hàng, hiển thị NULL ở cột email thành 'Not registered' thay vì để trống".

① Từ bảng customer, lấy name và email.

② Dùng dạng CASE tìm kiếm, thêm cột thứ 3 đặt bí danh email_display là 'Not registered' cho các hàng mà email là NULL, và giá trị email gốc nếu khác.

Dùng CASE trong UPDATE — Viết lại giá trị dựa trên một điều kiện

Đến giờ CASE đã tạo một cột mới ở phía đọc, nhưng viết CASE trong mệnh đề SET của một UPDATE cho phép bạn viết lại chính các giá trị cột trong bảng dựa trên một điều kiện.

Viết là UPDATE bảng SET cột = CASE WHEN đk THEN giá_trị1 WHEN đk THEN giá_trị2 ... ELSE cột END. Viết ELSE cột để nói "các hàng không khớp WHEN nào giữ giá trị gốc" là cách an toàn; quên nó thì ELSE bị coi như NULL, ghi đè các hàng ngoài mục tiêu bằng NULL.

Vì UPDATE là một thao tác phá hủy làm thay đổi bảng thực tế, bài này xử lý nó ở bài thực hành cuối cùng. Trước khi chạy, thực hành chuẩn trong sản xuất là thử cùng điều kiện với SELECT name, cột, CASE ..., kiểm tra kết quả bằng mắt, và chỉ viết lại thành UPDATE nếu thấy đúng.

-- Trước hết kiểm tra với SELECT (định làm age NULL thành 0)
SELECT name, age,
  CASE
    WHEN age IS NULL THEN 0
    ELSE age
  END AS new_age
FROM customer;

-- Nếu thấy đúng, viết lại thành UPDATE
UPDATE customer SET age = CASE
  WHEN age IS NULL THEN 0
  ELSE age
END;

Hãy hình dung yêu cầu "chuyển hàng loạt cột country của bảng customer từ tên quốc gia thành nhóm vùng (Asia / Western / Europe / Unknown)". Đây là bài thực hành cuối của bài viết, nên bạn thực hiện một thao tác phá hủy làm thay đổi bảng thực tế.

① UPDATE hàng loạt cột country của bảng customer bằng CASE. Ánh xạ là:

- Japan → Asia

- US hoặc UK → Western

- Italy → Europe

- NULL → Unknown

- Bất cứ thứ gì khác → giữ giá trị gốc (để chạy lại cho cùng kết quả)

② Sau UPDATE, chạy SELECT id, name, country FROM customer ORDER BY id; và xác nhận country của mỗi khách hàng đã đổi thành tên vùng.