SQL 3 — JOIN toàn tập

Ở hai bài trước, ta luôn truy vấn trên một bảng duy nhất. Nhưng dữ liệu thực tế hiếm khi nằm gọn trong một bảng. Một khách hàng có nhiều tài khoản, mỗi tài khoản có nhiều giao dịch, mỗi nhân viên thuộc một phòng ban... Để trả lời những câu hỏi "kết hợp" như "liệt kê tên khách hàng kèm số dư tài khoản của họ", ta cần JOIN — phép kết nối các bảng lại với nhau.

Bài này đi từ con số 0: vì sao dữ liệu lại bị tách ra nhiều bảng, rồi từng loại JOIN (INNER, LEFT, RIGHT, FULL, SELF, CROSS), sự khác biệt giữa ON và WHERE, các bẫy chết người khi JOIN, và cuối cùng là các phép hợp tập UNION, INTERSECT, EXCEPT.

Vì sao cần JOIN? Câu chuyện về chuẩn hoá

Hãy tưởng tượng ta nhồi tất cả vào một bảng "phẳng" duy nhất:

Tên và thành phố của "Nguyễn An" bị lặp lại ở mỗi dòng giao dịch. Vấn đề:

• Lãng phí: cùng một thông tin lưu trùng hàng nghìn lần.
• Bất nhất (anomaly): nếu An đổi tên, ta phải sửa ở mọi dòng. Sót một dòng là dữ liệu sai.
• Khó quản lý: muốn thêm một khách hàng chưa có giao dịch nào thì... không có dòng nào để chèn.

Giải pháp là chuẩn hoá (normalization): tách dữ liệu thành các bảng theo từng "thực thể", liên kết qua khoá ngoại (foreign key). Tên khách hàng chỉ lưu một lần trong customers; bảng accounts chỉ giữ customer_id trỏ về.

JOIN chính là cơ chế ghép ngược các bảng đã tách này, dựa trên điều kiện khớp khoá. Đó là cái giá phải trả cho việc lưu trữ gọn gàng — và là kỹ năng cốt lõi nhất của SQL.

Các bảng sandbox dùng xuyên suốt bài: customers(id, full_name, city, created_at), accounts(id, customer_id, account_no, balance, currency), transactions(id, account_id, amount, kind, created_at), employees(id, name, department_id, salary, hired_at), departments(id, name).

INNER JOIN — chỉ giữ những gì khớp được

INNER JOIN (hay chỉ viết JOIN) trả về những hàng có cặp khớp ở cả hai bảng. Hàng nào không tìm được "bạn nhảy" bên kia sẽ bị loại.

▶ Chạy được trong SQL Builder

-- Tên khách hàng kèm số tài khoản và số dư
SELECT c.full_name,
       a.account_no,
       a.balance
FROM customers AS c
INNER JOIN accounts AS a
    ON a.customer_id = c.id
ORDER BY c.full_name;

Đọc câu này theo trình tự tư duy:

1. Lấy bảng customers (đặt bí danh c).
2. Với mỗi hàng của c, tìm trong accounts (a) các hàng thỏa a.customer_id = c.id.
3. Mỗi cặp khớp tạo ra một hàng kết quả ghép cột của cả hai.

Lưu ý cách dùng bí danh bảng (c, a) và tiền tố cột (c.full_name, a.balance). Khi hai bảng có cột trùng tên (cả hai đều có id và created_at), tiền tố là bắt buộc để tránh lỗi nhập nhằng.

Ghép ba bảng cũng chỉ là JOIN nối tiếp:

▶ Chạy được trong SQL Builder

-- Khách hàng -> tài khoản -> giao dịch
SELECT c.full_name,
       a.account_no,
       t.amount,
       t.kind
FROM customers AS c
INNER JOIN accounts AS a ON a.customer_id = c.id
INNER JOIN transactions AS t ON t.account_id = a.id
WHERE t.kind = 'credit'
ORDER BY t.amount DESC;

Kết quả ở đây chỉ chứa những khách hàng có tài khoản VÀ tài khoản đó có giao dịch nạp tiền. Khách hàng chưa mở tài khoản, hay tài khoản chưa giao dịch, đều biến mất. Đó là bản chất "inner" — phần giao nhau.

OUTER JOIN — giữ lại cả hàng không khớp

Đôi khi ta muốn giữ những hàng không có cặp. Ví dụ: "liệt kê TẤT CẢ khách hàng, kể cả người chưa mở tài khoản". Đây là lúc cần OUTER JOIN. Có ba kiểu: LEFT, RIGHT, FULL.

LEFT OUTER JOIN

LEFT JOIN giữ toàn bộ hàng của bảng bên trái. Hàng nào tìm được cặp bên phải thì ghép bình thường; hàng nào không có cặp vẫn được giữ, và các cột của bảng phải bị điền NULL.

▶ Chạy được trong SQL Builder

-- Tất cả nhân viên, kèm tên phòng ban (nếu có)
SELECT e.name,
       e.salary,
       d.name AS department
FROM employees AS e
LEFT JOIN departments AS d
    ON d.id = e.department_id
ORDER BY e.name;

Nếu một nhân viên có department_id là NULL (chưa được phân phòng) hoặc trỏ tới phòng không tồn tại, cột department sẽ là NULL — nhưng dòng nhân viên đó vẫn xuất hiện. So với INNER JOIN, nhân viên "vô gia cư" này sẽ bị mất.

Ứng dụng kinh điển của LEFT JOIN là tìm hàng mồ côi — những hàng bên trái không có cặp bên phải:

▶ Chạy được trong SQL Builder

-- Khách hàng CHƯA có tài khoản nào
SELECT c.id,
       c.full_name,
       c.city
FROM customers AS c
LEFT JOIN accounts AS a
    ON a.customer_id = c.id
WHERE a.id IS NULL           -- không tìm được cặp -> cột bảng phải là NULL

Mẹo: vì a.id là khoá chính của accounts, nó không bao giờ NULL ở một hàng thật. Vậy a.id IS NULL chỉ đúng khi LEFT JOIN không tìm được cặp nào — tức khách hàng không có tài khoản.

So sánh trực tiếp INNER vs LEFT

▶ Chạy được trong SQL Builder

-- Đếm số tài khoản mỗi khách hàng. LEFT JOIN cho người 0 tài khoản giá trị 0;
-- COUNT(a.id) đếm hàng KHỚP, nên khách 0 tài khoản ra 0 (không phải NULL).
SELECT c.full_name,
       COUNT(a.id) AS so_tai_khoan
FROM customers AS c
LEFT JOIN accounts AS a
    ON a.customer_id = c.id
GROUP BY c.id, c.full_name
ORDER BY so_tai_khoan DESC;

Nếu đổi LEFT JOIN thành INNER JOIN, khách hàng 0 tài khoản sẽ biến mất hoàn toàn khỏi báo cáo thay vì hiện số 0. Hãy chú ý: ta dùng COUNT(a.id) chứ không phải COUNT(*). Vì COUNT(*) đếm cả hàng có a.id là NULL (đếm thành 1), trong khi COUNT(a.id) bỏ qua NULL — đúng ý "đếm tài khoản thật".

RIGHT OUTER JOIN

RIGHT JOIN là gương soi của LEFT: nó giữ toàn bộ hàng của bảng bên phải.

▶ Chạy được trong SQL Builder

-- Tất cả phòng ban, kể cả phòng chưa có nhân viên
SELECT e.name AS employee,
       d.name AS department
FROM employees AS e
RIGHT JOIN departments AS d
    ON d.id = e.department_id
ORDER BY d.name;

Phòng ban chưa có ai sẽ hiện một dòng với employee là NULL. Thực tế lập trình viên ít dùng RIGHT JOIN — mọi RIGHT JOIN đều viết lại được thành LEFT JOIN bằng cách đảo thứ tự hai bảng, mà LEFT đọc tự nhiên hơn (bảng chính nằm trước):

-- Tương đương câu RIGHT JOIN ở trên:
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id

FULL OUTER JOIN

FULL OUTER JOIN giữ tất cả hàng của cả hai bảng: khớp thì ghép, không khớp thì điền NULL ở vế thiếu. Nó là "LEFT + RIGHT gộp lại".

▶ Chạy được trong SQL Builder

-- Mọi nhân viên VÀ mọi phòng ban, dù có khớp hay không
SELECT e.name AS employee,
       d.name AS department
FROM employees AS e
FULL OUTER JOIN departments AS d
    ON d.id = e.department_id
ORDER BY d.name, e.name;

Kết quả gồm: nhân viên không có phòng (department = NULL), phòng không có nhân viên (employee = NULL), và các cặp khớp bình thường. FULL JOIN hữu ích khi đối soát hai nguồn dữ liệu và muốn thấy cả "thừa hai bên".

Lưu ý: MySQL không hỗ trợ FULL OUTER JOIN trực tiếp — phải giả lập bằng LEFT JOIN UNION RIGHT JOIN. PostgreSQL, SQL Server, Oracle, SQLite (3.39+) thì hỗ trợ thẳng.

Tóm tắt bằng "Venn" và bảng

Hình dung mỗi bảng là một vòng tròn. Phần giao nhau là các hàng khớp được.

SELF JOIN — bảng tự nối với chính nó

Đôi khi quan hệ nằm trong cùng một bảng. Ví dụ kinh điển: bảng nhân viên có cột "người quản lý" cũng là một nhân viên. Trong sandbox của ta, hãy minh hoạ SELF JOIN qua một câu hỏi hợp lý hơn: "tìm các cặp nhân viên cùng phòng ban".

Mấu chốt là tham chiếu bảng hai lần với hai bí danh khác nhau, coi như hai bảng độc lập:

▶ Chạy được trong SQL Builder

-- Các cặp nhân viên cùng phòng ban (không trùng cặp, không tự ghép chính mình)
SELECT e1.name AS nhan_vien_a,
       e2.name AS nhan_vien_b,
       d.name  AS department
FROM employees AS e1
INNER JOIN employees AS e2
    ON e1.department_id = e2.department_id   -- cùng phòng
    AND e1.id < e2.id                         -- tránh (A,A) và lặp (B,A)
INNER JOIN departments AS d
    ON d.id = e1.department_id
ORDER BY d.name, nhan_vien_a;

Điều kiện e1.id < e2.id cực kỳ quan trọng:

• e1.id <> e2.id thì loại được cặp tự ghép chính mình, nhưng vẫn cho cả (An, Bình) lẫn (Bình, An) — trùng.
• e1.id < e2.id giữ mỗi cặp đúng một lần, theo thứ tự id tăng.

Một ví dụ SELF JOIN khác — so sánh lương trong cùng phòng:

▶ Chạy được trong SQL Builder

-- Nhân viên có người cùng phòng lương cao hơn mình
SELECT e1.name AS nhan_vien,
       e1.salary,
       e2.name AS nguoi_luong_cao_hon,
       e2.salary AS luong_cao_hon
FROM employees AS e1
INNER JOIN employees AS e2
    ON e1.department_id = e2.department_id
    AND e2.salary > e1.salary
ORDER BY e1.name;

CROSS JOIN — tích Descartes

CROSS JOIN ghép mỗi hàng bảng A với mọi hàng bảng B, không cần điều kiện. Nếu A có m hàng, B có n hàng, kết quả là m × n hàng. Đây gọi là tích Descartes (Cartesian product).

▶ Chạy được trong SQL Builder

-- Mọi cặp (phòng ban × loại tiền tệ giả định) để dựng khung báo cáo
SELECT d.name AS department,
       cur.currency
FROM departments AS d
CROSS JOIN (SELECT DISTINCT currency FROM accounts) AS cur
ORDER BY d.name, cur.currency;

CROSS JOIN dùng có chủ đích khi cần sinh tổ hợp đầy đủ (ví dụ: dựng lưới "mọi sản phẩm × mọi tháng" để báo cáo, kể cả ô không có dữ liệu). Nhưng nó cũng là thủ phạm gây bùng nổ hàng khi bạn quên điều kiện JOIN:

-- BẪY: thiếu ON -> vô tình thành CROSS JOIN, ra customers × accounts hàng!
SELECT c.full_name, a.account_no
FROM customers AS c, accounts AS a;     -- không có WHERE/ON khớp khoá

Câu trên với 1.000 khách và 2.000 tài khoản sẽ trả về 2 triệu hàng vô nghĩa.

ON vs WHERE — khác biệt tinh tế nhưng chí mạng

Cả ON và WHERE đều là điều kiện lọc, nhưng thời điểm áp dụng khác nhau, và điều này thay đổi kết quả của OUTER JOIN.

• ON: áp dụng trong lúc ghép hai bảng (quyết định hàng nào khớp).
• WHERE: áp dụng sau khi đã ghép xong, lọc trên bảng kết quả.

Với INNER JOIN, đặt điều kiện ở ON hay WHERE cho kết quả giống nhau. Nhưng với LEFT JOIN, chúng khác hẳn. Xét hai câu sau:

▶ Chạy được trong SQL Builder

-- (A) Điều kiện ở ON: giữ MỌI khách, chỉ ghép tài khoản USD
SELECT c.full_name, a.account_no, a.currency
FROM customers AS c
LEFT JOIN accounts AS a
    ON a.customer_id = c.id
    AND a.currency = 'USD'         -- lọc trong lúc ghép
ORDER BY c.full_name;

Câu (A) giữ tất cả khách hàng. Khách có tài khoản USD thì hiện tài khoản đó; khách không có tài khoản USD vẫn xuất hiện với cột tài khoản = NULL.

▶ Chạy được trong SQL Builder

-- (B) Điều kiện ở WHERE: vô tình biến LEFT JOIN thành INNER JOIN!
SELECT c.full_name, a.account_no, a.currency
FROM customers AS c
LEFT JOIN accounts AS a
    ON a.customer_id = c.id
WHERE a.currency = 'USD'           -- lọc sau khi ghép
ORDER BY c.full_name;

Câu (B) "trông giống" nhưng làm điều khác hẳn. Sau khi LEFT JOIN, khách không có tài khoản USD có cột a.currency = NULL. Mệnh đề WHERE a.currency = 'USD' đánh giá NULL = 'USD' ra không-đúng, nên loại các hàng đó. Kết quả: chỉ còn khách có tài khoản USD — LEFT JOIN bị "tụt hạng" thành INNER JOIN.

Đây là một trong những bẫy phổ biến nhất: lọc cột của bảng LEFT (bảng "tuỳ chọn") trong WHERE sẽ vô tình loại các hàng NULL, làm mất tính chất OUTER. Quy tắc: điều kiện liên quan bảng-bên-phải-tuỳ-chọn đặt ở ON; điều kiện trên bảng-bên-trái-bắt-buộc đặt ở WHERE.

Bẫy: nhân bản hàng khi JOIN 1-n (fan-out)

Đây là lỗi gây sai số liệu âm thầm nhất. Khi JOIN một bảng "1" với bảng "nhiều", hàng bên "1" bị nhân lên theo số hàng khớp bên "nhiều".

▶ Chạy được trong SQL Builder

-- Một tài khoản có nhiều giao dịch -> dòng account bị lặp theo số giao dịch
SELECT a.account_no,
       a.balance,
       t.amount
FROM accounts AS a
INNER JOIN transactions AS t
    ON t.account_id = a.id
ORDER BY a.account_no;

Nếu ACC001 có 5 giao dịch, thì account_no = 'ACC001' và balance của nó xuất hiện 5 lần. Đó là hành vi đúng của JOIN — nhưng nó trở thành bẫy khi ta vô tư tính tổng:

▶ Chạy được trong SQL Builder

-- SAI: balance bị cộng lặp theo từng giao dịch -> tổng phồng to
SELECT SUM(a.balance) AS tong_so_du_SAI
FROM accounts AS a
INNER JOIN transactions AS t ON t.account_id = a.id;

Vì mỗi hàng giao dịch kéo theo một bản sao balance, SUM(a.balance) cộng balance của ACC001 năm lần. Cách đúng là gộp trước rồi mới JOIN, hoặc tính tổng trên đúng mức chi tiết:

▶ Chạy được trong SQL Builder

-- ĐÚNG: tổng số dư các tài khoản (không liên quan số giao dịch)
SELECT SUM(balance) AS tong_so_du_DUNG
FROM accounts;

▶ Chạy được trong SQL Builder

-- ĐÚNG: nếu cần cả số giao dịch, gộp transactions trước khi JOIN
SELECT a.account_no,
       a.balance,
       COALESCE(s.so_giao_dich, 0) AS so_giao_dich
FROM accounts AS a
LEFT JOIN (
    SELECT account_id, COUNT(*) AS so_giao_dich
    FROM transactions
    GROUP BY account_id
) AS s ON s.account_id = a.id
ORDER BY a.account_no;

Quy tắc vàng: trước khi SUM/AVG sau một JOIN 1-n, hãy tự hỏi "hàng này có bị nhân bản không?". Nếu có, gộp (aggregate) bảng "nhiều" thành một hàng trước, rồi mới JOIN.

Phép hợp tập: UNION, UNION ALL, INTERSECT, EXCEPT

JOIN ghép bảng theo chiều ngang (thêm cột). Phép hợp tập ghép theo chiều dọc (chồng hàng). Yêu cầu: các câu SELECT phải có cùng số cột và kiểu dữ liệu tương thích, theo đúng thứ tự.

UNION vs UNION ALL

UNION chồng hai tập hàng và loại bỏ trùng lặp. UNION ALL giữ tất cả, kể cả trùng — và nhanh hơn vì không phải khử trùng.

▶ Chạy được trong SQL Builder

-- Danh sách thành phố xuất hiện ở khách hàng (gộp, bỏ trùng)
SELECT city FROM customers WHERE city = 'Hà Nội'
UNION
SELECT city FROM customers WHERE city = 'Hồ Chí Minh';

▶ Chạy được trong SQL Builder

-- Gộp hai "luồng" sự kiện vào một cột nhãn chung
SELECT 'customer' AS loai, full_name AS ten, created_at
FROM customers
UNION ALL
SELECT 'employee' AS loai, name AS ten, hired_at
FROM employees
ORDER BY created_at DESC;

Mẹo: nếu bạn chắc chắn hai tập không trùng (hoặc trùng cũng không sao), hãy dùng UNION ALL để tránh chi phí khử trùng. Chỉ dùng UNION khi thực sự cần loại trùng.

INTERSECT và EXCEPT

INTERSECT trả về hàng có ở cả hai tập (phần giao). EXCEPT (Oracle gọi là MINUS) trả về hàng có ở tập đầu nhưng không có ở tập sau (phần hiệu).

(minh hoạ, không chạy trong sandbox — INTERSECT bị chặn trong sandbox)

-- Thành phố vừa có khách hàng vừa có... khách hàng tạo gần đây (ví dụ minh hoạ INTERSECT)
SELECT city FROM customers
INTERSECT
SELECT city FROM customers WHERE created_at >= '2024-01-01';

(minh hoạ, không chạy trong sandbox — EXCEPT bị chặn trong sandbox)

-- Thành phố có khách hàng NHƯNG không có khách nào tạo từ 2024 trở đi
SELECT city FROM customers
EXCEPT
SELECT city FROM customers WHERE created_at >= '2024-01-01';

Tương thích: PostgreSQL, SQL Server, SQLite hỗ trợ INTERSECT/EXCEPT. MySQL hỗ trợ từ phiên bản 8.0.31; bản cũ phải giả lập bằng JOIN hoặc subquery.

Lưu ý quan trọng: INTERSECT và EXCEPT so sánh toàn bộ hàng và mặc định loại trùng (như UNION). Chúng thường dùng cho bài toán tập hợp trên một cột khoá; với logic phức tạp, JOIN hoặc subquery (bài sau) thường linh hoạt hơn.

Tóm tắt

• Dữ liệu được chuẩn hoá thành nhiều bảng liên kết qua khoá ngoại để tránh trùng lặp và bất nhất; JOIN là cơ chế ghép chúng lại.
• INNER JOIN chỉ giữ hàng khớp ở cả hai bảng. LEFT/RIGHT/FULL OUTER JOIN giữ thêm hàng không khớp, điền NULL ở vế thiếu.
• SELF JOIN nối bảng với chính nó (hai bí danh), dùng id < id để tránh trùng/tự-ghép cặp. CROSS JOIN sinh tích Descartes m×n — hữu ích có chủ đích, nguy hiểm khi quên điều kiện.
• ON lọc trong lúc ghép, WHERE lọc sau khi ghép. Lọc cột bảng-LEFT trong WHERE sẽ vô tình biến LEFT JOIN thành INNER JOIN.
• Bẫy fan-out: JOIN 1-n nhân bản hàng bên "1"; đừng SUM/AVG vội — hãy gộp bảng "nhiều" trước.
• UNION (bỏ trùng) vs UNION ALL (giữ tất cả, nhanh hơn); INTERSECT (giao), EXCEPT/MINUS (hiệu) ghép hàng theo chiều dọc, yêu cầu cùng số cột và kiểu tương thích.

Tự kiểm tra

1. Một câu LEFT JOIN giữa customers và accounts. Nếu một khách hàng không có tài khoản nào, các cột của accounts trong kết quả sẽ có giá trị gì? Khách đó còn xuất hiện trong kết quả không?
2. Vì sao COUNT(a.id) và COUNT(*) cho kết quả khác nhau khi đứng sau một LEFT JOIN có hàng không khớp?
3. Hai câu chỉ khác nhau ở chỗ đặt a.currency = 'USD' trong ON hay trong WHERE của một LEFT JOIN. Câu nào giữ lại khách không có tài khoản USD, và tại sao?
4. Trong SELF JOIN tìm cặp nhân viên cùng phòng, vì sao dùng e1.id < e2.id tốt hơn e1.id <> e2.id?
5. Bạn JOIN accounts với transactions (1-n) rồi viết SUM(a.balance). Kết quả sai như thế nào và sửa ra sao?
6. Khi nào nên dùng UNION ALL thay vì UNION? Cho một tình huống cụ thể.

Đọc tiếp

SQL 4 — Subquery, CTE & Window