NULL không phải một giá trị. Nó là chỗ chưa biết, và SQL xử lý nó bằng logic ba trạng thái thay vì hai. Hệ quả là một số truy vấn trả về kết quả sai mà không báo lỗi gì.

Một dòng NULL làm NOT IN trả về rỗng

Phép đo đắt nhất: một dòng NULL xoá sạch kết quả

Hai bảng, a1 có 200.000 dòng, b1 có 100.000 dòng, không có NULL nào:

SELECT count(*) FROM a1 WHERE i NOT IN (SELECT i FROM b1);
Kết quả Thời gian
b1 không có NULL 100.000 dòng 30 ms
b1 thêm đúng một dòng NULL 0 dòng 25 ms

Một dòng. Không lỗi, không cảnh báo, và chạy còn nhanh hơn.

NOT EXISTS viết tương đương thì không bị:

Kết quả Thời gian
NOT EXISTS, cả hai trường hợp 100.000 dòng 20 ms

Nhanh hơn, đúng hơn. Đây là lý do thực dụng nhất để luôn dùng NOT EXISTS thay NOT IN khi bảng con là một truy vấn — bạn không kiểm soát được liệu cột đó có NULL hôm nay hay ngày mai.

Vì sao: x NOT IN (1, 2, NULL) không bao giờ đúng

NOT IN mở ra thành x <> 1 AND x <> 2 AND x <> NULL. Vế cuối luôn cho NULL, và:

Biểu thức Kết quả
true AND NULL NULL
false AND NULL false
true OR NULL true
NULL = NULL NULL, không phải true

WHERE chỉ giữ dòng nào cho ra đúng true. NULL bị loại y như false. Nên toàn bộ điều kiện không bao giờ đúng, và bạn nhận về tập rỗng.

Để ý dòng thứ hai: false AND NULL cho ra false chứ không phải NULL — vì đã biết chắc kết quả rồi, không cần biết vế kia. Logic ba trạng thái vẫn "lười" như logic hai trạng thái.

Cùng cái bẫy ở dạng nhẹ hơn

Một bảng năm nhân viên, vài ô để trống:

 id |  ten  |   phong    | luong
----+-------+------------+-------
  1 | An    | Ky thuat   |  2000
  2 | Binh  | Ky thuat   |  3000
  3 | Cuong |            |  2500
  4 | Dung  | Kinh doanh |
  5 | Em    |            |

Câu hỏi "ai không thuộc phòng Kỹ thuật":

Điều kiện Trả về
phong <> 'Ky thuat' Dung
NOT (phong = 'Ky thuat') Dung
phong IS DISTINCT FROM 'Ky thuat' Cuong, Dung, Em

Hai cách đầu bỏ sót Cường và Em — hai người thật sự không thuộc phòng Kỹ thuật. Với người đọc báo cáo, đó là hai nhân viên biến mất.

IS DISTINCT FROM là toán tử so sánh coi NULL như một giá trị bình thường. Nó dài dòng nhưng nó làm đúng điều bạn nghĩ khi gõ <>.

Các hàm tổng hợp bỏ qua NULL, trừ count(*)

count(*)     = 5
count(phong) = 3
count(luong) = 3
sum(luong)   = 7500
avg(luong)   = 2500

avg chia cho 3 chứ không phải 5. Nếu ý bạn là "lương trung bình của toàn công ty" và ba người chưa nhập lương, con số 2500 đang trả lời một câu hỏi khác.

Cách viết rõ ràng khi bạn muốn coi NULL là 0:

SELECT sum(coalesce(luong, 0)) / count(*) FROM nv;   -- 1500

Và count(*) so với count(cot) là cách rẻ nhất để biết một cột có bao nhiêu ô trống.

Bốn chỗ NULL hành xử khác dự đoán

UNIQUE cho phép nhiều NULL. Tôi chèn 'A', NULL, NULL, NULL vào cột unique và cả bốn đều lọt; chèn 'A' lần hai mới bị chặn. Vì NULL = NULL cho ra NULL, hai NULL không "bằng nhau" nên không vi phạm ràng buộc.

PostgreSQL 15 trở lên có cách đổi hành vi đó:

CREATE TABLE u2 (ma text UNIQUE NULLS NOT DISTINCT);

Đo được: chèn NULL lần hai bị từ chối.

ORDER BY đặt NULL ở hai đầu khác nhau tuỳ hướng.

ORDER BY luong ASC  -> 2000, 2500, 3000, NULL, NULL
ORDER BY luong DESC -> NULL, NULL, 3000, 2500, 2000

Đổi hướng sắp xếp là đổi luôn vị trí của NULL. Nếu bạn phân trang theo cột có thể NULL, khai tường minh NULLS LAST để thứ tự ổn định.

IS NULL dùng được chỉ mục. Đây là điều nhiều người tin ngược:

Index Only Scan using lon_v_idx on lon  (actual rows=1000)
Execution Time: 1.515 ms

Tìm 1.000 dòng NULL trong một triệu dòng mất 1,5 ms bằng Index Only Scan. B-tree của PostgreSQL lưu cả NULL, nên không cần chỉ mục một phần riêng cho việc này.

NOT EXISTS nhanh hơn NOT IN ngay cả khi không có NULL: 20 ms so với 30 ms. Bộ tối ưu biến nó thành anti-join, còn NOT IN phải giữ ngữ nghĩa ba trạng thái nên bị hạn chế hơn.

Khi nào cho phép NULL

Quy tắc thực dụng: NOT NULL là mặc định, cho phép NULL là quyết định phải giải thích được.

NULL đúng khi nó nghĩa là "chưa biết" hoặc "không áp dụng": ngày kết thúc của hợp đồng đang chạy, số điện thoại khách chưa cung cấp. Nó sai khi bạn dùng nó thay cho giá trị mặc định — số lượng bằng NULL thay vì 0, danh sách rỗng bằng NULL thay vì mảng rỗng.

Mỗi cột cho phép NULL là một chỗ mọi truy vấn về sau phải nhớ xử lý. Với cột chắc chắn luôn có giá trị, khai NOT NULL ngay từ đầu rẻ hơn nhiều so với thêm về sau — thêm về sau thì PostgreSQL phải quét cả bảng để kiểm.

Thử ba mươi giây

Tìm mọi cột cho phép NULL nhưng thật ra chưa bao giờ có NULL — đó là những cột nên siết lại:

SELECT c.table_name, c.column_name
FROM information_schema.columns c
WHERE c.table_schema = 'public' AND c.is_nullable = 'YES'
ORDER BY c.table_name, c.ordinal_position;

Rồi với từng cột nghi ngờ:

SELECT count(*) AS tong, count(cot_nghi_ngo) AS co_gia_tri FROM bang;

Hai số bằng nhau nghĩa là cột đó chưa từng có NULL — thêm NOT NULL sẽ bắt được lỗi sớm hơn thay vì để nó lan vào truy vấn.

Và quét mã tìm NOT IN với truy vấn con:

grep -rn "NOT IN *(" --include="*.sql" --include="*.java" --include="*.go" .

Mỗi chỗ như vậy là một quả bom hẹn giờ, chờ ngày cột bên trong có dòng NULL đầu tiên.

Phần sau đọc EXPLAIN: từng nút trong kế hoạch thực thi nghĩa là gì.