SQL NOT IN và NULL: vì sao truy vấn loại trừ trả về rỗng?

Danh sách có ba người dùng, chỉ một người nằm trong bảng loại trừ, nhưng truy vấn lại không trả ai. Trước khi nghi ngờ JOIN hoặc index, hãy kiểm tra xem subquery của NOT IN có chứa NULL không. Đây là lỗi về ý nghĩa điều kiện, không phải lỗi tốc độ truy vấn.

Bài viết dùng ID nguyên, bảng nhỏ và quy tắc đơn giản: chọn những người dùng không có ID tương ứng trong bảng loại trừ. Hãy chạy ví dụ ở database thử nghiệm; không tạo hay sửa các bảng minh họa trên production.

1. Một bộ dữ liệu đủ nhỏ để nhìn thấy lỗi

CREATE TABLE users (id INTEGER PRIMARY KEY);
CREATE TABLE exclusions (user_id INTEGER);

INSERT INTO users (id) VALUES (1), (2), (3);
INSERT INTO exclusions (user_id) VALUES (1), (NULL);

Trong ví dụ, users.id luôn có giá trị. Bảng exclusions chứa một hàng chưa xác định user_id. Với quy tắc đang xét, hàng chưa xác định này không được dùng để loại mọi người dùng khác.

SELECT u.id
FROM users AS u
WHERE u.id NOT IN (SELECT e.user_id FROM exclusions AS e)
ORDER BY u.id;

Kết quả là rỗng, thay vì 2 và 3 như nhiều người chờ đợi. Việc thêm index không thay đổi kết quả logic này.

2. NOT IN không đơn giản là “không thấy thì lấy”

SQL có trạng thái chưa xác định khi so sánh thông thường với NULL. 2 NOT IN (1, NULL) không thành true: số 2 khác 1, nhưng phép so sánh với giá trị chưa biết không chứng minh được điều kiện loại trừ. WHERE chỉ giữ hàng có điều kiện true.

Quy tắc này được trình bày trong PostgreSQL — Subquery Expressions. Không nên diễn giải NULL là một ID đặc biệt hoặc số 0.

3. Dùng NOT EXISTS cho câu hỏi về sự tồn tại

Với yêu cầu “không tồn tại hàng loại trừ có user_id bằng ID hiện tại”, viết trực tiếp điều đó:

SELECT u.id
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM exclusions AS e
  WHERE e.user_id = u.id
)
ORDER BY u.id;

Ví dụ trả 2 và 3. Hàng có user_id NULL không làm điều kiện bằng ID hiện tại thành true, nên không tạo ra một hàng khớp. Những hàng loại trừ trùng ID cũng không làm kết quả ngoài bị nhân bản.

Đây là lựa chọn theo ý nghĩa nghiệp vụ, không phải tuyên bố NOT EXISTS luôn nhanh hơn NOT IN. Sau khi có kết quả đúng, mới so sánh execution plan và chi phí trên dữ liệu đại diện.

4. Khi nào lọc NULL khỏi subquery là hợp lý?

Nếu hợp đồng dữ liệu quy định rằng user_id chưa xác định phải được bỏ qua, có thể giữ NOT IN và lọc rõ ràng:

SELECT u.id
FROM users AS u
WHERE u.id NOT IN (
  SELECT e.user_id
  FROM exclusions AS e
  WHERE e.user_id IS NOT NULL
)
ORDER BY u.id;

Truy vấn này cũng trả 2 và 3 trên fixture. Tuy nhiên, nếu NULL là dấu hiệu import lỗi hoặc thiếu quan hệ bắt buộc, việc âm thầm bỏ qua có thể che mất vấn đề cần xử lý. Chọn cách sửa schema và luồng ghi theo nguyên nhân, không chỉ làm màn hình hiện dữ liệu trở lại.

Không thay NULL bằng một ID giả qua COALESCE chỉ để tránh bẫy: giá trị giả có thể đụng dữ liệu thật hoặc làm contract khó hiểu. Khi kiểm tra giá trị thiếu, dùng IS NULL hoặc IS NOT NULL; tài liệu Comparison Functions and Operators giải thích khác biệt với phép bằng thông thường.

5. Kiểm tra cả phía trái và phạm vi dữ liệu

Trong bài, khóa phía trái không nullable. Nếu đổi sang một cột tùy chọn, cần quyết định hàng có NULL ở phía trái được giữ hay loại. Với phép bằng thông thường, NOT EXISTS có thể giữ hàng không khớp đó; NOT IN có hành vi khác tùy dữ liệu phía phải. Không coi hai cách viết là thay thế máy móc trong mọi schema.

Nếu ứng dụng multi-tenant, câu hỏi đầy đủ có thể là “không có hàng loại trừ của cùng tenant”. Khi đó cần điều kiện tenant tương ứng trong truy vấn và kiểm soát scope từ danh tính đã xác thực. Một ID khớp ở tổ chức khác không được tự làm mất kết quả của tổ chức hiện tại.

6. Các ca kiểm thử cần có

• Bảng loại trừ rỗng: cả ba ID được giữ.
• Bảng chỉ có ID 1: kết quả là 2 và 3.
• Bảng có ID 1 và NULL: truy vấn gốc rỗng, hai phương án có chủ đích trả 2 và 3.
• Bảng có nhiều hàng ID 1: kết quả ngoài không bị nhân bản.
• Bảng chỉ có NULL: kiểm tra đúng quy tắc bỏ qua dữ liệu chưa xác định.
• Nếu khóa ngoài nullable hoặc có tenant, bổ sung fixture riêng cho các trường hợp đó.

Các query hiển thị trong bài cùng những ca cơ bản đã được chạy bằng PDO SQLite trong bộ nhớ. Kiểm tra này không ghi database trên đĩa, không truy cập database production và không phải benchmark PostgreSQL. Tài liệu SQLite — SQL Language Expressions có bảng hành vi IN/NOT IN, bao gồm tập bên phải rỗng.

7. Quy trình review truy vấn loại trừ

1. Viết câu mô tả nghiệp vụ và kết quả mong đợi cho fixture nhỏ.
2. Kiểm tra nullability của cả hai vế và nguồn dữ liệu trong subquery.
3. Chọn NOT EXISTS hoặc lọc NULL theo contract đã thống nhất.
4. Assert danh sách ID cụ thể, không chỉ số lượng hàng.
5. Sau khi đúng logic, mới kiểm tra index và plan trên engine thực tế.

Khi một truy vấn loại trừ đột nhiên trả rỗng, hãy tìm NULL trước khi tối ưu. Một fixture nhỏ và quy tắc dữ liệu rõ ràng thường giúp xác định lỗi nhanh hơn việc thử nhiều biến thể SQL trên tập dữ liệu lớn.