Bài 15: LEFT JOIN: giữ cả người chưa có dữ liệu

Cô Mai cần gửi phụ huynh bảng "Con bạn đang học những lớp nào". Bảng phải có đủ sáu học viên, kể cả bạn chưa đăng ký lớp nào, để cô nhắc bạn đó đăng ký. Ở bài 14, bạn đã ghép students với enrollments bằng JOIN. Nhưng nếu dùng lại câu lệnh đó, một học viên sẽ biến mất khỏi báo cáo.

JOIN bỏ mất người không có dòng ghép

Hình dưới đây nối mỗi học viên với các lần đăng ký của bạn ấy. Đường nối đi từ students.id tới enrollments.student_id có cùng giá trị:

JOIN (còn gọi là INNER JOIN) chỉ giữ những cặp dòng ghép được. An có hai đường nối nên xuất hiện hai lần. Lan không có đường nối nào, nên không có mặt trong kết quả:

SELECT s.name, e.class_id, e.status
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.id
ORDER BY s.id, e.class_id;

Kết quả này đúng nếu câu hỏi là "liệt kê các lần đăng ký". Nhưng báo cáo của cô Mai hỏi về học viên, nên mỗi học viên phải có ít nhất một dòng.

LEFT JOIN giữ mọi dòng của bảng bên trái

LEFT JOIN (đầy đủ là LEFT OUTER JOIN) giữ mọi dòng của bảng bên trái, tức là bảng viết trước từ LEFT JOIN. Dòng nào ghép được thì ghép như JOIN. Dòng nào không ghép được vẫn được giữ, và các cột của bảng bên phải được điền NULL.

Chỉ cần đổi JOIN thành LEFT JOIN, câu lệnh ở mục trên trả về đủ mọi học viên:

SELECT s.name, e.class_id, e.status
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
ORDER BY s.id, e.class_id;

Bảy dòng đầu giống hệt kết quả của JOIN. Dòng thứ tám là của Lan: class_id và status đều là NULL, vì Lan không có dòng nào trong enrollments để lấy giá trị. NULL ở đây do LEFT JOIN tạo ra trong kết quả, không phải một đăng ký có thật. Bảng enrollments không hề thay đổi.

Đếm số lớp của mỗi học viên

Cô Mai muốn thêm cột "số lớp đã đăng ký" vào báo cáo. Bạn có thể nghĩ ngay tới COUNT(*), nhưng cách này cho kết quả sai với Lan.

Đếm bằng COUNT(*): Lan bị tính 1 lớp

SELECT s.name, COUNT(*) AS class_count
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

COUNT(*) đếm số dòng trong mỗi nhóm, không quan tâm giá trị trong dòng. Nhóm của Lan có đúng một dòng, chính là dòng LEFT JOIN tạo ra với các cột NULL, nên kết quả là 1.

Đếm một cột của bảng phải: Lan có 0 lớp

SELECT s.name, COUNT(e.class_id) AS class_count
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

COUNT(e.class_id) chỉ đếm những dòng có class_id khác NULL. Dòng của Lan có class_id là NULL nên không được đếm, và Lan có 0 lớp. Số của các bạn khác vẫn đúng như trước, vì các dòng của họ đều có class_id.

Điều kiện cho bảng bên phải: đặt ở ON hay ở WHERE?

Báo cáo chỉ cần các lớp đang học, tức là status = 'active'. Dung chỉ có một đăng ký đang tạm dừng (paused), còn Lan chưa đăng ký. Cả hai vẫn phải có mặt trong báo cáo.

Để chọn đúng chỗ đặt điều kiện, cần biết SQLite xử lý câu lệnh theo thứ tự nào:

Đặt ở WHERE: mất Dung và Lan

SELECT s.name, e.class_id, e.status
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
WHERE e.status = 'active'
ORDER BY s.id, e.class_id;

Sau bước ghép, dòng của Dung có status là 'paused', còn dòng của Lan có status là NULL. WHERE e.status = 'active' loại cả hai dòng, vì 'paused' khác 'active' và NULL không thỏa phép so sánh nào. Những học viên mà LEFT JOIN vừa giữ lại đã bị WHERE loại đi, nên LEFT JOIN không còn tác dụng.

Đặt trong ON: giữ đủ mọi người

SELECT s.name, e.class_id, e.status
FROM students AS s
LEFT JOIN enrollments AS e
  ON e.student_id = s.id AND e.status = 'active'
ORDER BY s.id, e.class_id;

Khi điều kiện nằm trong ON, nó chỉ quyết định đăng ký nào được ghép với học viên. Đăng ký paused của Dung không được ghép, nên Dung giống như chưa có lớp active nào. Nhưng LEFT JOIN vẫn giữ Dung với các cột NULL, giống như Lan.

Tìm những người chưa có dữ liệu

LEFT JOIN còn giúp trả lời câu hỏi ngược lại: "Học viên nào chưa đăng ký lớp nào?". Những học viên đó là các dòng mà cột của bảng phải là NULL sau khi ghép:

SELECT s.id, s.name
FROM students AS s
LEFT JOIN enrollments AS e ON e.student_id = s.id
WHERE e.student_id IS NULL;

Ở đây điều kiện phải nằm trong WHERE, vì ta muốn lọc kết quả sau khi ghép. Hãy kiểm tra NULL trên một cột không bao giờ NULL trong bảng enrollments, ví dụ e.student_id. Khi đó, giá trị NULL chỉ có thể do LEFT JOIN tạo ra. Nhớ dùng IS NULL; viết = NULL sẽ không trả về dòng nào.

Tự kiểm tra số dòng của kết quả

Trước khi gửi một báo cáo, hãy đoán trước kết quả sẽ có bao nhiêu dòng, rồi so với kết quả thật. Với students LEFT JOIN enrollments, số dòng được tính như sau:

• mỗi cặp học viên–đăng ký ghép được tạo một dòng;
• mỗi học viên không ghép được với đăng ký nào tạo thêm một dòng với các cột NULL.

Với dữ liệu mẫu, có 7 cặp ghép được và 1 học viên không ghép được (Lan), nên kết quả có 7 + 1 = 8 dòng. Nếu kết quả thật chỉ có 7 dòng, nhiều khả năng bạn đã dùng JOIN, hoặc một điều kiện trong WHERE đã loại mất Lan.

Bảng sau so sánh hai phép nối trên cùng dữ liệu mẫu:

Câu hỏi "Có cần giữ người không có dữ liệu không?" quyết định bạn chọn phép nối nào. Hãy trả lời câu hỏi đó trước khi viết câu lệnh.

Lỗi thường gặp

Tóm tắt

• JOIN chỉ giữ các cặp dòng ghép được. LEFT JOIN giữ thêm mọi dòng của bảng bên trái, và điền NULL vào các cột của bảng bên phải khi không ghép được.
• Bảng cần giữ đủ phải đứng bên trái của LEFT JOIN.
• Sau LEFT JOIN, hãy đếm một cột của bảng bên phải (như COUNT(e.class_id)), không dùng COUNT(*).
• Điều kiện chọn dòng của bảng bên phải đặt trong ON. Điều kiện lọc kết quả cuối cùng đặt trong WHERE.
• LEFT JOIN … WHERE <cột bảng phải> IS NULL tìm những dòng chưa có dữ liệu tương ứng.

Kiểm tra nhanh

Đọc thêm

• Tài liệu SQLite về phép nối trong SELECT (tiếng Anh)
• SQLBolt, bài 7: OUTER JOIN (tiếng Anh)