SQL từ cơ bản đến nâng cao
Trang chủ / Module 5
🔗 Module 5 / 16 · 6-7 giờ

JOIN: nối bảng đúng cách và tránh nhân đôi dữ liệu

Thành thạo mọi loại JOIN, anti-join, self-join, LATERAL và — quan trọng nhất — nhận biết 'grain' để tránh lỗi nhân đôi số liệu sau khi nối.

INNER/LEFT/FULLAnti-joinLATERALFan-out
📌 Vì sao module này quan trọng

JOIN là kỹ năng phân biệt người biết SQL với người dùng SQL như bảng tính. Dữ liệu quan hệ được thiết kế tách rời (chuẩn hoá) — muốn trả lời câu hỏi nghiệp vụ gần như luôn phải nối lại. Nhưng JOIN cũng là nơi sinh ra loại lỗi tai hại nhất: kết quả trông hợp lý nhưng doanh thu bị nhân đôi, khách bị đếm trùng hoặc biến mất. Module này dạy cách suy nghĩ để JOIN luôn đúng.

🎯 Mục tiêu học tập

  • Chọn đúng INNER/LEFT/RIGHT/FULL/CROSS JOIN cho từng bài toán và giải thích kết quả bằng mô hình 'tích rồi lọc'
  • Viết anti-join (tìm bản ghi KHÔNG có quan hệ) và hiểu bẫy ON vs WHERE trong LEFT JOIN
  • Nhận diện fan-out, kiểm soát grain và áp dụng 'tổng hợp trước, nối sau'
  • Dùng self-join, non-equi join, LATERAL cho các bài toán thứ bậc, khoảng giá trị và top-N mỗi nhóm

Mô hình tư duy: tích Descartes rồi lọc

Về mặt logic, A JOIN B ON điều_kiện = lấy mọi cặp (dòng A, dòng B) rồi giữ những cặp thoả điều kiện. (Database không thực sự sinh tích Descartes — nó dùng hash join, merge join, nested loop kèm index — nhưng kết quả luôn phải giống mô hình này.) Từ đó suy ra ngay: nếu một dòng A khớp 3 dòng B thì dòng A xuất hiện 3 lần trong kết quả.

JOINGiữ lạiDùng khi
INNER JOINChỉ cặp khớpCần dữ liệu cả hai phía (đơn hàng + khách của nó)
LEFT JOINMọi dòng trái; phải NULL nếu không khớpGiữ "bên chính" dù không có dữ liệu liên quan; tìm bản ghi mồ côi
RIGHT JOINNgược LEFTHầu như không cần — đảo vế và dùng LEFT cho dễ đọc
FULL JOINMọi dòng cả hai bênĐối soát hai nguồn dữ liệu
CROSS JOINTất cả tổ hợp (m × n)Sinh lưới tổ hợp (ngày × sản phẩm)
SELECT o.id AS order_id, o.status, c.full_name, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id      -- JOIN trần = INNER JOIN
ORDER BY o.id
LIMIT 5;
Kết quả
 order_id |  status   |    full_name     |   city
----------+-----------+------------------+-----------
        1 | delivered | Nguyễn Thu Hà    | Cần Thơ
        2 | delivered | Bùi Gia Huy      | Hà Nội
        3 | delivered | Đặng Ngọc Lan    | Đà Nẵng
        4 | delivered | Vũ Hải Nam       | Hải Phòng
        5 | delivered | Hoàng Khánh Linh | TP.HCM
(5 rows)
-- Nối 3 bảng: chi tiết đơn #2 kèm khách và tên sản phẩm
SELECT o.id AS order_id, c.full_name, p.name, oi.qty
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.id = 2;
Kết quả
 order_id |  full_name  |       name        | qty
----------+-------------+-------------------+-----
        2 | Bùi Gia Huy | Chuột không dây   |   1
        2 | Bùi Gia Huy | Máy xay sinh tố   |   2
        2 | Bùi Gia Huy | Laptop Zenbook 14 |   3
(3 rows)
❌ Lỗi thường gặp — Quên điều kiện JOIN → tích Descartes

FROM customers, products (cú pháp cũ dùng dấu phẩy) hoặc JOIN ... ON thiếu/sai điều kiện sinh ra m × n dòng. Với 1 triệu × 1 triệu bạn vừa tự DDoS database. Dấu hiệu: số dòng kết quả lớn bất thường. Dùng cú pháp JOIN ... ON tường minh, không viết join bằng dấu phẩy + WHERE.

SELECT (SELECT count(*) FROM customers) * (SELECT count(*) FROM products) AS ky_vong,
       count(*) AS thuc_te
FROM customers, products;
Kết quả
 ky_vong | thuc_te
---------+---------
    1200 |    1200
(1 row)
🧠 Cách chuyên gia — Luôn kiểm tra số dòng trước và sau JOIN

Thói quen số một của người làm dữ liệu chuyên nghiệp: sau khi thêm mỗi JOIN, chạy count(*) và so với kỳ vọng. Nối n-1 (đơn → khách) thì số dòng không đổi; nối 1-n (đơn → dòng hàng) thì số dòng tăng đúng bằng số dòng con. Nếu số dòng tăng bất ngờ, bạn đã nối sai khoá hoặc dữ liệu có trùng.

LEFT JOIN và anti-join: tìm cái 'không có'

Câu hỏi "khách nào chưa mua gì?" không thể trả lời bằng INNER JOIN (nó chỉ cho thấy cái có). Mẫu chuẩn: LEFT JOIN ... WHERE bảng_phải.khoá IS NULL.

SELECT c.id, c.full_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;
Kết quả
 id |     full_name
----+-------------------
 55 | Bùi Gia Huy
 56 | Nguyễn Khánh Linh
 57 | Trần Thị Bình
 58 | Lê Thu Hà
 59 | Phạm Hải Nam
 60 | Hoàng Văn An
(6 rows)
💡 Ba cách viết anti-join

(1) LEFT JOIN ... IS NULL, (2) NOT EXISTS (subquery), (3) NOT IN (subquery). Chuyên gia ưu tiên (2) NOT EXISTS hoặc (1); (3) nguy hiểm khi subquery có NULL (module 6). PostgreSQL biên dịch (1) và (2) thành cùng một kế hoạch "Anti Join" hiệu quả.

Bẫy kinh điển: điều kiện lọc bảng phải đặt ở ON hay WHERE?

Với LEFT JOIN, điều kiện trong ON quyết định dòng nào của bảng phải được ghép; điều kiện trong WHERE chạy sau JOIN và loại cả những dòng NULL — biến LEFT JOIN thành INNER JOIN trá hình.

-- Mục tiêu: mọi khách + số đơn ĐÃ GIAO của họ (khách chưa giao đơn nào hiện 0)
-- SAI: lọc trong WHERE làm rơi các khách không có đơn delivered
SELECT count(DISTINCT c.id) AS so_khach_sai
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered';
Kết quả
 so_khach_sai
--------------
           54
(1 row)
-- ĐÚNG: điều kiện của bảng phải nằm trong ON
SELECT count(DISTINCT c.id) AS so_khach_dung
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered';
Kết quả
 so_khach_dung
---------------
            60
(1 row)
SELECT c.id, c.full_name, count(o.id) AS don_da_giao
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered'
GROUP BY c.id
ORDER BY don_da_giao, c.id
LIMIT 4;
Kết quả
 id |     full_name     | don_da_giao
----+-------------------+-------------
 55 | Bùi Gia Huy       |           0
 56 | Nguyễn Khánh Linh |           0
 57 | Trần Thị Bình     |           0
 58 | Lê Thu Hà         |           0
(4 rows)
❌ Lỗi thường gặp — COUNT(*) với LEFT JOIN đếm cả dòng 'không có gì'

Khách chưa có đơn vẫn tạo 1 dòng (với phần phải là NULL), nên count(*) cho 1, không phải 0. Luôn đếm cột của bảng phải: count(o.id) bỏ qua NULL nên cho 0 đúng.

Fan-out: kẻ giết số liệu thầm lặng

Mỗi đơn có 1–4 dòng hàng, mỗi đơn có 0–1 payment. Nếu bạn nối cả hai vào đơn rồi tổng hợp, mỗi bảng nhân dòng của bảng kia. Đây là fan-out. Quy tắc sống còn: trước khi nối, hãy tự hỏi mỗi bảng có grain gì (1 dòng = cái gì) và quan hệ là 1-1, n-1 hay 1-n.

-- Một đơn: bao nhiêu dòng hàng, bao nhiêu payment?
SELECT o.id,
       (SELECT count(*) FROM order_items WHERE order_id = o.id) AS so_dong_hang,
       (SELECT count(*) FROM payments    WHERE order_id = o.id) AS so_payment
FROM orders o WHERE o.id IN (1, 2, 3, 4);
Kết quả
 id | so_dong_hang | so_payment
----+--------------+------------
  1 |            2 |          1
  2 |            3 |          1
  3 |            4 |          1
  4 |            1 |          1
(4 rows)
-- SAI: số tiền thanh toán bị nhân với số dòng hàng
SELECT sum(p.amount) AS tien_thu_sai, sum(DISTINCT p.amount) AS cach_chua_sai_van_sai
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments p     ON p.order_id = o.id;
Kết quả
  tien_thu_sai  | cach_chua_sai_van_sai
----------------+-----------------------
 32187028400.00 |         3040566000.00
(1 row)
-- ĐÚNG (mẫu chuẩn): đưa mỗi nhánh về grain 'đơn' rồi mới nối
WITH hang AS (
  SELECT order_id, sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS tien_hang, count(*) AS so_dong
  FROM order_items oi GROUP BY order_id
), tra AS (
  SELECT order_id, sum(amount) AS da_tra FROM payments GROUP BY order_id
)
SELECT count(*)                      AS so_don,
       round(sum(h.tien_hang))       AS tong_tien_hang,
       sum(COALESCE(t.da_tra, 0))    AS tong_da_tra
FROM orders o
JOIN hang h       ON h.order_id = o.id
LEFT JOIN tra t   ON t.order_id = o.id;
Kết quả
 so_don | tong_tien_hang |  tong_da_tra
--------+----------------+----------------
    400 |    12817441000 | 10635679000.00
(1 row)
❌ Lỗi thường gặp — Chữa nhân đôi bằng DISTINCT

Ví dụ thứ hai ở trên: sum(DISTINCT p.amount) bỏ các số tiền bằng nhau (hai đơn khác nhau cùng 1.590.000 bị gộp thành một) → vẫn sai và còn khó phát hiện hơn. Không bao giờ dùng DISTINCT để vá fan-out; hãy sửa grain.

🧠 Cách chuyên gia — Quy trình 4 bước cho truy vấn nhiều bảng
  1. Chọn bảng gốc theo grain của kết quả mong muốn (một dòng = một đơn? một khách? một ngày?).
  2. Với mỗi bảng 1-n muốn lấy số liệu, tổng hợp về grain của bảng gốc trong CTE/subquery.
  3. Nối các nhánh đã cùng grain bằng LEFT JOIN (không mất dòng gốc), COALESCE cho số 0.
  4. Đối soát: tổng sau cùng phải bằng tổng của bảng nguồn.

Self-join, FULL JOIN, CROSS JOIN, non-equi join

Self-join — nối bảng với chính nó để biểu diễn quan hệ cha-con (nhân viên—quản lý, khách—người giới thiệu). Bắt buộc dùng alias khác nhau.

SELECT e.full_name AS nhan_vien, m.full_name AS quan_ly, e.dept
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id
LIMIT 6;
Kết quả
   nhan_vien    |    quan_ly     |     dept
----------------+----------------+--------------
 Trần Quốc Bảo  | NULL           | Ban giám đốc
 Lê Thu Hương   | Trần Quốc Bảo  | Kỹ thuật
 Phạm Minh Đức  | Trần Quốc Bảo  | Kinh doanh
 Nguyễn Hải Yến | Lê Thu Hương   | Kỹ thuật
 Vũ Gia Khang   | Lê Thu Hương   | Kỹ thuật
 Đặng Ngọc Mai  | Nguyễn Hải Yến | Kỹ thuật
(6 rows)
SELECT c.id, c.full_name AS khach, r.full_name AS nguoi_gioi_thieu
FROM customers c
JOIN customers r ON r.id = c.referred_by
ORDER BY c.id LIMIT 4;
Kết quả
 id |       khach       | nguoi_gioi_thieu
----+-------------------+------------------
 12 | Hoàng Ngọc Lan    | Phạm Đức Phúc
 16 | Nguyễn Khánh Linh | Bùi Thị Bình
 20 | Hoàng Văn An      | Trần Quốc Dũng
 24 | Nguyễn Minh Châu  | Vũ Gia Huy
(4 rows)

FULL JOIN thích hợp để đối soát hai nguồn: dòng chỉ có ở một phía lộ ra ngay.

-- Đối soát: mọi đơn paid/shipped/delivered phải có payment và ngược lại
SELECT count(*) FILTER (WHERE p.id IS NULL) AS don_thieu_payment,
       count(*) FILTER (WHERE o.id IS NULL) AS payment_mo_coi,
       count(*) AS tong_dong_doi_soat
FROM (SELECT id FROM orders WHERE status IN ('paid','shipped','delivered')) o
FULL JOIN payments p ON p.order_id = o.id;
Kết quả
 don_thieu_payment | payment_mo_coi | tong_dong_doi_soat
-------------------+----------------+--------------------
                 0 |              0 |                320
(1 row)

CROSS JOIN sinh lưới tổ hợp để báo cáo không bị "lỗ hổng": mọi phương thức × mọi tháng, kể cả khi không có giao dịch.

SELECT m.method, mth::date AS thang, COALESCE(sum(p.amount), 0) AS doanh_thu
FROM (VALUES ('card'), ('momo'), ('cod')) AS m(method)
CROSS JOIN generate_series(date '2024-01-01', date '2024-03-01', interval '1 month') AS mth
LEFT JOIN payments p
       ON p.method = m.method
      AND date_trunc('month', p.paid_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date = mth::date
GROUP BY m.method, mth
ORDER BY m.method, mth;
Kết quả
 method |   thang    |  doanh_thu
--------+------------+--------------
 card   | 2024-01-01 |            0
 card   | 2024-02-01 | 268768500.00
 card   | 2024-03-01 | 181335000.00
 cod    | 2024-01-01 |  30429000.00
 cod    | 2024-02-01 | 298230100.00
 cod    | 2024-03-01 | 268347000.00
 momo   | 2024-01-01 |            0
 momo   | 2024-02-01 | 371760000.00
 momo   | 2024-03-01 |  68013000.00
(9 rows)

Non-equi join — điều kiện nối không phải dấu bằng, ví dụ nối giá trị vào khoảng (bảng bậc thang giá, bậc thuế, khung thời gian):

SELECT b.band, count(*) AS so_sp, string_agg(p.sku, ', ' ORDER BY p.sku) AS cac_sku
FROM products p
JOIN (VALUES ('1. Dưới 500k', 0, 500000), ('2. 500k-5tr', 500000, 5000000), ('3. Trên 5tr', 5000000, 1000000000))
     AS b(band, lo, hi)
  ON p.price >= b.lo AND p.price < b.hi
GROUP BY b.band ORDER BY b.band;
Kết quả
     band     | so_sp |                        cac_sku
--------------+-------+--------------------------------------------------------
 1. Dưới 500k |     6 | AC-002, AC-003, AC-004, FA-001, FA-002, HM-005
 2. 500k-5tr  |     7 | AC-001, FA-003, FA-004, FA-005, HM-001, HM-002, HM-003
 3. Trên 5tr  |     7 | HM-004, LT-001, LT-002, LT-003, PH-001, PH-002, PH-003
(3 rows)

LATERAL: 'vòng lặp' có kiểm soát — top-N mỗi nhóm

Subquery thường không tham chiếu được bảng bên trái trong FROM; LATERAL cho phép điều đó, giống "với mỗi dòng bên trái, chạy subquery này". Cực hợp với bài toán "N bản ghi mới nhất của mỗi X" và hàm trả về nhiều dòng.

SELECT c.id AS customer_id, o.id AS order_id, o.ordered_at::date AS ngay, o.status
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, ordered_at, status
  FROM orders
  WHERE customer_id = c.id
  ORDER BY ordered_at DESC
  LIMIT 2
) o
WHERE c.id <= 3
ORDER BY c.id, o.ordered_at DESC;
Kết quả
 customer_id | order_id |    ngay    |  status
-------------+----------+------------+-----------
           1 |      108 | 2024-06-11 | cancelled
           1 |       54 | 2024-05-29 | delivered
           2 |      247 | 2024-05-29 | paid
           2 |       31 | 2024-05-03 | delivered
           3 |      332 | 2024-05-17 | delivered
           3 |       62 | 2024-05-16 | delivered
(6 rows)
🧠 Cách chuyên gia — LATERAL + index = top-N cực nhanh

Với index (customer_id, ordered_at DESC), mỗi lần lặp chỉ đọc đúng 2 mục index. Với bảng 100 triệu đơn và 1 triệu khách, cách này nhanh hơn hẳn việc đánh số dòng toàn bảng bằng window function. Dùng LEFT JOIN LATERAL ... ON true nếu muốn giữ cả khách không có đơn.

🏋️ Bài tập thực hành

Sản phẩm không bán được trong tháng 1/2024Dễ

Liệt kê sku, name của sản phẩm không có dòng bán nào trong các đơn đặt từ 1/1 đến hết 31/1/2024 (dùng LEFT JOIN anti-join; điều kiện thời gian đặt đúng chỗ).

Xem lời giải & giải thích
SELECT p.sku, p.name
FROM products p
LEFT JOIN (
  SELECT oi.product_id
  FROM order_items oi JOIN orders o ON o.id = oi.order_id
  WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2024-02-01'
) s ON s.product_id = p.id
WHERE s.product_id IS NULL
ORDER BY p.sku;
Kết quả
  sku   |          name
--------+-------------------------
 AC-001 | Tai nghe không dây Echo
 AC-002 | Sạc nhanh 65W
 AC-003 | Cáp USB-C 1m
 AC-004 | Chuột không dây
 FA-001 | Áo thun basic
 FA-002 | Áo sơ mi công sở
 FA-004 | Giày chạy bộ Sprint
 FA-005 | Giày da nam
 HM-001 | Nồi chiên không dầu 5L
 HM-002 | Máy xay sinh tố
... (cắt bớt, chạy trên máy bạn để xem đủ)

Điều kiện thời gian nằm trong subquery (hoặc trong ON nếu nối thẳng) chứ không đặt ở WHERE bên ngoài — nếu đặt ở ngoài, các sản phẩm không bán sẽ bị loại cùng NULL. Đây chính là bẫy ON vs WHERE.

Doanh thu theo danh mục chaTrung bình

Tính doanh thu (đã trừ giảm giá, bỏ đơn huỷ) theo danh mục gốc (Điện tử, Thời trang, Gia dụng). Gợi ý: nối categories với chính nó qua parent_id, dùng COALESCE(parent.name, cat.name) vì sản phẩm có thể gắn thẳng danh mục gốc.

Xem lời giải & giải thích
SELECT COALESCE(par.name, cat.name) AS danh_muc_goc,
       round(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100)) AS doanh_thu
FROM order_items oi
JOIN orders o        ON o.id = oi.order_id AND o.status <> 'cancelled'
JOIN products p      ON p.id = oi.product_id
JOIN categories cat  ON cat.id = p.category_id
LEFT JOIN categories par ON par.id = cat.parent_id
GROUP BY 1 ORDER BY 2 DESC;
Kết quả
 danh_muc_goc |  doanh_thu
--------------+-------------
 Điện tử      | 11161132000
 Gia dụng     |   773744000
 Thời trang   |   289773000
(3 rows)

Chú ý LEFT JOIN vào danh mục cha vì danh mục gốc có parent_id NULL (sản phẩm 19, 20 gắn trực tiếp 'Gia dụng'). INNER JOIN sẽ làm rơi doanh thu của chúng — lỗi mất dòng âm thầm.

Khách mua cả điện thoại lẫn phụ kiệnTrung bình

Đếm số khách đã mua (đơn không huỷ) ít nhất một sản phẩm thuộc danh mục 4 (Điện thoại) và ít nhất một sản phẩm thuộc danh mục 6 (Phụ kiện), bằng JOIN/GROUP BY (không dùng subquery).

Xem lời giải & giải thích
SELECT count(*) AS so_khach FROM (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON p.id = oi.product_id
  WHERE o.status <> 'cancelled' AND p.category_id IN (4, 6)
  GROUP BY o.customer_id
  HAVING count(DISTINCT p.category_id) = 2
) t;
Kết quả
 so_khach
----------
       52
(1 row)

Mẫu 'phải có đủ cả hai': lọc tập danh mục rồi HAVING count(DISTINCT ...) = số danh mục yêu cầu. Đừng dùng WHERE category_id = 4 AND category_id = 6 (không dòng nào thoả).

Đơn có thanh toán lệch tiền hàngKhó

Tìm các đơn mà số tiền payment khác tổng tiền hàng (sau giảm giá) — nếu không có, hãy chứng minh bằng đếm số đơn kiểm tra. Áp dụng mẫu 'tổng hợp trước, nối sau'.

Xem lời giải & giải thích
WITH hang AS (
  SELECT order_id, round(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100), 2) AS tien_hang
  FROM order_items oi GROUP BY order_id
)
SELECT count(*) AS so_don_doi_soat,
       count(*) FILTER (WHERE p.amount IS DISTINCT FROM h.tien_hang) AS so_don_lech
FROM payments p
JOIN hang h ON h.order_id = p.order_id;
Kết quả
 so_don_doi_soat | so_don_lech
-----------------+-------------
             320 |           0
(1 row)

Dùng IS DISTINCT FROM thay <> để NULL cũng được coi là 'lệch'. Truy vấn đối soát kiểu này nên chạy định kỳ như một 'unit test' cho dữ liệu.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Giải thích JOIN bằng mô hình 'mọi cặp rồi lọc' và dự đoán đúng số dòng kết quả
  • Viết được anti-join và biết điều kiện bảng phải đặt trong ON, không phải WHERE
  • Nhận ra fan-out và áp dụng 'tổng hợp về cùng grain trước, nối sau'
  • Dùng self-join, FULL JOIN đối soát, CROSS JOIN lưới tổ hợp và LATERAL top-N