INNER JOIN — set intersection + nested loop teaser

Dashboard TaskFlow ở M02.7 để trống project_name vì tasks chỉ lưu project_id. Muốn hiển thị "tên user + title task họ được assign", bạn cần dữ liệu từ hai bảng cùng lúc — không thể SELECT từ một bảng duy nhất.

JOIN là cơ chế SQL kết hợp nhiều bảng theo một predicate. INNER JOIN — dạng JOIN phổ biến nhất — chỉ giữ row match ở cả hai bảng theo điều kiện join. Bài này map cú pháp, cơ chế, và một anti-pattern thực chiến hay gặp nhất.

1. Analogy — Ghép 2 cọc card

Hình dung bạn có hai cọc card:

• Cọc users: mỗi card ghi user_id và name (Alice, Bob, Carol, Dave, Eve)
• Cọc tasks: mỗi card ghi task_id, title, và assignee_id (khóa ngoại trỏ về user_id)

INNER JOIN là thao tác ghép từng card trong cọc users với từng card trong cọc tasks khi user_id = assignee_id. Card không có cặp khớp bị bỏ qua — user chưa có task nào, và task chưa có assignee, đều bị loại ra khỏi kết quả.

2. Set theory view — INNER JOIN = intersection

Xét hai tập: tập các user_id trong bảng users, và tập các assignee_id trong bảng tasks. INNER JOIN trả về row tương ứng với phần giao (intersection) của hai tập đó.

   users                tasks
  ┌──────────┐         ┌──────────┐
  │ user chưa│         │          │
  │ assign   │  ┌──────┼──────┐   │
  │ task nào │  │ INNER│JOIN  │   │
  │          │  │ (match│ both)│   │
  │          │  └──────┼──────┘   │
  └──────────┘    │    │ task chưa│
                  │    │ có       │
                  │    │ assignee │
                  │    └──────────┘
                  └── result set

Trên TaskFlow:

• User không có task nào (chưa được assign bất kỳ task nào): bị loại.
• Task có assignee_id IS NULL (task chưa assign): bị loại.
• Chỉ cặp (user, task) match theo predicate mới xuất hiện trong kết quả.

Đây là behavior đúng khi business query là "danh sách task kèm thông tin assignee" — task chưa có người nhận không cần xuất hiện. Khi bạn cần cả task chưa có assignee, chuyển sang LEFT JOIN (Module 3 bài 02 của khoá này).

3. Cú pháp — explicit JOIN ON vs implicit WHERE

-- EXPLICIT (recommended - SQL-92 standard)
SELECT u.name, t.title
FROM users u
INNER JOIN tasks t ON u.id = t.assignee_id;
-- Hoac: JOIN tasks t ON ... (INNER la default, co the bo)

-- IMPLICIT (legacy SQL-89, KHONG dung trong code moi)
SELECT u.name, t.title
FROM users u, tasks t
WHERE u.id = t.assignee_id;

Bốn lý do explicit JOIN ON win:

1. Readability — điều kiện join tách biệt khỏi điều kiện filter trong WHERE, người đọc phân biệt được "ghép bảng thế nào" vs "lọc row nào".
2. Tránh Cartesian accident — nếu bạn quên viết WHERE u.id = t.assignee_id ở dạng implicit, query vẫn chạy và trả về Cartesian product (xem mục 7).
3. OUTER JOIN bắt buộc explicit — LEFT JOIN, RIGHT JOIN, FULL JOIN không có dạng implicit; viết nhất quán explicit từ đầu tránh phải đổi style sau.
4. Maintenance — thêm filter vào WHERE clause không ảnh hưởng nhầm join condition.

Planner xử lý hai dạng identically — không có performance khác biệt. Đây thuần túy là readability và safety.

4. Nested loop teaser — cơ chế executor

PostgreSQL có 3 thuật toán JOIN. Module 7 của khoá này sẽ đi sâu; đây là teaser để bạn biết query của mình đang chạy gì bên dưới:

• Nested Loop: duyệt từng row của bảng A (outer), với mỗi row tìm row match trong bảng B (inner). Hiệu quả khi bảng outer nhỏ và có index trên join column của bảng inner.
• Hash Join: build hash table từ bảng nhỏ hơn, probe bằng bảng lớn hơn. Hiệu quả khi cả hai bảng lớn và không có index phù hợp.
• Merge Join: sort cả hai bảng theo join key rồi merge tuần tự. Hiệu quả khi dữ liệu đã có sẵn thứ tự (index scan).

Xem planner chọn thuật toán nào với EXPLAIN:

EXPLAIN SELECT u.name, t.title
FROM users u
JOIN tasks t ON u.id = t.assignee_id;
-- Plan: Hash Join  (Hash u, scan t, probe)
-- HOAC: Nested Loop with Index Scan on tasks.assignee_id

Khi tasks.assignee_id có index, planner thường chọn Nested Loop. Khi bảng lớn và không có index, planner chuyển sang Hash Join. Module 5 của khoá này phân tích composite index cho JOIN; Module 7 đi sâu vào cost model và khi nào force hint.

5. Multi-table INNER JOIN

JOIN nhiều bảng bằng cách chain nhiều INNER JOIN clause:

-- 3-table: tasks + projects + users
SELECT
  t.title,
  p.name   AS project_name,
  u.name   AS assignee
FROM tasks t
INNER JOIN projects p ON t.project_id = p.id
INNER JOIN users    u ON t.assignee_id = u.id
WHERE t.status = 'doing';

Thứ tự viết JOIN trong SQL text không ảnh hưởng kết quả — planner reorder dựa trên cost ước tính (statistics + index). Convention để code dễ đọc:

• Bắt đầu từ bảng "trung tâm" của query — trên TaskFlow thường là tasks vì đó là entity chính.
• JOIN theo quan hệ phóng ra: tasks → projects, tasks → users.

6. Alias bắt buộc cho multi-table

Khi nhiều bảng có column trùng tên (vd id, name, created_at), PostgreSQL ném ERROR: column reference "..." is ambiguous:

-- ERROR: id va name ambiguous
SELECT id, name
FROM users
JOIN projects ON users.id = projects.owner_id;
-- ERROR: column reference "id" is ambiguous

Fix bằng alias ngắn và qualify tất cả column:

-- FIX: alias + qualify every column
SELECT
  u.id   AS user_id,
  u.name AS user_name,
  p.name AS project_name
FROM users   u
JOIN projects p ON u.id = p.owner_id;

Convention alias trên TaskFlow: u (users), t (tasks), p (projects), c (comments), pm (project_members). Ngắn, nhất quán, tránh đặt alias trùng nhau trong cùng query.

7. Pitfall — Cartesian product

Ba dạng Cartesian product hay gặp, theo thứ tự nguy hiểm tăng dần:

-- BUG 1: implicit JOIN quen WHERE condition -> Cartesian product
SELECT u.name, t.title
FROM users u, tasks t;
-- 10k user x 100k task = 1 ti row -> OOM hoac hang gio chay

-- BUG 2: ON clause luon true -> Cartesian product nguy trang
SELECT u.name, t.title
FROM users u
JOIN tasks t ON 1 = 1;
-- Tuong duong Cartesian, query syntax hop le, error khong bao

-- BUG 3: typo column -> sai semantic, khong co error
SELECT u.name, t.title
FROM users u
JOIN tasks t ON u.id = t.id;
-- t.id la task ID, khong phai assignee_id
-- Ghep nhung cap (user_1, task_1), (user_2, task_2)... - data sai logic
-- Query chay binh thuong, result co ve hop ly, rat kho phat hien

8. Applied — TaskFlow scenarios

-- "Liet ke task + ai assign + project nao"
SELECT
  t.id,
  t.title,
  t.status,
  u.name  AS assignee,
  p.name  AS project
FROM tasks t
INNER JOIN users    u ON t.assignee_id = u.id
INNER JOIN projects p ON t.project_id  = p.id
WHERE t.status IN ('todo', 'doing')
ORDER BY t.due_at NULLS LAST
LIMIT 50;

-- "Comment thread cua task X kem ten author"
SELECT
  c.body,
  c.created_at,
  u.name AS author
FROM comments c
INNER JOIN users u ON c.user_id = u.id
WHERE c.task_id = $1
ORDER BY c.created_at;

Cross-link: M02.7 mini-challenge dashboard có placeholder project_name vì chưa học JOIN. Bây giờ bạn có thể fix — nhưng lưu ý rủi ro: nếu task có project_id IS NULL (task không thuộc project nào), INNER JOIN sẽ loại task đó ra khỏi kết quả. Khi spec yêu cầu giữ cả task không thuộc project, cần LEFT JOIN thay vì INNER JOIN — bài tiếp theo của khoá này.

9. Deep Dive — JOIN fundamentals

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

• Khoá Spring — bài 4.4 Relationships — @OneToMany @ManyToOne @JoinColumn trong JPA dịch sang SQL JOIN thế nào, vấn đề N+1 query.

10. Tóm tắt

• INNER JOIN = set intersection trên join predicate — chỉ giữ row match cả 2 bảng.
• Explicit JOIN ON win implicit WHERE: readability, tránh Cartesian accident, OUTER JOIN bắt buộc explicit, maintenance an toàn hơn.
• Multi-table JOIN: planner reorder dựa cost, convention viết FROM bảng trung tâm rồi JOIN phóng ra.
• Alias bắt buộc cho multi-table — qualify mọi column để tránh ambiguous error.
• 3 dạng Cartesian bug: implicit quên condition, ON 1=1, typo column — loại 3 nguy hiểm nhất vì silent.
• INNER JOIN loại row không match — task có project_id IS NULL sẽ bị loại; dùng LEFT JOIN nếu muốn giữ.
• Forward: Module 5 của khoá này (composite index cho JOIN), Module 7 của khoá này (join algorithm deep dive).

11. Tự kiểm tra

SELECT t.id, t.title, t.status,
       COALESCE(p.name, '(No project)') AS project_name
FROM tasks t
LEFT JOIN projects p ON t.project_id = p.id
WHERE t.assignee_id = $1 ...

Bài tiếp theo: LEFT/RIGHT/FULL OUTER JOIN — NULL trap với WHERE-after-JOIN