Anti-join: tìm dòng không khớp

Bạn sẽ học được

• Tìm dòng không có bản ghi liên quan bằng ba cách: anti-join, NOT EXISTS, NOT IN.
• Hiểu vì sao NOT IN gặp NULL có thể trả về không dòng nào.
• Áp dụng anti-join với điều kiện phụ, ví dụ "chưa có đơn hoàn tất".
• Chọn cách viết phù hợp khi dữ liệu có thể chứa NULL.
• Kiểm tra kết quả anti-join bằng phép đối chiếu tổng.

Khái niệm cốt lõi

Câu hỏi "không có" luôn khó hơn câu hỏi "có". Với "có", bạn JOIN và đếm. Với "không", phải tìm những dòng mà phép nối không tìm được cặp — gọi là anti-join.

Cách một, dùng LEFT JOIN rồi lọc NULL:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Kết quả: Hà Bùi. Cách đọc: giữ mọi khách, ghép đơn nếu có; dòng nào không ghép được sẽ có o.id là NULL. Điều kiện phải dùng cột khóa của bảng phải (o.id), vì cột này không bao giờ NULL trong dữ liệu thật — nếu tồn tại dòng thì nó có id.

Cách hai, dùng NOT EXISTS:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Kết quả cũng là Hà Bùi. EXISTS kiểm tra "có ít nhất một dòng thỏa điều kiện không" và trả TRUE/FALSE; NOT EXISTS đảo lại. Điểm hay: truy vấn con tương quan — nó tham chiếu c.id từ truy vấn ngoài, nên được đánh giá cho từng khách. PostgreSQL tối ưu phép này bằng hash anti-join hoặc nested loop có chỉ mục, thường nhanh hơn LEFT JOIN + IS NULL khi bảng lớn.

Cách ba, dùng NOT IN:

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Kết quả cũng đúng: Hà Bùi. Nhưng NOT IN có một cái bẫy nghiêm trọng liên quan đến NULL.

Nếu danh sách con chứa một giá trị NULL, NOT IN sẽ trả không dòng nào, bất kể dữ liệu thật. Lý do: x NOT IN (1, 2, NULL) tương đương x <> 1 AND x <> 2 AND x <> NULL. Mà x <> NULL luôn là NULL, không phải TRUE, nên toàn bộ điều kiện không bao giờ đúng. Xem ví dụ:

SELECT name
FROM customers
WHERE email NOT IN (SELECT email FROM customers WHERE city = 'Hà Nội');

Trong danh sách email của khách Hà Nội có NULL (Dũng Phạm), nên câu này trả 0 dòng — dù rõ ràng có nhiều khách ở thành phố khác với email khác. Muốn dùng NOT IN an toàn, phải loại NULL trong truy vấn con:

SELECT name
FROM customers
WHERE email NOT IN (
  SELECT email FROM customers WHERE city = 'Hà Nội' AND email IS NOT NULL
);

Khi đó kết quả có 8 dòng (mười khách có email trừ An Nguyễn và Khánh Hồ). Nhưng thà tránh hẳn NOT IN với cột có thể NULL — hãy dùng NOT EXISTS, nó xử lý NULL đúng theo ngữ nghĩa "không tồn tại dòng nào khớp".

Bảng so sánh ba cách:

Anti-join cũng dùng được với điều kiện phụ. "Khách chưa có đơn hoàn tất" không phải "khách chưa có đơn" — khác biệt nằm ở điều kiện trong truy vấn con:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'completed'
);

Kết quả bốn người: Hà Bùi (không đơn), Em Võ (chỉ pending), Inh Đỗ (chỉ cancelled), Nga Lý (chỉ pending). Đây là câu hỏi nghiệp vụ rất thật: "khách nào chưa từng mua thành công" — dùng cho chiến dịch nhắc lại.

Một biến thể khác của anti-join là tìm dòng mồ côi — bản ghi con trỏ đến bản ghi cha không tồn tại. Ví dụ đơn hàng không có dòng chi tiết:

SELECT o.id
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE oi.id IS NULL;

Trên dữ liệu hiện tại, kết quả rỗng — mọi đơn đều có ít nhất một món. Trong hệ thống thật, truy vấn này dùng để phát hiện dữ liệu hỏng sau khi nhập từ hệ thống cũ.

Cuối cùng, anti-join thường đi kèm phép kiểm chứng: số dòng tìm được cộng với số dòng có cặp phải bằng tổng bảng. Ở đây 11 khách có đơn + 1 khách chưa có đơn = 12. Nếu tổng không khớp, có thể JOIN của bạn đang nhân dòng.

Ví dụ chạy được

Sản phẩm chưa bán được lần nào:

SELECT p.name, p.price
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Kết quả một dòng: Giá kê laptop, 390.000. Đây là sản phẩm duy nhất không xuất hiện trong order_items. Với LEFT JOIN:

SELECT p.name, p.price
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

Cùng kết quả. Nếu muốn thấy cả số lượng tồn kho để quyết định xử lý, cách LEFT JOIN tiện hơn vì đã có sẵn cột p.*:

SELECT p.name, p.stock, p.price
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

Giá kê laptop còn 7 chiếc trong kho, giá 390.000.

Đếm khách chưa có đơn hoàn tất:

SELECT COUNT(*) AS so_khach
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'completed'
);

Kết quả: 4. Đối chiếu: 8 khách có đơn hoàn tất + 4 khách chưa có = 12. Phép kiểm chứng này nên chạy mỗi lần làm anti-join có điều kiện phụ.

Liệt kê khách chưa có đơn hoàn tất kèm lý do gần nhất:

SELECT c.name,
       COUNT(o.id) AS tong_don,
       COUNT(*) FILTER (WHERE o.status = 'pending') AS don_pending
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(*) FILTER (WHERE o.status = 'completed') = 0
ORDER BY c.name;

Bốn dòng: Em Võ (1 đơn, 1 pending), Hà Bùi (0 đơn), Inh Đỗ (1 đơn cancelled, 0 pending), Nga Lý (1 đơn, 1 pending). COUNT(*) FILTER là cú pháp PostgreSQL đếm có điều kiện, rất tiện cho dạng báo cáo này — nó tương đương SUM(CASE WHEN ... THEN 1 ELSE 0 END) nhưng ngắn hơn.

Minh họa bẫy NOT IN:

SELECT COUNT(*) AS so_dong
FROM customers
WHERE email NOT IN (SELECT email FROM customers WHERE city = 'Hà Nội');

Kết quả: 0. Bỏ AND email IS NOT NULL trong truy vấn con:

SELECT COUNT(*) AS so_dong
FROM customers
WHERE email NOT IN (
  SELECT email FROM customers WHERE city = 'Hà Nội' AND email IS NOT NULL
);

Kết quả: 8. Cùng ý định, khác hẳn kết quả chỉ vì một NULL. Đây là lý do nên mặc định dùng NOT EXISTS.

Một ví dụ anti-join hai chiều: khách chưa mua gì và sản phẩm chưa bán gì trong cùng một báo cáo không cần thiết — hãy viết hai câu riêng, mỗi câu một câu hỏi. Anti-join càng phức tạp càng dễ sai; chia nhỏ rồi ghép kết quả ở tầng ứng dụng thường rõ ràng hơn.

Thực hành

1. Tìm khách chưa có đơn bằng NOT EXISTS. Kết quả: Hà Bùi.
2. Tìm sản phẩm chưa bán bằng LEFT JOIN ... IS NULL. Kết quả: Giá kê laptop.
3. Tìm khách chưa có đơn completed bằng cả NOT EXISTS và LEFT JOIN có điều kiện trong ON. Cả hai phải trả bốn người.
4. Chạy câu NOT IN ở ví dụ trên, rồi thêm AND email IS NOT NULL và so sánh. Ghi lại hai kết quả 0 và 8 để nhớ bẫy.

Lỗi thường gặp

Lỗi đầu tiên từng khiến nhiều báo cáo "khách chưa mua" trả về rỗng và bị hiểu nhầm là "tất cả khách đã mua". Nó không báo lỗi, không cảnh báo — chỉ im lặng sai. Hãy coi NOT IN với cột có thể NULL là điều cấm.

Tóm tắt

• Anti-join tìm dòng không có bản ghi liên quan; ba cách viết: LEFT JOIN + IS NULL, NOT EXISTS, NOT IN.
• NOT EXISTS an toàn với NULL và thường là lựa chọn tốt nhất.
• NOT IN trả 0 dòng nếu danh sách con chứa NULL — tránh dùng với cột nullable.
• Anti-join nhận điều kiện phụ trong truy vấn con để trả lời "chưa từng làm X".
• Kiểm tra chéo: số dòng có cặp + số dòng không có cặp = tổng bảng.