Self-join + multi-table JOIN — comment threads, 4 bảng cùng query

TaskFlow vừa nhận yêu cầu mới: màn hình comment thread hiển thị "comment gốc + reply kèm tên tác giả, tiêu đề task, và tên project" — tất cả trong một lần load. Dữ liệu trải rộng trên 4 bảng (comments, users, tasks, projects) và bảng comments cần JOIN với chính nó để lấy thông tin comment cha.

Đây là hai pattern JOIN bạn sẽ gặp trong mọi dự án thực chiến: self-join cho quan hệ parent-child trong cùng bảng, và multi-table JOIN để tổng hợp dữ liệu từ nhiều bảng chỉ trong một query. Bài này cover cả hai, cộng với N+1 anti-pattern — lý do chính xác vì sao ORM lazy load là kẻ thù của performance.

1. Analogy — 2 bộ bài từ cùng một deck

Hình dung bảng users là một bộ bài. Self-join giống như tách bộ bài thành hai cọc từ cùng một deck — cọc A đóng vai "nhân viên", cọc B (lật ngược lại cũng từ deck đó) đóng vai "quản lý". Bạn ghép từng card "nhân viên" với card "quản lý" tương ứng theo manager_id.

Multi-table JOIN là ghép 3–4 bộ bài khác nhau (comments, users, tasks, projects) theo predicate chain: comment phải match user qua user_id, phải match task qua task_id, task phải match project qua project_id.

2. Self-join — bảng JOIN với chính nó

Use case kinh điển: quan hệ parent-child trong cùng một bảng. TaskFlow lưu cấu trúc org chart ngay trong bảng users — mỗi user có thể có một manager_id trỏ về id của user khác trong cùng bảng.

-- Setup: them column manager_id vao users
ALTER TABLE users ADD COLUMN manager_id BIGINT REFERENCES users(id);

-- Self-join: lay user kem ten manager cua ho
SELECT
  u.id         AS user_id,
  u.name       AS user_name,
  m.name       AS manager_name
FROM users u
LEFT JOIN users m ON u.manager_id = m.id;

Hai điểm cốt lõi:

Alias bắt buộc phải khác nhau. u đóng vai "nhân viên", m đóng vai "quản lý" — cả hai đều là bảng users vật lý, nhưng parser xử lý chúng như hai bảng logic độc lập.

LEFT JOIN vì CEO không có manager. User gốc của org chart (hoặc user freelance không ai quản lý) có manager_id = NULL. INNER JOIN sẽ loại những user này khỏi kết quả — sai spec. LEFT JOIN giữ họ lại với manager_name = NULL.

3. Use case self-join thực tế

Self-join áp dụng bất kỳ khi nào một bảng chứa self-reference qua foreign key:

Pattern chung: bất kỳ bảng nào có column FK trỏ về chính primary key của nó đều cần self-join khi bạn muốn lấy thông tin của cả "row hiện tại" lẫn "row cha" trong cùng một query.

4. Demo TaskFlow — comment thread với self-join

Module 4 của khoá này sẽ refactor bảng comments để thêm parent_id BIGINT REFERENCES comments(id) — hỗ trợ reply comment. Query sau lấy danh sách comment kèm thông tin comment cha:

-- Comment thread: comment + author + parent comment + parent author
SELECT
  c.id,
  c.body,
  c.created_at,
  ua.name        AS author,
  cp.body        AS parent_body,
  up.name        AS parent_author
FROM comments c
INNER JOIN users   ua ON c.user_id   = ua.id      -- author cua comment hien tai
LEFT  JOIN comments cp ON c.parent_id = cp.id      -- self-join lay comment cha
LEFT  JOIN users   up ON cp.user_id   = up.id      -- author cua comment cha
WHERE c.task_id = $1
ORDER BY c.created_at;

4 bảng trong query này — nhưng comments xuất hiện 2 lần (c và cp), users xuất hiện 2 lần (ua và up). Mỗi alias phục vụ một vai trò khác nhau: c là comment hiện tại, cp là comment cha, ua là tác giả comment hiện tại, up là tác giả comment cha.

LEFT JOIN cho cả cp và up: comment gốc của thread không có cha (parent_id = NULL) — LEFT JOIN giữ comment gốc với parent_body = NULL, parent_author = NULL. INNER JOIN sẽ loại mọi comment gốc khỏi kết quả.

5. Multi-table JOIN — 4 bảng TaskFlow

Comment feed của project cần dữ liệu từ 4 bảng khác nhau. Một query duy nhất thay vì 4 round-trip đến database:

-- Comment feed cua project X: comment + ten author + title task + project name
SELECT
  c.id,
  c.body,
  c.created_at,
  u.name  AS author,
  t.title AS task_title,
  p.name  AS project_name
FROM comments c
INNER JOIN users    u ON c.user_id      = u.id
INNER JOIN tasks    t ON c.task_id      = t.id
INNER JOIN projects p ON t.project_id   = p.id
WHERE p.id = $1
ORDER BY c.created_at DESC
LIMIT 50;

Convention viết từ entity trung tâm (comments) ra ngoài theo dependency chain: comments → users, comments → tasks → projects. Thứ tự text trong SQL không ảnh hưởng kết quả — planner reorder dựa cost — nhưng viết theo chiều dependency giúp người đọc hiểu ngay "bảng nào kết nối với bảng nào qua FK nào".

6. N+1 anti-pattern — vì sao multi-table JOIN win ORM lazy load

Đây là bug performance phổ biến nhất khi dùng ORM (JPA/Hibernate, Prisma, ActiveRecord, Sequelize). Tên gọi "N+1" xuất phát từ công thức số lần query: 1 query lấy danh sách + N query lấy detail cho từng phần tử.

// ORM lazy load - SAI (N+1 pattern)
const comments = await commentRepo.findByTaskId(taskId); // 1 query: lay 50 comment

for (const c of comments) {
  const author  = await userRepo.findById(c.userId);           // 50 query
  const task    = await taskRepo.findById(c.taskId);           // 50 query
  const project = await projectRepo.findById(task.projectId);  // 50 query
}
// Tong: 1 + 50 + 50 + 50 = 151 query cho 50 comment

151 query thay vì 1 query — mỗi query là một round-trip network đến PostgreSQL. Với 50 comment, latency tích lũy theo tuyến tính. Với 500 comment, thành 1501 query.

// Fix: 1 query SQL JOIN tra ve du lieu day du
const rows = await db.query(`
  SELECT c.id, c.body, c.created_at,
         u.name  AS author_name,
         t.title AS task_title,
         p.name  AS project_name
  FROM comments c
  JOIN users    u ON c.user_id    = u.id
  JOIN tasks    t ON c.task_id    = t.id
  JOIN projects p ON t.project_id = p.id
  WHERE c.task_id = $1
`, [taskId]);
// 1 query, 50 row, 0 N+1

ORM có cách fix mà không cần viết raw SQL:

• Spring Data JPA: @Query("SELECT c FROM Comment c JOIN FETCH c.user JOIN FETCH c.task t JOIN FETCH t.project WHERE c.task.id = :taskId")
• Prisma: { include: { user: true, task: { include: { project: true } } } }
• ActiveRecord: Comment.includes(:user, task: :project).where(task_id: taskId)

Cross-link: Module 4 của khoá Spring tại khoá Spring trên OLHub đi sâu vào N+1 với Spring Data JPA — cùng anti-pattern, cùng fix với JOIN FETCH và @EntityGraph.

7. Pitfall — alias trùng và quên qualify

Ba lỗi self-join và multi-table JOIN hay gặp nhất:

-- BUG 1: column ambiguous khi khong qualify
SELECT id, name
FROM users u
JOIN tasks t ON u.id = t.assignee_id;
-- ERROR: column "id" is ambiguous (u.id hay t.id?)

-- FIX: qualify tat ca column
SELECT u.id, u.name
FROM users u
JOIN tasks t ON u.id = t.assignee_id;

-- BUG 2: alias trung trong self-join
SELECT a.name, a.name
FROM users a
JOIN users a ON a.manager_id = a.id;
-- ERROR: table name "a" specified more than once

-- FIX: alias khac nhau cho moi role
SELECT u.name, m.name
FROM users u
JOIN users m ON u.manager_id = m.id;

-- BUG 3: quen ON clause cho bang thu 4
SELECT c.body, u.name, t.title, p.name
FROM comments c
JOIN users u    ON c.user_id = u.id
JOIN tasks t    ON c.task_id = t.id
JOIN projects p;  -- BUG: thieu ON clause
-- ERROR: syntax error, hoac Cartesian product voi projects

8. JOIN order convention

Trong đa số trường hợp, planner PostgreSQL tự reorder JOIN theo cost model — thứ tự bạn viết trong SQL text không ảnh hưởng performance. Convention viết readable:

-- Readable: comments la entity trung tam, JOIN phong ra
SELECT c.body, u.name, t.title, p.name
FROM comments c
JOIN tasks    t ON c.task_id     = t.id
JOIN projects p ON t.project_id  = p.id
JOIN users    u ON c.user_id     = u.id;

-- Less readable: bat dau tu projects (entity ngoai cung)
SELECT c.body, u.name, t.title, p.name
FROM projects p
JOIN tasks    t ON t.project_id  = p.id
JOIN comments c ON c.task_id     = t.id
JOIN users    u ON c.user_id     = u.id;

Hai tình huống thứ tự text có ảnh hưởng thực sự:

• Khi số bảng vượt join_collapse_limit (mặc định 8) — planner giữ thứ tự bạn viết. Lúc đó convention "bảng nhỏ nhất làm outer loop" có tác dụng.
• Khi bạn set SET join_collapse_limit = 1 để force planner tôn trọng thứ tự text (tuning nâng cao — Module 7 của khoá này).

9. Deep Dive — Multi-table JOIN

10. Tóm tắt

• Self-join = bảng JOIN với chính nó qua hai alias khác nhau. Alias bắt buộc phải độc nhất — parser xử lý mỗi alias như một bảng logic riêng biệt.
• Use case self-join: bất kỳ quan hệ parent-child trong cùng bảng — org chart, comment thread, subtask, category tree.
• Multi-table 4+ bảng: mỗi lần bảng xuất hiện trong query, alias bắt buộc — qualify mọi column để tránh ambiguous error.
• N+1 anti-pattern: ORM lazy load 1 danh sách + N query detail = 1 + N*M round-trip. Fix bằng JOIN trong một query, hoặc ORM JOIN FETCH / eager include.
• JOIN order text không matter (planner reorder), nhưng convention readable: FROM entity trung tâm, JOIN theo dependency chain ra ngoài.
• Forward: Module 4 của khoá này (comments refactor thêm parent_id self-FK), Module 7 của khoá này (join algorithms — Nested Loop, Hash, Merge), Module 8 của khoá này (recursive CTE cho deep tree), Module 4 khoá Spring (JOIN FETCH và N+1 fix với Hibernate).

11. Tự kiểm tra

Bài tiếp theo: GROUP BY + HAVING — SQL logical processing order