SQL LEFT JOIN: đặt điều kiện ở ON hay WHERE để không làm mất dòng?

Báo cáo cần hiển thị mọi khách hàng, kể cả người chưa có đơn đã thanh toán. Nhưng sau khi thêm điều kiện trạng thái, một số khách hàng biến mất. Dù câu lệnh vẫn có chữ LEFT JOIN, vị trí của điều kiện có thể làm kết quả không còn đúng mong muốn.

Bài viết dùng fixture nhỏ để kiểm tra yêu cầu: giữ mọi khách hàng và chỉ nối những đơn có status = 'paid'. SQL tạo bảng chỉ dành cho database thử nghiệm, không chạy trên production.

1. Chuẩn bị ba trường hợp khác nhau

CREATE TABLE customers (id INTEGER PRIMARY KEY);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO customers (id) VALUES (1), (2), (3);
INSERT INTO orders (id, customer_id, status) VALUES
  (10, 1, 'pending'),
  (11, 1, 'paid'),
  (12, 2, 'pending');

Khách 1 có cả đơn pending và paid; khách 2 chỉ có pending; khách 3 chưa có đơn. Fixture này quan trọng vì “chưa có đơn nào” khác “có đơn nhưng không đơn nào đạt điều kiện”. Một test chỉ gồm khách 1 và khách 3 dễ bỏ sót lỗi.

2. Điều kiện ở WHERE lọc kết quả sau phép nối

SELECT c.id, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

Truy vấn chỉ trả khách 1 với order_id 11. Khách 2 bị loại vì đơn nối vào không paid. Khách 3 có các cột phía orders được bổ sung NULL, nên điều kiện so sánh trạng thái cũng không thành true.

PostgreSQL — Table Expressions giải thích sự khác biệt giữa điều kiện nối và lọc sau nối đối với outer join. Đây là mô hình ngữ nghĩa để hiểu kết quả, không phải cam kết engine luôn thực thi vật lý theo một thứ tự cố định.

Nếu yêu cầu thật sự là chỉ lấy khách có đơn paid, việc loại dòng có thể đúng. Lỗi xuất hiện khi mục tiêu vẫn là giữ mọi khách hàng nhưng điều kiện đã diễn đạt một tập kết quả khác.

3. Đặt tiêu chí chọn đơn phù hợp vào ON

SELECT c.id, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
ORDER BY c.id, o.id;

Kết quả gồm (1, 11), (2, NULL), (3, NULL). Khách 2 vẫn có hàng đại diện vì không có đơn phù hợp với toàn bộ điều kiện ON. Điều này khớp yêu cầu đầu bài.

Điều kiện chọn khách phía trái, chẳng hạn chỉ khách đang hoạt động, vẫn có thể thuộc WHERE. Hãy phân biệt rõ “khách nào xuất hiện trong báo cáo” với “đơn nào được nối vào khách đó”, thay vì chuyển tất cả điều kiện sang ON một cách máy móc.

4. Vì sao OR order_id IS NULL chưa sửa được mọi trường hợp?

SELECT c.id, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid' OR o.id IS NULL
ORDER BY c.id, o.id;

Biến thể này trả khách 1 và khách 3, nhưng vẫn mất khách 2. Khách 2 đã nối được với order 12 nên không có hàng bổ sung NULL. Sau đó WHERE loại đơn pending đó; phép nối không tự tạo thêm một hàng NULL để bù lại.

Trong review, hãy yêu cầu kết quả mong đợi cho cả ba nhóm của fixture. Việc nhìn một màn hình có thêm vài dòng trở lại chưa chứng minh truy vấn đã giữ đầy đủ dữ liệu.

5. Đếm đơn đúng sau LEFT JOIN

SELECT c.id,
       COUNT(*) AS joined_rows,
       COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
GROUP BY c.id
ORDER BY c.id;

Với fixture, joined_rows đều bằng 1, còn paid_orders lần lượt là 1, 0, 0. COUNT(*) đếm cả hàng đại diện khách chưa có đơn phù hợp; COUNT(o.id) không đếm NULL. Chọn một khóa phía phải không nullable khi thực sự có bản ghi, thay vì đếm một cột nghiệp vụ có thể thiếu dữ liệu. Xem PostgreSQL — Aggregate Functions.

Nếu nối thêm một quan hệ một-nhiều khác, một đơn có thể bị nhân lên nhiều hàng. COUNT(o.id) không tự giải quyết fan-out đó. Cần kiểm tra cardinality và cân nhắc gom nhóm từng quan hệ trước khi nối, theo đúng đại lượng báo cáo muốn đo.

6. Kiểm thử cả tập ID lẫn giá trị tổng hợp

Các query trong cả bản Việt và Anh đã được chạy bằng PDO SQLite trong bộ nhớ. Kiểm tra xác nhận ba kết quả khác nhau của WHERE, ON và OR IS NULL, đồng thời thử khi không có đơn nào và khi thêm một đơn paid cho khách 1. Không có database trên đĩa hoặc database production nào bị thay đổi.

• Assert danh sách cặp customer_id/order_id với thứ tự rõ ràng.
• Kiểm tra khách không có đơn và khách chỉ có đơn không phù hợp.
• Kiểm tra nhiều đơn phù hợp để biết số hàng tăng theo yêu cầu.
• Assert số 0 trong aggregate, không chỉ sự tồn tại của khách.
• Với multi-tenant, kiểm tra scope và điều kiện nối đầy đủ.

Thử nghiệm nhỏ xác minh ý nghĩa truy vấn, không phải benchmark hay cam kết plan trên PostgreSQL. Khi triển khai, chạy lại trên engine thật với schema, dữ liệu và index tương ứng.

7. Một cách review dễ áp dụng

1. Viết rõ nhóm đối tượng phải luôn được giữ.
2. Tách điều kiện lọc đối tượng chính khỏi điều kiện chọn bản ghi liên quan.
3. Đọc mọi WHERE tham chiếu bảng phải và giải thích trường hợp NULL.
4. Kiểm tra fan-out trước khi tính COUNT hoặc SUM.
5. Chốt kết quả bằng fixture nhỏ, sau đó mới tối ưu performance.

LEFT JOIN không đảm bảo mọi dòng phía trái còn xuất hiện sau tất cả các bộ lọc. Hiểu điều kiện nào quyết định một cặp khớp và điều kiện nào quyết định hàng cuối được giữ sẽ giúp báo cáo phản ánh đúng nghiệp vụ.