Xử lý NULL: IS NULL và COALESCE

Trong tập dữ liệu employees, Andrea Costa rất đặc biệt: anh ấy có department_id = NULL. Bạn đã chạm vào NULL (với LEFT JOIN), nhưng đã đến lúc phải xem xét vấn đề này một cách nghiêm túc: NULLs trong SQL hành xử khác với bất kỳ giá trị nào khác và bỏ qua chúng là một trong những nguyên nhân hàng đầu gây ra lỗi trong mã sản xuất.

NULL không phải là 0, không phải chuỗi rỗng

NULL có nghĩa là giá trị không xác định/thiếu. Không phải 0, không phải '', không phải false. Đó là "Tôi không biết". Từ đây rút ra một tính chất đáng ngạc nhiên: NULL không bằng bất cứ thứ gì, kể cả chính nó.

-- All these expressions return NULL (not true!):
SELECT NULL = NULL;       -- NULL
SELECT NULL = 0;          -- NULL
SELECT NULL <> 'qualcosa'; -- NULL

Hậu quả cụ thể: WHERE column = NULL không bao giờ có tác dụng. Bạn phải sử dụng các toán tử chuyên dụng IS NULL / IS NOT NULL:

-- Employees without a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NULL;

-- Employees WITH a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NOT NULL;

COALESCE - dự phòng NULL

Thường thì bạn muốn hiển thị thứ gì đó thay vì NULL khi dữ liệu được thiếu: chuỗi '(none)', số 0, ngày mặc định. Chức năng đó chính xác đây là COALESCE(a, b, c, ...): nó trả về đầu tiên đối số không NULL trong danh sách.

-- Show the department_id, or 0 if missing:
SELECT first_name, last_name, COALESCE(department_id, 0) AS dept
FROM   employees;

Kết hợp với LEFT JOIN nó trở nên vô cùng mạnh mẽ đối với các báo cáo:

SELECT e.first_name,
       e.last_name,
       COALESCE(d.name, '(nessun dipartimento)') AS dipartimento
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

Kết quả: tất cả nhân viên được trả về, kèm theo tên bộ phận hoặc thông tin rõ ràng nhãn cho những ai chưa có.

NULL và tổng hợp

Các hàm tổng hợp bỏ qua NULLs (ngoại trừ COUNT(*)):

• COUNT(department_id) chỉ đếm các hàng có giá trị;
• AVG(salary) trung bình bỏ qua NULLs — nó không coi chúng là 0;
• SUM(...) chỉ tính tổng các giá trị không phải NULL.

Đây gần như luôn luôn là những gì bạn muốn. Nhưng thật tốt khi biết: nếu bạn đang tìm kiếm "tổng số nhân viên" sử dụng COUNT(*), không phải COUNT(department_id).

Hãy thử nó

Xem lại bài tập