1.1 Mục tiêu — JOIN là vũ khí thứ ba

Bạn đã học hai trục lớn: filter (tuần 1–2) và aggregate (tuần 3). Tuần này thêm trục thứ ba — JOIN: ghép dữ liệu từ nhiều bảng theo cầu nối khóa.

Sau tuần 4 bạn cần làm được

• Phân biệt rõ INNER, LEFT, RIGHT, FULL OUTER, CROSS qua hành vi cụ thể, không chỉ định nghĩa.
• Giải thích vì sao đặt predicate trong WHERE phá vỡ semantics của OUTER JOIN.
• Viết self-join cho bài toán "so sánh dòng với dòng cùng bảng" (employee/manager, consecutive rows).
• Implement bài "không có dữ liệu" (anti-join) bằng ba cách: NOT EXISTS, LEFT JOIN ... IS NULL, NOT IN, và biết cái nào an toàn nhất.
• Nhận ra "Cartesian explosion" và sửa được trong 30 giây.

1.2 Mental model — JOIN là Cartesian + Filter

Mọi dạng JOIN đều có thể nghĩ qua hai bước:

1. Cartesian product — ghép mọi cặp (dòng A, dòng B).
2. Filter — giữ lại các cặp thỏa predicate (thường là A.key = B.key).
3. (Với OUTER JOIN) Bổ sung dòng "không match" với NULL.

Đây là cách bạn nên tư duy khi gỡ bug. Engine không thực sự làm cross product (quá đắt) — nó dùng hash join hoặc merge join — nhưng kết quả tương đương.

2.1 INNER JOIN — chỉ giữ cặp match

-- Lấy đơn hàng kèm tên khách
SELECT o.order_id, o.total, c.name
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id;

Đơn không có khách hàng tương ứng (FK rỗng) bị loại. Khách không có đơn cũng bị loại.

2.2 ON vs USING vs NATURAL JOIN

-- ON: predicate tự do, rõ ràng nhất, dùng phổ biến
SELECT o.order_id, c.name
FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

-- USING: khi tên cột giống hệt, gộp thành 1 cột trong kết quả
SELECT order_id, name
FROM orders JOIN customers USING (customer_id);
-- (customer_id chỉ xuất hiện 1 lần, không phải c.customer_id và o.customer_id)

-- NATURAL JOIN: tự động JOIN trên mọi cột cùng tên — TRÁNH DÙNG
SELECT * FROM orders NATURAL JOIN customers;

2.3 JOIN nhiều bảng (chain JOIN)

Một query có thể JOIN nhiều bảng liên tiếp. Engine xử lý trái-sang-phải theo mặc định.

-- Lấy chi tiết: ai đặt cái gì, bao nhiêu tiền
SELECT
    c.name        AS customer,
    o.order_date,
    p.name        AS product,
    oi.quantity,
    oi.unit_price
FROM customers c
JOIN orders      o  ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products    p  ON p.product_id  = oi.product_id
WHERE c.country = 'Vietnam'
ORDER BY c.name, o.order_date;

2.4 INNER JOIN và NULL trong khóa

Predicate A.k = B.k là phép so sánh thông thường — và bạn đã biết: NULL = NULL trả UNKNOWN, không phải TRUE.

-- Đơn guest sẽ bị bỏ qua
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id;

3.1 LEFT JOIN — giữ tất cả dòng từ bảng trái

SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS n_orders   -- không phải COUNT(*)!
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

3.2 RIGHT JOIN và FULL OUTER JOIN

RIGHT JOIN — đối xứng của LEFT

A RIGHT JOIN B = B LEFT JOIN A. Hiếm khi dùng trong thực tế — quy ước team thường viết LEFT JOIN và đảo thứ tự bảng để đọc tự nhiên.

FULL OUTER JOIN — giữ cả hai bên

A FULL OUTER JOIN B: dòng A không match → B NULL; dòng B không match → A NULL; dòng match → bình thường.

3.3 WHERE vs ON trong OUTER JOIN — bug đẳng cấp

Đây là bug subtle nhất trong cả tuần. Đặt predicate trên cột bảng phải sai chỗ sẽ biến LEFT JOIN thành INNER JOIN.

SELECT c.name, COUNT(o.order_id) AS n_paid
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.name;

SELECT c.name, COUNT(o.order_id) AS n_paid
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'paid'
GROUP BY c.name;

4.1 Self-join — bảng tham chiếu chính nó

Self-join là kỹ thuật join một bảng với chính nó dưới hai alias khác nhau. Dùng khi:

• Bảng có FK trỏ về PK của chính nó (hierarchy).
• Cần so sánh dòng với dòng khác cùng bảng (consecutive, pair).

-- Liệt kê (nhân viên, quản lý) cho mọi nhân viên có quản lý
SELECT e.name AS employee, m.name AS manager
FROM Employee e
JOIN Employee m ON m.id = e.manager_id;

4.2 So sánh dòng với dòng — pair queries

Self-join với predicate khác equality trên khóa sẽ tạo các cặp dòng cần so sánh.

SELECT e.name AS Employee
FROM Employee e
JOIN Employee m
  ON m.id = e.manager_id
 AND e.salary > m.salary;
-- Hoặc tách điều kiện:
-- ON m.id = e.manager_id WHERE e.salary > m.salary;

SELECT today.id
FROM Weather today
JOIN Weather yesterday
  ON yesterday.recordDate = today.recordDate - INTERVAL 1 DAY  -- MySQL
 AND today.temperature > yesterday.temperature;

4.3 Pair generation và bẫy duplicate

Khi tạo cặp (a, b) từ cùng bảng, cần cẩn thận về thứ tự và trùng lặp.

-- Naive
SELECT f1.user_a, f2.user_b
FROM friends f1
JOIN friends f2 ON f1.user_b = f2.user_a;

-- Sạch hơn
SELECT DISTINCT
    LEAST(f1.user_a, f2.user_b)    AS u1,
    GREATEST(f1.user_a, f2.user_b) AS u2
FROM friends f1
JOIN friends f2 ON f1.user_b = f2.user_a
WHERE f1.user_a <> f2.user_b
  AND NOT EXISTS (
      SELECT 1 FROM friends ff
      WHERE (ff.user_a = f1.user_a AND ff.user_b = f2.user_b)
         OR (ff.user_a = f2.user_b AND ff.user_b = f1.user_a)
  );

Pattern: dùng LEAST/GREATEST để chuẩn hóa thứ tự cặp, NOT EXISTS để loại friend trực tiếp.

5.1 CROSS JOIN — Cartesian có chủ đích

Ngược lại với "Cartesian explosion" tai nạn, CROSS JOIN cố ý dùng cho các pattern hữu ích.

-- Tạo 4 region × 12 tháng = 48 dòng baseline, sau đó LEFT JOIN sales
SELECT r.region, m.month, COALESCE(SUM(s.amount), 0) AS revenue
FROM regions r
CROSS JOIN months m
LEFT JOIN sales s
       ON s.region = r.region
      AND s.month  = m.month
      AND s.year   = 2024
GROUP BY r.region, m.month
ORDER BY r.region, m.month;

5.2 Generate sequences với CROSS JOIN

Khi không có bảng calendar/numbers sẵn, có thể tạo ad-hoc bằng CROSS JOIN.

-- PostgreSQL có generate_series, đơn giản nhất:
SELECT day::date FROM generate_series('2024-01-01'::date, '2024-12-31', '1 day') day;

-- MySQL phải tự sinh (ad-hoc, dùng để demo):
WITH RECURSIVE days AS (
    SELECT DATE '2024-01-01' AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM days WHERE day < '2024-12-31'
)
SELECT * FROM days;
-- Recursive CTE chính thức học tuần 7

SELECT a.n + b.n*10 AS num
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
      UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN
     (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
      UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
ORDER BY num;

Generation bằng recursive CTE sẽ học chính thức tuần 7. Tạm biết CROSS JOIN có thể dùng cho mục đích này.

6.1 Ba cách viết anti-join

Cách 1 — NOT EXISTS (khuyên dùng)

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Cách 2 — LEFT JOIN ... IS NULL

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

Cách 3 — NOT IN (CẨN THẬN)

SELECT c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
    -- IS NOT NULL bắt buộc — nếu không sẽ trả tập rỗng (bug tuần 2)
);

Ba query trên cùng kết quả (giả sử dữ liệu sạch). Khác biệt nằm ở an toàn NULL và performance — slide tiếp theo.

6.2 So sánh: an toàn NULL và performance

Trong mock interview, viết NOT EXISTS và giải thích "tôi chọn vì an toàn NULL" — interviewer sẽ thấy bạn hiểu sâu.

7.1 LeetCode 175 — Combine Two Tables

Schema

Yêu cầu

Trả về (firstName, lastName, city, state) cho mọi Person — kể cả Person không có Address.

Phân rã

"Mọi Person" ⇒ Person là bảng anchor ⇒ LEFT JOIN Address.

Lời giải

SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p
LEFT JOIN Address a ON a.personId = p.personId;

7.2 LeetCode 181 — Employees Earning More Than Their Managers

Schema

Yêu cầu

Trả về tên các nhân viên có salary > salary của manager họ.

Phân rã

1. Mỗi nhân viên (e) có một manager (m) qua e.managerId = m.id → self-join.
2. Filter e.salary > m.salary.

Lời giải

SELECT e.name AS Employee
FROM Employee e
JOIN Employee m
  ON m.id = e.managerId
WHERE e.salary > m.salary;

7.3 LeetCode 183 — Customers Who Never Order

Schema

Ba cách giải — tất cả đều pass

-- Cách 1: NOT EXISTS (khuyên)
SELECT name AS Customers
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customerId = c.id);

-- Cách 2: LEFT JOIN ... IS NULL
SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON o.customerId = c.id
WHERE o.id IS NULL;

-- Cách 3: NOT IN (chỉ an toàn nếu Orders.customerId NOT NULL)
SELECT name AS Customers
FROM Customers
WHERE id NOT IN (SELECT customerId FROM Orders);

7.4 LeetCode 1280 — Students and Examinations (Medium)

Schema

Yêu cầu

Với mỗi (student, subject) — kể cả khi sinh viên chưa thi môn đó — trả về số lần thi.

Phân rã — kết hợp 3 kỹ thuật tuần này

1. "Mỗi (student, subject)" ⇒ CROSS JOIN Students với Subjects để tạo grid đầy đủ.
2. Đếm số lần thi ⇒ LEFT JOIN Examinations (kể cả 0 lần).
3. GROUP BY và COUNT đúng cột (không phải COUNT(*)!).

Lời giải

SELECT
    s.student_id,
    s.student_name,
    sub.subject_name,
    COUNT(e.subject_name) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN Examinations e
       ON e.student_id   = s.student_id
      AND e.subject_name = sub.subject_name
GROUP BY s.student_id, s.student_name, sub.subject_name
ORDER BY s.student_id, sub.subject_name;

Đây là bài Medium chỉ với kỹ thuật tuần 4. Cảm giác "all click together" này là dấu hiệu bạn đã thấm.

Knowledge Check — Tuần 4

9.1 Bài tập LeetCode (5 problem)

1. 175. Combine Two Tables (Easy) — đã giải tại lớp.
2. 181. Employees Earning More Than Their Managers (Easy) — đã giải tại lớp.
3. 183. Customers Who Never Order (Easy) — viết bằng cả 3 cách, đo thời gian execution trên LeetCode.
4. 1280. Students and Examinations (Easy/Medium) — đã giải tại lớp.
5. 1731. The Number of Employees Which Report to Each Employee (Easy) — self-join + GROUP BY + AVG. Bẫy: chỉ tính manager có ≥ 1 báo cáo trực tiếp.

Bonus — viết 3 cách cho bài 183 và so sánh EXPLAIN

Trên LeetCode, sau khi submit, click "Compile/Run" để xem thời gian. Trên dialect bạn dùng (MySQL/PostgreSQL local), chạy EXPLAIN trước query và so sánh execution plan của ba cách. Note lại: cách nào dùng "anti-join" hash, cách nào hiện "Nested Loop".

9.2 Đọc thêm và tự kiểm tra

Đọc

• SQL Cookbook (Anthony Molinaro) — Chương 4 (Inserting, Updating, Deleting) §4.7–4.9 và Chương 6 nâng cao về JOIN.
• SQL Antipatterns (Bill Karwin) — Chương "Implicit Columns" và "Diplomatic Immunity" về bẫy NATURAL JOIN.
• Markus Winand — use-the-index-luke.com phần "Joins" về index strategy cho join.

Tự kiểm tra (không nhìn slide)

• Bạn vẽ được Venn diagram cho INNER, LEFT, RIGHT, FULL OUTER không cần nhìn?
• Bạn giải thích được vì sao đặt o.status = 'paid' trong WHERE phá LEFT JOIN?
• Bạn viết được self-join cho "tìm cặp employee cùng phòng có salary chênh lệch > 1000" trong dưới 2 phút?
• Bạn nhớ ba cách viết anti-join và lý do chọn NOT EXISTS làm default?