LEFT JOIN

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

• Giữ mọi dòng của bảng trái bằng LEFT JOIN, kể cả khi không có cặp.
• Đếm đúng sau LEFT JOIN bằng COUNT(cot) thay COUNT(*).
• Nhận ra bẫy đặt điều kiện bảng phải trong WHERE làm mất tính "outer".
• Dùng LEFT JOIN ... IS NULL để tìm dòng không có cặp.
• Phân biệt LEFT JOIN với RIGHT JOIN và chọn cách viết dễ đọc.

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

INNER JOIN vứt bỏ dòng không tìm được cặp. Nhiều câu hỏi thật lại cần điều ngược lại: "khách nào chưa mua hàng", "sản phẩm nào chưa bán được", "nhân viên nào chưa có phòng ban". LEFT JOIN giữ mọi dòng của bảng bên trái và điền NULL vào các cột bên phải khi không có cặp:

SELECT c.name, o.id AS don
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

Mười bảy dòng — 16 dòng ứng với 16 đơn, cộng một dòng cho Hà Bùi với don = NULL. Nếu chỉ chọn c.name thì mới thấy đúng 12 khách; ở đây mỗi đơn tạo một dòng, nên khách mua nhiều lần xuất hiện nhiều lần.

Cụm từ cần nhớ: bảng trái được giữ nguyên, bảng phải là phần "có thì thêm". Trong FROM customers c LEFT JOIN orders o, customers là trái. Nếu đổi thứ tự bảng, kết quả đổi nghĩa — orders o LEFT JOIN customers c giữ mọi đơn, và vì mọi đơn đều có khách, kết quả giống INNER JOIN 16 dòng. Thứ tự bảng không phải chi tiết hình thức.

Bẫy đầu tiên và phổ biến nhất: đếm sau LEFT JOIN.

SELECT c.name, COUNT(*) AS so_don
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY so_don DESC, c.name;

Hà Bùi được đếm 1 đơn, dù cô chưa mua gì. Lý do: LEFT JOIN tạo một dòng cho cô ấy với các cột orders là NULL, và COUNT(*) đếm mọi dòng bất kể nội dung. Muốn đếm đơn thật, đếm một cột của bảng phải:

SELECT c.name, COUNT(o.id) AS so_don
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY so_don DESC, c.name;

COUNT(o.id) bỏ qua NULL, nên Hà Bùi nhận 0. Đây là câu trả lời đúng cho "số đơn mỗi khách, kể cả khách chưa mua". Quy tắc: sau LEFT JOIN, đếm cột của bảng phải, không đếm *.

Bẫy thứ hai: đặt điều kiện bảng phải trong WHERE.

SELECT c.name, COUNT(o.id) AS so_don
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'   -- bẫy
GROUP BY c.id, c.name;

Kết quả chỉ còn 8 khách có đơn hoàn tất. Hà Bùi biến mất — vì dòng NULL của cô ấy có o.status = NULL, và NULL = 'completed' không phải TRUE mà là NULL, nên bị WHERE loại. Câu lệnh đã trở thành INNER JOIN trên thực tế.

Muốn giữ mọi khách và chỉ đếm đơn hoàn tất, đặt điều kiện vào ON:

SELECT c.name, COUNT(o.id) AS so_don_hoan_tat
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'completed'
GROUP BY c.id, c.name
ORDER BY so_don_hoan_tat DESC, c.name;

Bây giờ mười hai khách đều có mặt; những người không có đơn hoàn tất nhận 0. Đây là khác biệt cốt lõi giữa điều kiện trong ON và trong WHERE khi có outer join — với INNER JOIN thì hai cách tương đương, với LEFT JOIN thì không.

LEFT JOIN cũng dùng để tìm dòng không có cặp:

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ó; sau đó chỉ lấy những dòng mà phần đơn là NULL — tức là không ghép được. Đây là anti-join, và SQL có cách viết khác gọn hơn bằng NOT EXISTS (bài năm). Điều cần nhớ ở đây: IS NULL trên cột khóa của bảng phải là dấu hiệu "không có cặp".

RIGHT JOIN là LEFT JOIN đảo chiều: A RIGHT JOIN B giữ mọi dòng của B. Bạn hầu như không bao giờ cần nó — đổi thứ tự hai bảng rồi dùng LEFT JOIN cho kết quả tương đương và dễ đọc hơn. Nhiều nhóm kỹ thuật quy ước cấm RIGHT JOIN vì lý do này.

FULL OUTER JOIN giữ cả hai phía, điền NULL ở cả hai bên khi thiếu cặp. Nó hữu ích khi rà soát dữ liệu hai hệ thống, ví dụ so bảng khách cũ và mới để tìm dòng chỉ có ở một bên. Trong báo cáo thường ngày, LEFT JOIN đủ dùng.

Ví dụ chạy được

Mọi khách kèm số đơn, kể cả người chưa mua:

SELECT c.name, COUNT(o.id) AS so_don
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY so_don DESC, c.name;

Mười hai dòng: Bình Trần 3; An Nguyễn, Chi Lê, Giang Đặng mỗi người 2; Dũng, Em, Inh, Khánh, Linh, Minh, Nga mỗi người 1; Hà Bùi 0. Tổng số đơn 16. Đây là mẫu truy vấn bạn sẽ dùng lại rất nhiều — nó là nền của bài luyện cuối chương, chỉ khác điều kiện giữ Hà Bùi hay không.

Mọi sản phẩm kèm số dòng đã bán:

SELECT p.name, COUNT(oi.id) AS so_dong_ban
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY so_dong_ban DESC, p.name;

Chín dòng. Giá kê laptop có so_dong_ban = 0 — sản phẩm duy nhất chưa bán được. Nếu dùng INNER JOIN, sản phẩm này biến mất và bạn sẽ không bao giờ biết nó tồn kho lâu. Câu hỏi "hàng nào ế" bắt buộc phải LEFT JOIN.

Lọc điều kiện trong ON để giữ mọi khách:

SELECT c.name, COUNT(o.id) AS so_don_pending
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'pending'
GROUP BY c.id, c.name
ORDER BY so_don_pending DESC, c.name;

Mười hai dòng, trong đó Em Võ và Nga Lý mỗi người 1 đơn pending; mười khách còn lại 0. Đối chiếu với bản đặt điều kiện trong WHERE: khi đó kết quả chỉ còn hai dòng, vì mười khách kia bị loại. Sự khác biệt này là bài học đáng nhớ nhất của bài.

Tìm khách chưa từng đặt đơn:

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

Hà Bùi. Chạy thêm bản đếm để chắc rằng chỉ có một người:

SELECT COUNT(*) AS so_khach_chua_mua
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Kết quả: 1.

Kết hợp LEFT JOIN với điều kiện trên bảng trái — an toàn:

SELECT c.name, COUNT(o.id) AS so_don
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Hà Nội'
GROUP BY c.id, c.name
ORDER BY so_don DESC;

Kết quả ba khách Hà Nội: An 2, Dũng 1, Khánh 1. Điều kiện lọc trên bảng trái không làm mất tính "giữ mọi dòng trái" — nó chỉ thu hẹp tập khách trước khi ghép. Chỉ điều kiện trên bảng phải mới gây bẫy.

Thực hành

1. Viết LEFT JOIN khách — đơn, đếm số đơn bằng COUNT(o.id). Xác nhận Hà Bùi có 0, không phải 1.
2. Viết LEFT JOIN sản phẩm — dòng hàng. Sản phẩm nào có 0? Kết quả: Giá kê laptop.
3. Chạy cùng truy vấn với điều kiện o.status = 'completed' đặt lần lượt trong WHERE và trong ON. Đếm số dòng hai kết quả: 8 khách so với 12 khách.
4. Tìm khách chưa có đơn completed bằng anti-join. Kết quả gồm bốn người: Hà Bùi, Em Võ, Inh Đỗ, Nga Lý.

Lỗi thường gặp

Lỗi thứ hai khó thấy nhất vì câu lệnh vẫn chạy và trả kết quả "hợp lý". Cách tự kiểm tra: đếm số dòng kết quả. Nếu bạn viết LEFT JOIN mà kết quả có đúng số dòng của INNER JOIN cùng câu, hãy nghi ngờ một điều kiện WHERE đang lọc bảng phải.

Tóm tắt

• LEFT JOIN giữ mọi dòng bảng trái, điền NULL cho phần thiếu bên phải.
• Sau LEFT JOIN, đếm bằng COUNT(cot_bang_phai) để không đếm dòng rỗng.
• Điều kiện trên bảng phải đặt trong ON để giữ tính outer; đặt trong WHERE sẽ biến thành inner.
• LEFT JOIN ... WHERE cot_phai IS NULL là anti-join — tìm dòng không có cặp.
• RIGHT JOIN viết lại được thành LEFT JOIN bằng cách đổi thứ tự bảng.