OUTER JOIN — NULL trap biến LEFT JOIN thành INNER

TL;DR: OUTER JOIN giữ row không match bằng cách bù NULL vào phía thiếu. Bug phổ biến nhất: filter WHERE t.status = 'done' sau LEFT JOIN biến nó thành INNER JOIN vì NULL = 'done' cho UNKNOWN — row bị loại. Fix: di chuyển predicate của bảng nullable vào ON. Sau LEFT JOIN, dùng COUNT(t.id) không phải COUNT(*) để đếm đúng (skip NULL). Cú pháp OUTER JOIN là ANSI SQL chuẩn, hoạt động trên mọi RDBMS.

Dashboard TaskFlow cần hiển thị danh sách tất cả user kèm số task họ được assign. Bạn viết INNER JOIN — query chạy, con số trông hợp lý. Nhưng sáng hôm sau PM phản hồi: "Sao user Alice không thấy trong báo cáo?" Alice chưa được assign task nào — và INNER JOIN im lặng bỏ qua cô ấy hoàn toàn.

Bạn chuyển sang LEFT JOIN. Bây giờ Alice xuất hiện. Nhưng bạn thêm filter WHERE t.status = 'done' để chỉ đếm task hoàn thành — và Alice biến mất lần nữa. Không có error, không có warning. Bug này là một trong ba lỗi JOIN phổ biến nhất trong production. Bài này map ba dạng OUTER JOIN và hai trap thực chiến để bạn không bao giờ gặp lại.

1. Analogy — Ghép card hai cọc, giữ thừa

Hình dung hai cọc card: cọc trái là danh sách user, cọc phải là danh sách task. Ghép card theo assignee_id = user.id.

INNER JOIN chỉ giữ cặp match được — user không có task, task không có assignee, cả hai đều bị bỏ vào thùng rác. LEFT JOIN giữ toàn bộ cọc trái kể cả card không ghép được — slot bên phải bù NULL. RIGHT JOIN ngược lại, giữ toàn bộ cọc phải. FULL OUTER JOIN giữ cả hai cọc — card nào không ghép được thì slot bên kia bù NULL.

2. Ba dạng OUTER JOIN

-- LEFT JOIN: giu tat ca user, NULL bu task neu khong match
SELECT u.name, t.title
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id;
-- User chua duoc assign task nao van xuat hien voi t.title = NULL

-- RIGHT JOIN: giu tat ca tasks, NULL bu user neu khong match
-- Luon rewrite duoc thanh LEFT JOIN bang cach doi thu tu bang
SELECT u.name, t.title
FROM tasks t
RIGHT JOIN users u ON t.assignee_id = u.id;
-- Tuong duong voi: SELECT u.name, t.title FROM users u LEFT JOIN tasks t ON ...

-- FULL OUTER JOIN: giu ca hai phia
SELECT u.name, t.title
FROM users u
FULL OUTER JOIN tasks t ON t.assignee_id = u.id;
-- Bao gom: user khong co task (t.title=NULL) + task khong co assignee (u.name=NULL)

Pattern thực chiến: luôn dùng LEFT JOIN, tránh RIGHT JOIN vì RIGHT JOIN có thể rewrite thành LEFT JOIN bằng cách đảo thứ tự bảng — đọc dễ hơn, nhất quán hơn. FULL OUTER JOIN hiếm gặp, dùng cho audit và sync data.

Minh họa Venn ba dạng:

INNER JOIN        LEFT JOIN         FULL OUTER JOIN
  [A ∩ B]         [A ∪ (A∩B)]        [A ∪ B]
   users  tasks    users  tasks       users  tasks
     |  \/  |       |  \/  |          |  \/  |
     |  /\  |       |  /\  |          |  /\  |
     match           all A            all A+B

3. Big trap — WHERE-after-JOIN biến LEFT thành INNER

Đây là bug số một với OUTER JOIN. Bạn thêm filter vào WHERE sau khi đã LEFT JOIN — và không nhận ra mình vừa biến LEFT thành INNER.

-- BUG: WHERE filter sau LEFT JOIN, NULL bi loai
SELECT u.name, COUNT(t.id) AS task_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
WHERE t.status = 'done';
-- Bug: user chua co task nao co t.status = NULL
-- NULL = 'done' -> UNKNOWN -> WHERE loai row nay
-- Ket qua: chi user co task done, user chua assign mat tieu

Cơ chế: user chưa có task nào thì t.status = NULL. Predicate NULL = 'done' cho ra UNKNOWN (three-valued logic từ Module 2 bài 2 của khoá này). WHERE chỉ pass row khi predicate = TRUE — UNKNOWN bị loại. Toàn bộ user không có task done bị loại khỏi kết quả, kể cả user chưa assign task nào.

Fix — move predicate vào ON clause:

-- RIGHT: predicate cho bang nullable nam trong ON
SELECT u.name, COUNT(t.id) AS task_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id AND t.status = 'done'
GROUP BY u.id, u.name;
-- User chua co task done van xuat hien voi task_count = 0
-- ON chi loc task truoc khi join, khong loai user

Khi predicate nằm trong ON, database lọc task trước rồi mới join — user không có task match vẫn xuất hiện với NULL bù vào phía task. Khi predicate nằm trong WHERE, database lọc sau join — NULL row bị loại mất.

Quy tắc phân biệt ON vs WHERE trong LEFT JOIN:

Thử ngẫmdashboard "user active tuần này" của bạn có filter kép u.is_banned = false và t.status = 'done' sau cùng một LEFT JOIN. Bảng đưa ra ở trên xếp mỗi điều kiện đó vào đâu?

4. Pitfall — COUNT(t.id) vs COUNT(*)

Sau khi fix WHERE thành ON, user chưa assign task xuất hiện trong kết quả — nhưng bây giờ có thể gặp pitfall thứ hai: đếm sai vì dùng COUNT(*).

-- BUG: COUNT(*) dem ca row co t.id = NULL (user chua assign)
SELECT u.name, COUNT(*) AS task_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
GROUP BY u.id, u.name;
-- User khong co task: 1 row voi (u.name, NULL, NULL...)
-- COUNT(*) dem row nay la 1 -- sai spec, spec muon 0

Khi user không có task, LEFT JOIN tạo ra một row với tất cả cột bảng tasks là NULL. COUNT(*) đếm số row — row này vẫn là một row nên trả về 1. Nhưng spec muốn đếm số task, không phải số row.

-- FIX: COUNT(t.id) chi dem non-NULL
SELECT u.name, COUNT(t.id) AS task_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
GROUP BY u.id, u.name;
-- User khong co task: t.id = NULL -> COUNT(t.id) = 0 -- dung

Thử ngẫmbạn đổi COUNT(t.id) thành COUNT(t.title) trong cùng câu LEFT JOIN, nghĩ hai cột đều thuộc bảng tasks nên tương đương. Có tình huống nào khiến hai con số ra khác nhau không?

5. Demo TaskFlow — wrong vs right side-by-side

Setup: 3 user. User A có 2 task (1 done, 1 doing). User B không có task nào. User C có 1 task (done).

-- WRONG: WHERE loai user B (khong co task -> t.status = NULL -> bi loai)
SELECT u.id, u.name, COUNT(t.id) AS done_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
WHERE t.status = 'done'
GROUP BY u.id, u.name;
-- Output: 2 user (A va C), B mat -- SAI

-- RIGHT option 1: predicate trong ON
SELECT u.id, u.name, COUNT(t.id) AS done_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id AND t.status = 'done'
GROUP BY u.id, u.name;
-- Output: 3 user, B co done_count = 0 -- DUNG

-- RIGHT option 2: FILTER aggregate -- cleaner cho conditional count
SELECT u.id, u.name,
  COUNT(t.id) FILTER (WHERE t.status = 'done') AS done_count
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
GROUP BY u.id, u.name;
-- Output: 3 user, B co done_count = 0 -- DUNG
-- FILTER chi dem task co status = 'done', skip NULL tu dong

FILTER (WHERE ...) là cú pháp ANSI SQL chuẩn (PostgreSQL, SQLite 3.30+) — cho phép conditional aggregate mà không cần di chuyển predicate vào ON. MySQL chưa hỗ trợ FILTER — dùng SUM(CASE WHEN t.status = 'done' THEN 1 ELSE 0 END) làm fallback. Module 3 bài 5 của khoá này đi sâu về FILTER aggregate pattern.

6. FULL OUTER JOIN — use case thực tế

FULL OUTER JOIN hiếm trong CRUD thông thường nhưng hữu ích cho audit và reconciliation:

-- "User chua assign task nao + task chua co assignee"
-- Dung FULL OUTER + filter chi lay row khong match ca hai phia
SELECT u.name, t.title
FROM users u
FULL OUTER JOIN tasks t ON t.assignee_id = u.id
WHERE u.id IS NULL OR t.id IS NULL;
-- u.id IS NULL: task khong co assignee (khong match user nao)
-- t.id IS NULL: user chua duoc assign task nao

Hai use case thực chiến:

Audit reconciliation — so sánh hai bảng source data để tìm row chỉ tồn tại một phía:

-- Tim row chi co trong bang A ma khong co trong bang B (hoac nguoc lai)
SELECT a.id AS id_in_a, b.id AS id_in_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.ref_id = b.ref_id
WHERE a.id IS NULL OR b.id IS NULL;
-- Row voi a.id = NULL: chi co trong B, khong co trong A
-- Row voi b.id = NULL: chi co trong A, khong co trong B

Data sync — identify record lệch pha giữa hai hệ thống:

-- So sanh inventory tu hai nguon (warehouse vs ERP)
SELECT
  COALESCE(w.product_id, e.product_id) AS product_id,
  w.qty AS warehouse_qty,
  e.qty AS erp_qty
FROM warehouse_stock w
FULL OUTER JOIN erp_stock e ON w.product_id = e.product_id
WHERE (w.qty != e.qty OR w.qty IS NULL OR e.qty IS NULL)
   OR w.product_id IS NULL OR e.product_id IS NULL;
-- Bao gom: so luong khac nhau + product chi co 1 phia
-- IS DISTINCT FROM la PG/SQL:2003 -- MySQL/SQLite dung: (a != b OR a IS NULL OR b IS NULL)

7. Applied — TaskFlow leaderboard

Kết hợp LEFT JOIN, ON-predicate, COUNT(t.id), và window function để tạo leaderboard hoàn chỉnh:

-- Leaderboard: moi user kem so task done, ke ca user 0 task
SELECT
  u.id,
  u.name,
  COUNT(t.id) FILTER (WHERE t.status = 'done')              AS done,
  COUNT(t.id) FILTER (WHERE t.status IN ('todo', 'doing'))  AS active,
  RANK() OVER (
    ORDER BY COUNT(t.id) FILTER (WHERE t.status = 'done') DESC
  ) AS rank
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
GROUP BY u.id, u.name
ORDER BY rank
LIMIT 10;
-- User chua co task done van xuat hien voi done=0, active=0, rank cuoi bang

Window function RANK() OVER (...) tính rank trên toàn result set sau GROUP BY. Module 3 bài 7 của khoá này đi sâu về window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD).

8. Deep Dive — OUTER JOIN

Liên kết khoá học khác

• Khoá Spring — bài 01.4 Relationships — Optional<Order> trong JPA Hibernate map thành LEFT JOIN thế nào.

9. Tóm tắt

• LEFT JOIN giữ tất cả row bảng trái, bù NULL bên phải khi không match. RIGHT JOIN ngược lại — luôn rewrite được thành LEFT JOIN bằng cách đảo thứ tự bảng.
• FULL OUTER JOIN giữ tất cả row cả hai bảng — hiếm trong CRUD, hữu ích cho audit reconciliation và data sync.
• WHERE-after-LEFT-JOIN biến LEFT thành INNER — predicate NULL = value cho UNKNOWN, WHERE loại row → user không có task bị mất khỏi kết quả. Fix: di chuyển predicate của bảng nullable vào ON clause.
• Quy tắc ON vs WHERE: predicate lọc bảng trái (giữ nguyên) → WHERE. Predicate lọc bảng phải (nullable trong LEFT JOIN) → ON.
• COUNT(t.id) skip NULL, COUNT(*) đếm cả row NULL bù — sau LEFT JOIN, dùng COUNT(t.id) để đếm số row từ bảng phải, tránh COUNT(*) trả 1 khi user không có task.
• FILTER aggregate — COUNT(t.id) FILTER (WHERE t.status = 'done') là cách cleaner cho conditional count, không cần di chuyển predicate vào ON. Chi tiết tại Module 3 bài 5 của khoá này.
• Window function RANK() OVER — Module 3 bài 7 của khoá này đi sâu về window functions.

10. Tự kiểm tra

Bài tiếp theo: Self-join + multi-table — comment threads + N+1 pattern