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.
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ả.
| JOIN | Giữ lại | Dùng khi |
|---|---|---|
INNER JOIN | Chỉ cặp khớp | Cần dữ liệu cả hai phía (đơn hàng + khách của nó) |
LEFT JOIN | Mọi dòng trái; phải NULL nếu không khớp | Giữ "bên chính" dù không có dữ liệu liên quan; tìm bản ghi mồ côi |
RIGHT JOIN | Ngược LEFT | Hầu như không cần — đảo vế và dùng LEFT cho dễ đọc |
FULL JOIN | Mọi dòng cả hai bên | Đối soát hai nguồn dữ liệu |
CROSS JOIN | Tấ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; 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; 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)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; ky_vong | thuc_te
---------+---------
1200 | 1200
(1 row)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; 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)(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'; 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'; 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; 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)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); 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; 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; so_don | tong_tien_hang | tong_da_tra
--------+----------------+----------------
400 | 12817441000 | 10635679000.00
(1 row)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.
- 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?).
- 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.
- Nối các nhánh đã cùng grain bằng
LEFT JOIN(không mất dòng gốc),COALESCEcho số 0. - Đố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; 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; 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; 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; 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; 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; 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)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
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; 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.
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; 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.
Đế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; 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ả).
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; 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
-
PostgreSQL — Table Expressions (JOIN, LATERAL)Tài liệu chính thức về FROM, JOIN, LATERALDocs
-
A Visual Explanation of SQL Joins — Coding HorrorCách hình dung JOIN bằng Venn (chỉ để tham khảo, hãy ưu tiên mô hình 'tích rồi lọc')Bài viết
✅ 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