NULL trông như một giá trị bình thường trong bảng, nhưng nó không phải. Nó là "không biết" — chỗ trống nơi dữ liệu chưa có. Và cái nghĩa đó làm mọi phép so sánh dính tới nó cư xử khác hẳn trực giác: một điều kiện WHERE tưởng bắt hết lại lặng lẽ bỏ sót hàng loạt hàng, một NOT IN trả về rỗng không lý do. Bài này dựng một bảng một triệu hàng có NULL và đo từng cái bẫy — bắt đầu bằng cái tôi tự sập vào.
NULL là "không biết", không phải một giá trị
Đa số ngôn ngữ có logic hai trạng thái: một điều kiện hoặc đúng, hoặc sai. SQL có ba: đúng (true), sai (false), và không biết (unknown). Trạng thái thứ ba sinh ra từ NULL. Vì NULL nghĩa là "không biết", mọi so sánh với nó cũng trả về "không biết": NULL = 5 không phải false, mà là unknown; NULL = NULL cũng là unknown, chứ không phải true — hai điều chưa biết thì không thể khẳng định chúng bằng nhau.
Điều này còn vô hại cho tới khi gặp WHERE. Mệnh đề WHERE chỉ giữ lại những hàng cho kết quả đúng; hàng cho false và hàng cho unknown đều bị loại. Đó là mấu chốt của mọi bất ngờ bên dưới: một hàng NULL làm điều kiện ra unknown, và unknown bị vứt đi y như false — dù ý bạn không phải vậy.
Một lần tôi đo hớ: <> 7 nuốt mất hàng NULL
Tôi có bảng sv một triệu hàng, cột diem, trong đó 200.000 hàng để NULL (điểm chưa nhập). Tôi muốn đếm "mọi hàng có điểm khác 7", viết câu hiển nhiên:
SELECT count(*) FROM sv WHERE diem <> 7;
Tôi nhẩm trước kết quả: tổng một triệu, trừ đi 10.000 hàng đúng bằng 7, phải còn khoảng 990.000. Đo ra:
diem <> 7 -> 790000
Thiếu đúng 200.000 — cả khối hàng NULL. Con số hụt bằng chằn chặn số hàng NULL là đầu mối. Với một hàng diem là NULL, biểu thức NULL <> 7 không ra true cũng không ra false, nó ra unknown; và WHERE loại mọi hàng không-true, nên toàn bộ 200.000 hàng NULL rơi khỏi kết quả. Tôi kiểm lại bằng ba mảnh cho khớp: hàng bằng 7 (10.000) + hàng khác 7 (790.000) + hàng NULL (200.000) = 1.000.000. Đúng khít. <> 7 chỉ nói về những hàng đã biết điểm; nó im lặng với những hàng chưa biết.
Cái sai của tôi không phải ở câu SQL — nó chạy đúng theo luật. Cái sai là ở kỳ vọng: tôi nghĩ như logic hai trạng thái ("không phải 7 thì đương nhiên nằm trong tập này"), trong khi SQL có trạng thái thứ ba nuốt mất những hàng đó. Muốn gộp cả hàng NULL vào, phải nói thẳng: WHERE diem <> 7 OR diem IS NULL — đo ra đúng 990.000.
= NULL không bao giờ đúng, phải IS NULL
Hệ quả trực tiếp: không có cách nào dùng = để tìm hàng NULL. Người mới hay viết WHERE diem = NULL và ngạc nhiên vì luôn nhận về rỗng:
WHERE diem = NULL -> 0 hàng
WHERE diem IS NULL -> 200000 hàng
diem = NULL là "diem có bằng cái-không-biết không?" — câu trả lời mãi mãi là unknown, nên không hàng nào lọt qua WHERE. SQL cho một cặp toán tử riêng, IS NULL và IS NOT NULL, chuyên để hỏi về sự có mặt của NULL; chúng trả về true/false thật sự chứ không phải unknown. Đây là lý do mọi câu lọc NULL phải dùng IS NULL, không bao giờ = NULL.
Cái bẫy nguy hơn: NOT IN với một NULL
Bẫy <> còn dễ thấy vì con số hụt đi. NOT IN thì âm thầm hơn nhiều và hay gặp trong thực tế. Tôi có một bảng nhỏ chan chứa vài giá trị cần loại — nhưng lỡ có một NULL lọt vào (rất thường xảy ra khi lấy từ một cột cho phép NULL):
-- chan chứa: 7, 13, và một NULL
SELECT count(*) FROM sv WHERE diem NOT IN (SELECT v FROM chan);
NOT IN (7, 13, NULL) -> 0 hàng (!)
NOT IN (lọc bỏ NULL) -> 780000 hàng
Toàn bộ kết quả biến mất — 0 hàng, không một lời cảnh báo. Lý do: diem NOT IN (7, 13, NULL) nở ra thành diem <> 7 AND diem <> 13 AND diem <> NULL. Vế cuối diem <> NULL luôn là unknown, và bất-cứ-gì AND unknown không bao giờ ra true — nhiều nhất là unknown. Nên với mọi hàng, điều kiện ra unknown, và WHERE loại sạch. Một NULL duy nhất trong danh sách NOT IN đủ để câu truy vấn luôn trả rỗng. Chữa bằng cách lọc NULL khỏi subquery (WHERE v IS NOT NULL) — khi đó ra 780.000 hàng như mong đợi. Đây là một trong những lỗi SQL tốn thời gian gỡ nhất, vì câu vẫn "chạy", chỉ trả sai.
Hàm tổng hợp bỏ qua NULL
NULL cũng đổi cách các hàm tổng hợp đếm. COUNT(*) đếm mọi hàng; nhưng COUNT(cột) chỉ đếm hàng mà cột đó không NULL:
COUNT(*) = 1000000
COUNT(diem) = 800000 -> lệch 200000 = số hàng NULL
SUM và AVG cũng bỏ qua NULL. Điều này khiến AVG khác hẳn "tổng chia số hàng" nếu bạn tính tay:
AVG(diem) = 50
SUM(diem)/COUNT(*) = 40 (sai — chia cho cả hàng NULL)
AVG(diem) chia tổng cho 800.000 (số hàng có điểm), cho ra 50 — trung bình đúng của những ai có điểm. Nếu bạn tự chia SUM/COUNT(*), bạn chia cho một triệu và ra 40, coi như 200.000 người vắng điểm là điểm 0 — một con số khác hẳn, và thường là sai ý. AVG bỏ NULL chính là hành vi đúng ở đây; chỉ cần biết nó làm vậy để không tự tính lại theo cách khác. Tương tự, DISTINCT coi mọi NULL là một nhóm duy nhất: đếm giá trị phân biệt trên cột bỏ NULL cho 80, còn SELECT DISTINCT gộp tất cả NULL thành một dòng, ra 81.
NULL tốn chỗ không? Ngược lại
Trước khi đo, tôi mơ hồ nghĩ NULL "tốn chỗ" — phải đánh dấu đâu đó chứ. Tôi so hai bảng cùng cấu trúc năm cột, một bảng điền đủ, một bảng để bốn cột toàn NULL:
5 cột int có giá trị -> 50 MB
id + 4 cột toàn NULL -> 35 MB
Hàng toàn NULL nhỏ hơn, không lớn hơn. PostgreSQL đánh dấu các cột NULL bằng một null bitmap trong header tuple — đúng một bit mỗi cột — và không lưu byte dữ liệu nào cho cột NULL. Nên bốn cột NULL chỉ tốn thêm vài bit, trong khi bốn cột có giá trị tốn 16 byte mỗi hàng. Như bài kiểu dữ liệu đã thấy, cái quyết định dung lượng là byte dữ liệu thật, và NULL đơn giản là không có byte nào. Thêm nữa, chỉ mục B-tree có lưu NULL, nên IS NULL vẫn dùng được chỉ mục: kế hoạch cho WHERE diem IS NULL chạy Index Only Scan, đọc 200.000 hàng chỉ với 171 trang — không hề phải quét cả bảng.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: mỗi khi một cột cho phép NULL, mọi điều kiện trên nó phải tính tới trạng thái thứ ba. col <> x, col NOT IN (...), NOT (col = x) đều lặng lẽ bỏ hàng NULL. Nếu ý bạn là "gồm cả hàng chưa biết", phải thêm OR col IS NULL. Đây không phải lỗi của CSDL — nó nhất quán với nghĩa "không biết" — mà là chỗ trực giác hai-trạng-thái của lập trình viên lệch với logic ba-trạng-thái của SQL.
Hệ quả thứ hai: cẩn thận nhất với NOT IN trên subquery. Nếu cột trong subquery cho phép NULL, một NULL lọt vào là cả câu trả rỗng. Thói quen an toàn: dùng NOT EXISTS thay cho NOT IN (nó xử lý NULL đúng ý hơn), hoặc luôn WHERE ... IS NOT NULL trong subquery. Con số mang theo: NULL là "không biết", so sánh với nó ra unknown, và WHERE loại mọi hàng không-true — nên diem <> 7 bỏ 200000 hàng NULL, NOT IN (...,NULL) trả 0 hàng, COUNT(cột) và AVG bỏ NULL (AVG=50 chứ không phải 40); muốn bắt NULL phải IS NULL, và hàng NULL còn nhỏ hơn trên đĩa vì giá trị không được lưu. Khi một truy vấn "chạy đúng" mà con số hụt đi một khối tròn trịa, hãy nghi ngay NULL và trạng thái thứ ba.
Thử ba mươi giây
Trong psql, tạo nhanh một bảng: CREATE TABLE t (x int); INSERT INTO t VALUES (1),(2),(NULL);. Giờ chạy SELECT count(*), count(x) FROM t; — bạn thấy 3 và 2, cột x bỏ hàng NULL. Rồi SELECT count(*) FROM t WHERE x <> 1; cho 1, không phải 2 — hàng NULL biến mất y như bài này. Cuối cùng thử SELECT count(*) FROM t WHERE x NOT IN (1, NULL); và xem nó trả 0 dù còn hàng x=2. Ba câu, nửa phút, đủ để trạng thái thứ ba của SQL in vào trí nhớ trước khi nó cắn bạn trong một truy vấn thật.
