Một dòng NULL làm NOT IN trả về rỗng — không lỗi, không cảnh báo, và còn chạy nhanh hơn

Hình dung một tờ khai có một ô để trống. Ai đó hỏi: "số điện thoại người này có khác 555-1234 không?" Ô trống, nên câu trả lời trung thực không phải "có" cũng không phải "không" — mà là "không biết". Và "không biết" kết hợp với bất cứ điều gì khác vẫn ra "không biết". Đó chính là toàn bộ câu chuyện của NULL: nó 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ì.

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);

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ị:

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à:

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":

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.

Mẫu số chung

Cái ô để trống ở đầu bài chỉ ra một sự thật đúng ở mọi ngôn ngữ: một giá trị nghĩa là "vắng / chưa biết" không thể tuân theo đại số hai-trạng-thái bình thường. Mọi phép toán trên nó phải lan truyền cái chưa-biết, và so bằng với nó không thể ra true — vì bạn không thể biết hai cái chưa- biết có bằng nhau không. Code viết cho hai trạng thái (đúng/sai) sẽ âm thầm bỏ sót hoặc làm hỏng trạng thái thứ ba.

Cùng cái bẫy ấy hiện ra khắp nơi, đôi khi ở chỗ ta không ngờ:

• Số thực có một NULL của riêng nó: NaN theo chuẩn IEEE-754. NaN != NaN cho ra true (đối xứng với NULL = NULL không ra true); mọi phép tính chạm NaN đều ra NaN (lan truyền); và mọi so sánh với NaN đều false, nên nó phá sort, phá ==, phá min/max y hệt NULL phá WHERE. NaN chính là NULL của thế giới dấu phẩy động.
• null của Java là "lỗi tỷ đô" theo lời chính người tạo ra nó, vì nó lẻn qua mọi phép toán bình thường rồi nổ NullPointerException ở nơi xa nhất. Câu trả lời hiện đại đúng như bài học của SQL: biến "vắng" thành một kiểu riêng bắt buộc xử lý — Optional<T> của Java, kiểu nullable String? của Kotlin và Swift bắt kiểm ngay lúc biên dịch, Option<T> của Rust (không có null).
• Và chính SQL cũng cho lối thoát cùng tinh thần ấy: IS DISTINCT FROM, IS NULL là các toán tử cố ý coi cái chưa-biết như một thứ so sánh được, thay vì để nó lẻn qua = và <>.

Nên câu hỏi đúng khi một truy vấn (hay một hàm) trả về ít hơn mong đợi mà không báo lỗi không phải "tôi lọc sai chỗ nào" mà là "có cột nào ở đây mang trạng thái chưa biết, và toán tử tôi dùng xử lý trạng thái đó ra sao". Muốn tìm mọi cột cho phép NULL nhưng thật ra chưa bao giờ có NULL — những cột nên siết NOT NULL — soát nhanh:

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ờ, count(*) so với count(cot) — hai số bằng nhau nghĩa là cột đó chưa từng có NULL:

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

Và quét mã tìm NOT IN với truy vấn con — mỗi chỗ là một quả bom hẹn giờ chờ ngày cột bên trong có dòng NULL đầu tiên:

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

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