Buổi 8/10

    JOIN - Kết hợp bảng

    180 phút
    Thực hành: 0/13 bài
    📊 Xem sơ đồ dữ liệu mẫu (dùng trong mọi buổi)
    Sơ đồ dữ liệu mẫu của khóa học (chứng khoán)
    1n1n1n1n1n1n1n1n111nCustomerscustomer_idPKcustomer_codefull_namecustomer_typecitystatusreferrer_idFKAccountsaccount_idPKcustomer_idFKaccount_noaccount_typebalancestatusOrdersorder_idPKaccount_idFKsymbolFKsidequantitypriceorder_datestatusTradestrade_idPKorder_idFKsymbolFKquantitypricetrade_dateCustomerStatscustomer_idPK,FKtotal_trade_valuetrade_countPortfolioaccount_idPK,FKsymbolPK,FKquantityavg_costSecuritiessymbolPKcompany_nameexchangesectorMarketPricessymbolPK,FKtrade_datePKclose_pricevolumeAuditLoglog_idPKcustomer_idold_statusnew_statuschanged_at

    PK = khóa chính, FK = khóa ngoài. Đường nối: 1 ở phía bảng 'một', n ở phía bảng 'nhiều'. Đường nét đứt: liên hệ logic (không khai báo FK). Quan hệ n–n giữa Accounts và Securities đi qua bảng Portfolio.

    Buổi 8: JOIN - Kết hợp bảng

    Buổi 7 đã cho ta bản đồ: khách hàng có tài khoản, tài khoản có lệnh, lệnh có giao dịch. Nhưng bản đồ chỉ để hiểu; muốn ra báo cáo thật ("khách hàng nào giao dịch nhiều nhất tuần này", "mã nào không có ai mua", "ai chưa từng giao dịch") ta phải kết hợp các bảng trong một câu truy vấn. Công cụ đó là JOIN.

    Buổi này đi qua các loại JOIN theo thứ tự: INNER, LEFT, RIGHT, FULL, CROSS, Self Join; sau đó áp dụng vào bài toán phân tích (tổng/trung bình giá trị giao dịch, mã bán chạy nhất) và nói nhẹ về hiệu năng (index). Mỗi loại JOIN đều có ví dụ chạy được ngay (nút "Chạy thử") và bài tập ngay bên dưới - học đến đâu làm đến đó. Mọi ví dụ chạy được trên cả SQL Server và MySQL 8; chỗ nào cú pháp khác nhau sẽ có tiêu đề nhỏ.

    Ví dụ nghiệp vụ: bảng Trades chỉ có order_id và symbol, không có tên khách hàng. Muốn gửi báo cáo khớp lệnh cuối ngày cho từng khách, phòng vận hành phải nối Trades → Orders → Accounts → Customers. Không có JOIN thì không có báo cáo nào có ý nghĩa.

    Quy ước: bộ dữ liệu mẫu xem mục "Bộ dữ liệu mẫu của khóa học" ở Buổi 1 và các quan hệ ở Buổi 7. "Hôm nay" cố định là 2026-09-25. Trong buổi này, "giao dịch" = Trades (kết quả khớp lệnh, có quantity, price), "sản phẩm" = Securities (mã chứng khoán).

    Chuỗi khóa ngoài ta sẽ đi qua rất nhiều lần:

    text
    Customers --(customer_id)--> Accounts --(account_id)--> Orders --(order_id)--> Trades --(symbol)--> Securities

    Mục tiêu buổi học

    • Sau buổi này bạn có thể nối bảng bằng INNER JOIN (2 và nhiều bảng) theo chuỗi khóa ngoài.
    • Sau buổi này bạn có thể dùng LEFT/RIGHT JOIN để giữ hàng của một phía và tìm hàng không khớp (IS NULL).
    • Sau buổi này bạn có thể giải thích FULL OUTER JOIN và viết cách thay thế trên MySQL bằng UNION.
    • Sau buổi này bạn có thể dùng CROSS JOIN đúng chỗ (dựng lưới báo cáo) và biết khi nào không nên dùng.
    • Sau buổi này bạn có thể viết Self Join: cặp khách cùng thành phố, cây người giới thiệu.
    • Sau buổi này bạn có thể phân tích nghiệp vụ bằng JOIN + GROUP BY (tổng/trung bình giá trị giao dịch, mã bán chạy) và nhận biết ảnh hưởng của Index tới hiệu năng JOIN.

    Bản đồ buổi học

    Phần Nội dung Thời lượng gợi ý
    Mở đầu Tổng quan các loại JOIN (hình Venn), bảng mini 4 phút
    1. INNER JOIN Khái niệm, cú pháp, nhiều bảng, khóa kép 12 phút
    Thực hành bài 8.1 INNER JOIN 2 bảng, 3 bảng, 5 bảng (3 bài, gồm chữa bài) 22 phút
    1.4 Hiệu năng Index, kế hoạch thực thi 5 phút
    2-5. LEFT / RIGHT / FULL / CROSS Giữ hàng một phía, anti-join, đối chiếu, lưới báo cáo 22 phút
    Thực hành bài 8.2 LEFT, anti-join, RIGHT, FULL, CROSS (5 bài, gồm chữa bài) 48 phút
    6. Self Join Cặp cùng thành phố, cây người giới thiệu 6 phút
    Thực hành bài 8.4 Self Join một và hai cấp (2 bài, gồm chữa bài) 22 phút
    7. Phân tích bằng JOIN JOIN + GROUP BY: tổng, trung bình, bán chạy 7 phút
    Thực hành bài 8.3 Phân tích giá trị giao dịch (3 bài, gồm chữa bài) 27 phút
    Tổng kết Tóm tắt, sai lầm thường gặp, giao bài về nhà 5 phút
    Tổng Lý thuyết + demo ~61 phút, thực hành ~119 phút (66%) 180 phút

    Tổng quan các loại JOIN

    JOIN ghép các dòng của hai bảng thành các dòng rộng hơn, dựa trên một điều kiện nối (thường là khóa ngoài = khóa chính). Các loại JOIN chỉ khác nhau ở một điều: xử lý thế nào với dòng không tìm được đối tác ở bảng kia. Hình dưới dùng biểu đồ Venn để so sánh năm loại JOIN mà ta sẽ học.

    Các loại JOIN (A = bảng bên trái, B = bảng bên phải)
    INNER JOIN
    Chỉ dòng khớp ở cả A và B
    LEFT JOIN
    Mọi dòng của A + phần khớp của B (không khớp → NULL)
    RIGHT JOIN
    Mọi dòng của B + phần khớp của A
    FULL JOIN
    Mọi dòng của cả hai (MySQL: dùng UNION)
    CROSS JOIN
    Tích Descartes: mỗi dòng A ghép mỗi dòng B

    Vùng tô đậm là phần được trả về. Ví dụ nghiệp vụ: A = Customers, B = Orders; LEFT JOIN + điều kiện B IS NULL cho ra các khách hàng chưa có lệnh nào.

    Cách đọc hình: hai vòng tròn là hai bảng (trái và phải); phần giao nhau là các dòng khớp điều kiện; phần tô màu là những dòng được giữ lại trong kết quả. INNER chỉ giữ phần giao; LEFT giữ cả vòng trái; RIGHT giữ cả vòng phải; FULL giữ cả hai vòng; riêng CROSS không có điều kiện nối nên ghép mọi dòng với mọi dòng.

    Để dễ hình dung, lấy một ví dụ mini: bảng trái là 3 khách hàng, bảng phải là 2 tài khoản (nối theo customer_id).

    Bảng trái: khách hàng Bảng phải: tài khoản
    customer_id = 1 Nguyen Van An ACC00001 thuộc khách 1
    customer_id = 10 Ngo Thi Nga ACC00002 thuộc khách 2 (không có trong bảng trái)
    customer_id = 15 Vo Van Tuan
    Loại JOIN Kết quả (khách / tài khoản) Số dòng
    INNER (1, ACC00001) 1
    LEFT (1, ACC00001), (10, NULL), (15, NULL) 3
    RIGHT (1, ACC00001), (NULL, ACC00002) 2
    FULL (1, ACC00001), (10, NULL), (15, NULL), (NULL, ACC00002) 4
    CROSS mọi cặp 3 x 2 6

    SQL Server vs MySQL: INNER, LEFT, RIGHT, CROSS JOIN chạy giống nhau trên cả hai hệ. Khác biệt lớn nhất: MySQL không có FULL JOIN (mục 4). Ngoài ra MySQL có JOIN ... USING (cột) và NATURAL JOIN còn T-SQL không hỗ trợ - khóa học này luôn dùng JOIN ... ON để một câu lệnh chạy được trên cả hai.

    1. INNER JOIN

    1.1. Khái niệm

    INNER JOIN chỉ trả về các cặp dòng khớp điều kiện ở cả hai bảng. Dòng nào ở một bên không tìm được dòng tương ứng bên kia thì biến mất khỏi kết quả.

    1.2. Cú pháp

    SQL
    SELECT ...
FROM   bảng1 [AS] b1
INNER JOIN bảng2 [AS] b2 ON b1.cột_khóa = b2.cột_khóa;

    INNER có thể bỏ (JOIN mặc định là INNER JOIN). Điều kiện nối đặt sau ON, thường là khóa ngoài = khóa chính. Dùng bí danh (alias) c, a... để ngắn gọn và tránh mơ hồ khi hai bảng có cùng tên cột (customer_id, symbol...).

    Ví dụ: khách hàng cùng tài khoản của họ.

    SQL · chạy thử được
    SELECT c.customer_id, c.full_name, a.account_no
FROM Customers c
INNER JOIN Accounts a ON a.customer_id = c.customer_id
WHERE c.customer_id IN (1, 10, 15)
ORDER BY c.customer_id, a.account_no;

    Khách 10 và 15 không xuất hiện: họ chưa có tài khoản nên không có dòng nào để khớp. Đó chính là bản chất của INNER JOIN, và cũng là lý do ta cần LEFT JOIN ở mục 2.

    Mẹo: trước khi chạy, hãy đoán số dòng. INNER JOIN 1-n cho ra đúng bằng số dòng của bảng "n" có đối tác (ở đây 1 dòng). Tập thói quen này giúp bạn phát hiện ngay khi JOIN sai điều kiện (số dòng bất ngờ tăng vọt hoặc rụng mất).

    1.3. Nối nhiều bảng (Multiple INNER JOIN)

    Muốn lấy tên khách hàng cho mỗi giao dịch, phải đi hết chuỗi khóa ngoài: Trades → Orders → Accounts → Customers. Mỗi JOIN thêm một bảng, mỗi lần nối theo một khóa ngoài:

    SQL · chạy thử được
    SELECT t.trade_id, c.full_name, t.symbol, t.quantity, t.price, t.trade_date
FROM Trades t
INNER JOIN Orders    o ON o.order_id    = t.order_id
INNER JOIN Accounts  a ON a.account_id  = o.account_id
INNER JOIN Customers c ON c.customer_id = a.customer_id
WHERE t.trade_date >= '2026-09-24'
ORDER BY t.trade_id;

    Kết quả có 14 dòng, mở đầu bằng: 2 Quy Dau tu Tang Truong Xanh MBS 2000 25000.00 2026-09-24, 5 Ngan Hang Nong Nghiep Phat Trien VCB 500 92000.00 2026-09-24...

    Thêm bảng Securities để lấy tên công ty và lọc theo sàn (nối theo t.symbol), ta có truy vấn 5 bảng:

    SQL · chạy thử được
    SELECT t.trade_id, c.full_name, s.company_name, t.quantity * t.price AS gross_value
FROM Trades t
INNER JOIN Orders o ON o.order_id = t.order_id
INNER JOIN Accounts a ON a.account_id = o.account_id
INNER JOIN Customers c ON c.customer_id = a.customer_id
INNER JOIN Securities s ON s.symbol = t.symbol
WHERE s.exchange = 'HNX' AND t.trade_date >= '2026-09-21'
ORDER BY t.trade_id;

    Cách đọc/viết truy vấn nhiều bảng: (1) xác định bảng "gốc" chứa thứ cần đếm/liệt kê (ở đây Trades); (2) đi dần ra từng bảng cần thêm thông tin; (3) mỗi JOIN một ON khớp đúng khóa; (4) cuối cùng mới viết WHERE, GROUP BY, ORDER BY.

    Điều kiện trong ON hay WHERE? Với INNER JOIN, hai nơi cho kết quả giống hệt (ON để nối, WHERE để lọc - nên tách rõ cho dễ đọc). Với LEFT JOIN thì khác hẳn, xem mục 2.3.

    Nối theo khóa kép (nhiều điều kiện trong ON). Khi khóa của bảng gồm nhiều cột (MarketPrices có khóa (symbol, trade_date)), phải nối bằng đủ các cột với AND. Ví dụ so sánh giá đặt của lệnh với giá đóng cửa của cùng mã, cùng ngày:

    SQL · chạy thử được
    SELECT o.order_id, o.symbol, o.order_date, o.price AS gia_dat, m.close_price AS gia_dong_cua,
       o.price - m.close_price AS chenh_lech
FROM Orders o
INNER JOIN MarketPrices m ON m.symbol = o.symbol AND m.trade_date = o.order_date
WHERE o.order_id IN (2, 3, 5, 6, 8, 9)
ORDER BY o.order_id;

    Cảnh báo: thiếu một vế của khóa kép (chỉ m.symbol = o.symbol) thì mỗi lệnh sẽ ghép với mọi ngày của mã đó - số dòng tăng gấp bội và kết quả sai. Bảng có khóa kép luôn phải nối đủ khóa.

    Lưu ý: INNER JOIN này chỉ trả về lệnh nào có giá thị trường cùng ngày (72 trên 120 lệnh). Các lệnh còn lại không có dòng giá cùng ngày nên "biến mất" - nếu cần giữ đủ lệnh, dùng LEFT JOIN (mục 2).

    Vừa học xong INNER JOIN cho hai, ba và nhiều bảng theo chuỗi khóa ngoài, hãy làm bài 8.1 để nối khách hàng với tài khoản, giao dịch với tên khách hàng và tên sản phẩm.

    1.4. Hiệu năng: Index và tối ưu hóa

    Với dữ liệu mẫu vài trăm dòng, JOIN nào cũng nhanh. Trên hệ thống thật (hàng chục triệu lệnh), tốc độ JOIN phụ thuộc chủ yếu vào việc cột dùng trong ON có index hay không. Không có index, hệ quản trị phải quét toàn bộ bảng con cho mỗi dòng của bảng cha; có index, nó "tra mục lục" và nhảy thẳng tới dòng cần (chi tiết cấu trúc index ở Buổi 10).

    • Khóa chính luôn có index. Khóa ngoài thì không luôn có: MySQL/InnoDB tự tạo index cho cột FK, còn SQL Server không - nên tự tạo cho các cột hay JOIN:
    SQL
    -- Cú pháp giống nhau ở SQL Server và MySQL
CREATE INDEX IX_Orders_account_id  ON Orders (account_id);
CREATE INDEX IX_Trades_order_id    ON Trades (order_id);
CREATE INDEX IX_Trades_symbol_date ON Trades (symbol, trade_date);   -- index kép cho lọc theo mã + ngày
    • Ba thói quen tối ưu cơ bản: (1) chỉ SELECT cột cần dùng, tránh SELECT * khi nối nhiều bảng; (2) lọc sớm (WHERE) bằng cột có index và không bọc hàm quanh cột (WHERE trade_date >= '2026-09-24' tốt hơn WHERE YEAR(trade_date) = 2026); (3) kiểm tra kế hoạch thực thi.
    Việc SQL Server (SSMS) MySQL
    Xem kế hoạch thực thi Bấm Include Actual Execution Plan (Ctrl+M) rồi chạy Đặt EXPLAIN trước câu SELECT (xem cột type, key, rows)
    Xem số trang đọc SET STATISTICS IO ON; EXPLAIN ANALYZE (MySQL 8)
    Thuật toán nối thường thấy Nested Loops / Hash Match / Merge Join Nested Loop / Hash Join (8.0.18+)

    Hai thao tác xem kế hoạch này làm trên máy của bạn, hệ thống chấm bài không kiểm tra.

    Lưu ý: đừng thêm index vô tội vạ: mỗi index làm INSERT/UPDATE chậm đi và tốn dung lượng. Index phục vụ truy vấn đọc thường xuyên.

    Cảnh báo: viết kiểu "nối cũ" FROM Customers c, Accounts a WHERE c.customer_id = a.customer_id. Nó chạy, nhưng quên mất điều kiện là thành tích Descartes (mục 5) trả về hàng chục nghìn dòng vô nghĩa. Luôn dùng JOIN ... ON.

    2. LEFT JOIN (LEFT OUTER JOIN)

    2.1. Khái niệm

    LEFT JOIN trả về tất cả dòng của bảng trái, kèm dòng khớp từ bảng phải; nếu không khớp, các cột của bảng phải là NULL. Dùng khi câu hỏi có dạng "tất cả khách hàng, kể cả những người chưa ...".

    SQL · chạy thử được
    SELECT c.customer_id, c.full_name, a.account_no
FROM Customers c
LEFT JOIN Accounts a ON a.customer_id = c.customer_id
WHERE c.customer_id IN (1, 10, 15)
ORDER BY c.customer_id, a.account_no;

    So với INNER JOIN ở mục 1.2, hai khách chưa có tài khoản (10 và 15) đã xuất hiện với NULL. Cú pháp đầy đủ là LEFT OUTER JOIN; từ OUTER có thể bỏ.

    2.2. Tìm hàng không khớp: WHERE bảng_phải.id IS NULL

    Sau khi LEFT JOIN, những dòng có cột khóa của bảng phải bằng NULL chính là các dòng không có đối tác. Đây là kỹ thuật anti-join kinh điển: "khách hàng chưa có giao dịch nào".

    Vì "giao dịch của khách" cách khách hàng 3 bảng (Accounts, Orders, Trades), ta gom "khách hàng đã có giao dịch" thành một bảng dẫn xuất rồi LEFT JOIN vào Customers:

    SQL · chạy thử được
    SELECT c.customer_id, c.full_name
FROM Customers c
LEFT JOIN (SELECT DISTINCT a.customer_id
           FROM Accounts a
           INNER JOIN Orders o ON o.account_id = a.account_id
           INNER JOIN Trades t ON t.order_id   = o.order_id) x
       ON x.customer_id = c.customer_id
WHERE x.customer_id IS NULL
ORDER BY c.customer_id;

    Kết quả có 12 khách; chú ý khách 3 (Le Van Cuong): có tài khoản, có 3 lệnh, nhưng lệnh chưa khớp nên vẫn "chưa có giao dịch". Đây là lý do phải nối tới tận Trades chứ không dừng ở Orders.

    Ba cách viết anti-join:

    Cách Ưu điểm Lưu ý
    LEFT JOIN ... WHERE phải.khóa IS NULL Đọc trực quan, thường tối ưu tốt Phải chọn cột khóa (luôn không NULL khi khớp) để kiểm tra
    NOT EXISTS (SELECT 1 ...) An toàn với NULL Dài hơn một chút
    NOT IN (SELECT ...) Ngắn Nguy hiểm khi tập con có NULL (xem Buổi 7)

    2.3. Bẫy: điều kiện của bảng phải phải đặt ở ON, không phải WHERE

    So sánh hai truy vấn "tài khoản CLOSED của khách 4, 5, 10":

    SQL · chạy thử được
    -- (A) Điều kiện trong ON: vẫn giữ mọi khách
SELECT c.customer_id, a.account_no, a.status
FROM Customers c LEFT JOIN Accounts a ON a.customer_id = c.customer_id AND a.status = 'CLOSED'
WHERE c.customer_id IN (4, 5, 10)
ORDER BY c.customer_id, a.account_no;
    SQL · chạy thử được
    -- (B) Điều kiện trong WHERE: LEFT JOIN "biến" thành INNER JOIN
SELECT c.customer_id, a.account_no, a.status
FROM Customers c LEFT JOIN Accounts a ON a.customer_id = c.customer_id
WHERE a.status = 'CLOSED' AND c.customer_id IN (4, 5, 10)
ORDER BY c.customer_id, a.account_no;

    Ở (B), WHERE a.status = 'CLOSED' loại luôn các dòng NULL nên khách 5 và 10 mất (chỉ còn khách 4). Quy tắc: điều kiện lọc bảng bên phải của LEFT JOIN phải nằm trong ON; điều kiện lọc bảng bên trái mới nằm trong WHERE. (Bài 8.2c dùng đúng quy tắc này.)

    Vị trí điều kiện lọc bảng phải Ý nghĩa Kết quả
    Trong ON "Chỉ nối những dòng phải thỏa điều kiện" Giữ đủ dòng bảng trái
    Trong WHERE "Chỉ giữ kết quả thỏa điều kiện" Loại các dòng NULL → thành INNER JOIN

    2.4. Bẫy: nhân đôi dòng (fan-out) và cách tránh

    Khi nối theo chuỗi 1-n-n, dòng của bảng trên bị lặp theo số dòng của bảng dưới. Bài 8.2a yêu cầu khách chưa có giao dịch xuất hiện đúng một dòng dù họ có nhiều lệnh chưa khớp, vì nếu LEFT JOIN trực tiếp Accounts → Orders → Trades, khách 3 (3 lệnh chưa khớp) sẽ hiện 3 dòng NULL. Cách xử lý: nối vào một bảng đã INNER JOIN sẵn (như mục 2.2) hoặc SELECT DISTINCT.

    Nguy hiểm hơn là cộng sai số: cộng số dư sau khi nối thêm bảng Orders sẽ lặp số dư theo số lệnh.

    SQL · chạy thử được
    -- Sai: nối thêm Orders, số dư mỗi tài khoản bị cộng lặp theo số lệnh
SELECT c.customer_id, SUM(a.balance) AS sai
FROM Customers c JOIN Accounts a ON a.customer_id = c.customer_id
                 JOIN Orders o   ON o.account_id  = a.account_id
WHERE c.customer_id = 2 GROUP BY c.customer_id;
    SQL · chạy thử được
    -- Đúng: chỉ nối Accounts
SELECT c.customer_id, SUM(a.balance) AS dung
FROM Customers c JOIN Accounts a ON a.customer_id = c.customer_id
WHERE c.customer_id = 2 GROUP BY c.customer_id;

    Nguyên tắc vàng: tổng hợp ở mức chi tiết nhất trước, rồi mới nối lên (pre-aggregate trong bảng dẫn xuất). Ta sẽ dùng mẫu này ở mục 7.

    SQL Server vs MySQL: truy vấn COUNT(cột), SUM, AVG trên cột có thể NULL (sau LEFT JOIN) đôi khi in thông báo "Warning: Null value is eliminated by an aggregate or other SET operation" trên SQL Server. Đó chỉ là cảnh báo, không phải lỗi; kết quả vẫn đúng. Cách tránh: gom nhóm trong bảng dẫn xuất (chỉ chứa dữ liệu thật) rồi LEFT JOIN + COALESCE.

    3. RIGHT JOIN (RIGHT OUTER JOIN)

    3.1. Khái niệm và ứng dụng

    RIGHT JOIN là ảnh gương của LEFT JOIN: giữ tất cả dòng của bảng phải, bảng trái không khớp thì NULL. Hai câu sau hoàn toàn tương đương:

    SQL
    FROM Trades t  RIGHT JOIN Securities s ON s.symbol = t.symbol
FROM Securities s  LEFT JOIN Trades t   ON s.symbol = t.symbol

    3.2. Khác biệt giữa LEFT và RIGHT

    Chỉ khác thứ tự bảng trong câu lệnh. Trong thực tế, hầu hết người viết SQL chỉ dùng LEFT JOIN (bảng cần giữ đủ dòng đặt đầu tiên, đọc từ trái sang phải cho tự nhiên); RIGHT JOIN hữu ích khi sửa một truy vấn có sẵn mà không muốn đảo cả câu.

    LEFT JOIN RIGHT JOIN
    Bảng được giữ đủ dòng Bảng trước từ khóa JOIN Bảng sau từ khóa JOIN
    Dòng không khớp Cột bảng phải = NULL Cột bảng trái = NULL
    Viết tương đương A LEFT JOIN B B RIGHT JOIN A

    Ví dụ: mỗi mã chứng khoán có bao nhiêu giao dịch từ 2026-09-22, mã nào không có thì hiện 0 (bài 8.2c):

    SQL · chạy thử được
    SELECT s.symbol, COUNT(t.trade_id) AS trade_count, COALESCE(SUM(t.quantity), 0) AS total_qty
FROM Trades t
RIGHT JOIN Securities s ON s.symbol = t.symbol AND t.trade_date >= '2026-09-22'   -- điều kiện ngày ở ON!
GROUP BY s.symbol
ORDER BY COUNT(t.trade_id), s.symbol;

    HPG và PVI có giao dịch trong lịch sử nhưng không có từ ngày 22/09 - nhờ giữ bảng Securities nên vẫn hiện với 0. Điều kiện ngày đặt ở ON (mục 2.3).

    Mẹo: COUNT(t.trade_id) đếm theo cột của bảng phía bị NULL nên bỏ qua dòng không khớp → ra 0. Nếu dùng COUNT(*), mã không có giao dịch vẫn ra 1 (đếm cả dòng NULL được giữ lại). Đây là lỗi rất hay gặp.

    4. FULL JOIN (FULL OUTER JOIN)

    4.1. Khái niệm

    FULL JOIN trả về tất cả dòng của cả hai bảng: khớp thì ghép, không khớp thì phía còn lại là NULL. Nó là hợp của LEFT JOIN và RIGHT JOIN. Ứng dụng tiêu biểu là đối chiếu (reconciliation) giữa hai nguồn dữ liệu: phát hiện dòng chỉ có ở nguồn này hoặc chỉ có ở nguồn kia.

    Bảng tóm tắt các loại JOIN:

    JOIN Giữ dòng khớp Giữ dòng chỉ có ở bảng trái Giữ dòng chỉ có ở bảng phải
    INNER Có Không Không
    LEFT Có Có (phải = NULL) Không
    RIGHT Có Không Có (trái = NULL)
    FULL Có Có Có

    4.2. SQL Server có FULL JOIN, MySQL không

    SQL Server:

    SQL · chạy thử được
    SQL Server
    SELECT i.city AS ca_nhan, n.city AS to_chuc
FROM (SELECT DISTINCT city FROM Customers WHERE customer_type = 'INDIVIDUAL') i
FULL JOIN (SELECT DISTINCT city FROM Customers WHERE customer_type = 'INSTITUTION') n ON n.city = i.city
ORDER BY COALESCE(i.city, n.city);

    MySQL (không hỗ trợ FULL JOIN): workaround bằng UNION của LEFT JOIN và RIGHT JOIN:

    SQL · chạy thử được
    MySQL
    SELECT i.city AS ca_nhan, n.city AS to_chuc FROM (SELECT DISTINCT city FROM Customers WHERE customer_type = 'INDIVIDUAL') i
LEFT JOIN  (SELECT DISTINCT city FROM Customers WHERE customer_type = 'INSTITUTION') n ON n.city = i.city
UNION
SELECT i.city, n.city FROM (SELECT DISTINCT city FROM Customers WHERE customer_type = 'INDIVIDUAL') i
RIGHT JOIN (SELECT DISTINCT city FROM Customers WHERE customer_type = 'INSTITUTION') n ON n.city = i.city
ORDER BY 1;

    UNION (không ALL) tự loại các dòng khớp bị lặp ở hai nửa. Kết quả ở cả hai hệ:

    ca_nhan to_chuc
    Can Tho NULL
    Da Nang NULL
    Ha Noi Ha Noi
    Hai Phong NULL
    TP.HCM TP.HCM

    Thành phố chỉ có khách cá nhân thì cột bên phải NULL. Một biến thể chính xác hơn khi sợ trùng dòng là LEFT JOIN UNION ALL phần "chỉ có bên phải": RIGHT JOIN ... WHERE bảng_trái.id IS NULL (bài 8.2d dùng cách này).

    SQL Server vs MySQL: cùng một yêu cầu "lấy tất cả từ cả hai phía", SQL Server chỉ cần một từ khóa FULL JOIN, còn MySQL phải ghép hai truy vấn bằng UNION. Khi viết bài chấm chạy được trên cả hai hệ, hãy dùng dạng UNION (hoặc LEFT JOIN + UNION ALL phần chỉ-có-bên-phải) để cùng một logic chạy được ở hai nơi.

    4.3. Ví dụ đối chiếu giao dịch với danh mục (bài 8.2d)

    Cuối ngày, vận hành so các cặp (tài khoản, mã) đã giao dịch với danh mục đang giữ (Portfolio). Với tài khoản 1 đến 6, kết quả có dạng (rút gọn):

    account_id symbol trạng thái Ý nghĩa
    2 IDC BOTH có giao dịch và đang giữ
    2 PVS BOTH có giao dịch và đang giữ
    2 SHS TRADED_ONLY có giao dịch nhưng không còn giữ (đã bán hết?)
    1 HPG HELD_ONLY đang giữ nhưng không có giao dịch trong sổ lệnh (số dư đầu kỳ)

    TRADED_ONLY / HELD_ONLY là các dòng cần điều tra. Nếu chỉ dùng INNER hoặc LEFT JOIN ta chỉ thấy một chiều. Trong bài, dùng COALESCE(t.account_id, p.account_id) để lấy khóa từ phía có dữ liệu.

    5. CROSS JOIN

    5.1. Tích Descartes

    CROSS JOIN ghép mỗi dòng của bảng 1 với mỗi dòng của bảng 2, không có ON. Số dòng = n1 x n2. Customers (40) x Securities (12) cho 480 dòng.

    SQL · chạy thử được
    SELECT s.symbol, s.exchange, ci.city
FROM Securities s
CROSS JOIN (SELECT DISTINCT city FROM Customers WHERE customer_id IN (1, 2)) ci
WHERE s.symbol IN ('FPT', 'VCB')
ORDER BY s.symbol, ci.city;

    Kết quả 4 dòng (2 mã x 2 thành phố): mỗi mã ghép với cả Da Nang và Ha Noi.

    5.2. Khi nào sử dụng?

    CROSS JOIN hiếm khi là "kết quả cuối" mà thường là bước dựng khung:

    1. Lưới báo cáo đầy đủ: tạo mọi tổ hợp (thành phố x loại tài khoản), rồi LEFT JOIN số đếm thật để tổ hợp không có dữ liệu hiện 0 thay vì biến mất (bài 8.2e). Cách làm này rất phổ biến trong báo cáo quản trị.
    2. Bảng lịch: ngày x mã chứng khoán để tìm ngày mã không có giá.
    3. Sinh dữ liệu thử, so sánh mọi cặp.

    Ví dụ lưới sàn x chiều lệnh: đếm số lệnh theo từng tổ hợp (HNX/HOSE x BUY/SELL), kể cả tổ hợp không có lệnh vẫn hiện 0:

    SQL · chạy thử được
    SELECT e.exchange, sd.side, COUNT(o.order_id) AS so_lenh
FROM (SELECT DISTINCT exchange FROM Securities) e
CROSS JOIN (SELECT DISTINCT side FROM Orders) sd
LEFT JOIN Securities s ON s.exchange = e.exchange
LEFT JOIN Orders o     ON o.symbol = s.symbol AND o.side = sd.side
GROUP BY e.exchange, sd.side
ORDER BY e.exchange, sd.side;

    Mẫu ba bước: CROSS JOIN dựng khung → LEFT JOIN dữ liệu thật → GROUP BY đếm. Đây chính là cấu trúc của bài 8.2e.

    Cảnh báo: với hai bảng hàng triệu dòng, CROSS JOIN sinh hàng nghìn tỷ dòng và làm treo máy chủ. Luôn kiểm tra kích thước trước (SELECT COUNT(*) hai bảng rồi nhân lại). Quên ON trong INNER JOIN bằng tay ở MySQL cũng vô tình thành CROSS JOIN.

    Vừa học xong LEFT, RIGHT, FULL và CROSS JOIN, hãy làm bài 8.2 để giữ đủ dòng bảng trái, tìm khách chưa giao dịch (anti-join), đếm giao dịch theo mã bằng RIGHT JOIN, đối chiếu bằng FULL JOIN và dựng lưới thành phố x loại tài khoản.

    6. Self Join

    6.1. Kết hợp bảng với chính nó

    Self join là JOIN mà cả hai phía là cùng một bảng, phân biệt bằng hai bí danh khác nhau. Dùng khi một bảng chứa quan hệ giữa các dòng của chính nó. Đây không phải một loại JOIN riêng: bạn có thể dùng INNER hay LEFT trong self join, chỉ cần hai bí danh khác nhau.

    6.2. Tìm khách hàng ở cùng thành phố

    Hai dòng khác nhau, cùng city:

    SQL · chạy thử được
    SELECT c1.city, c1.customer_id AS customer_id_1, c1.full_name AS full_name_1,
       c2.customer_id AS customer_id_2, c2.full_name AS full_name_2
FROM Customers c1
INNER JOIN Customers c2 ON c2.city = c1.city AND c2.customer_id > c1.customer_id
WHERE c1.city = 'Da Nang'
ORDER BY c1.customer_id, c2.customer_id;

    Kết quả có 10 cặp (5 khách ở Da Nang: 2, 6, 15, 21, 27). Điều kiện c2.customer_id > c1.customer_id làm hai việc: loại việc ghép người với chính mình và loại cặp đảo ngược (B, A) khi đã có (A, B). Nếu dùng <> thay vì >, mỗi cặp bị lặp hai lần.

    Điều kiện Số cặp (5 khách) Vấn đề
    Không có điều kiện thứ hai 25 Có cả (A, A) và cả (A, B), (B, A)
    c2.customer_id <> c1.customer_id 20 Mỗi cặp lặp hai lần
    c2.customer_id > c1.customer_id 10 Đúng: mỗi cặp một lần

    6.3. Cây phân cấp: người giới thiệu (referrer)

    Customers.referrer_id trỏ tới customer_id của người giới thiệu. Để hiển thị tên người giới thiệu, nối bảng với chính nó qua referrer_id:

    SQL · chạy thử được
    SELECT c.customer_id, c.full_name, r.full_name AS referrer_name
FROM Customers c
INNER JOIN Customers r ON r.customer_id = c.referrer_id     -- INNER: chỉ khách có người giới thiệu
WHERE c.customer_id IN (8, 27, 29, 33)
ORDER BY c.customer_id;

    Muốn liệt kê cả khách không có người giới thiệu thì đổi sang LEFT JOIN. Muốn đi hai cấp (người giới thiệu của người giới thiệu) thì nối thêm một lần nữa, dùng LEFT JOIN vì không phải ai cũng có cấp trên (bài 8.4b):

    SQL · chạy thử được
    SELECT c.customer_id, c.full_name, r.full_name AS referrer_name, g.full_name AS referrer_of_referrer
FROM Customers c
INNER JOIN Customers r ON r.customer_id = c.referrer_id
LEFT  JOIN Customers g ON g.customer_id = r.referrer_id
WHERE c.customer_id IN (8, 27, 29, 33)
ORDER BY c.customer_id;

    Đọc kết quả: khách 8 (Bui Thi Lan) do Nguyen Van An giới thiệu, người này không có cấp trên (NULL); khách 27 (Vu Van Kien) do Bui Thi Lan giới thiệu, và Bui Thi Lan lại do Nguyen Van An giới thiệu - chuỗi 27 ← 8 ← 1.

    Lưu ý: với cây sâu không giới hạn (cấp bao nhiêu cũng được), self join cố định số cấp không đủ; phải dùng CTE đệ quy (WITH ... UNION ALL), nội dung dành cho khóa nâng cao. Ứng dụng khác của self join: cấu trúc phòng ban - nhân viên - quản lý, so sánh giá ngày hôm nay với hôm qua trong MarketPrices.

    Vừa học xong Self Join, hãy làm bài 8.4 để tìm cặp khách hàng ở cùng thành phố và dựng chuỗi người giới thiệu hai cấp.

    7. Phân tích bằng JOIN

    Các bài phân tích luôn theo mẫu: JOIN để đưa dữ liệu về cùng một chỗ → GROUP BY để tổng hợp → ORDER BY/TOP để chọn.

    7.1. Tổng giá trị giao dịch cho từng khách hàng (bài 8.3a)

    Giá trị một giao dịch = quantity * price. Mẫu chuẩn: tổng hợp theo khách ở bảng dẫn xuất (chỉ dữ liệu thật) rồi LEFT JOIN từ Customers để giữ cả khách chưa giao dịch:

    SQL · chạy thử được
    SELECT c.customer_id, c.full_name,
       COALESCE(x.n, 0) AS trade_count,
       CAST(ROUND(COALESCE(x.v, 0), 2) AS DECIMAL(20,2)) AS total_value
FROM Customers c
LEFT JOIN (SELECT a.customer_id, COUNT(*) AS n, SUM(t.quantity * t.price) AS v
           FROM Accounts a
           INNER JOIN Orders o ON o.account_id = a.account_id
           INNER JOIN Trades t ON t.order_id   = o.order_id
           GROUP BY a.customer_id) x ON x.customer_id = c.customer_id
WHERE c.customer_type = 'INSTITUTION'
ORDER BY total_value DESC, c.customer_id;

    Đọc kết quả: quỹ Tang Truong Xanh (36) đứng đầu với 4 giao dịch, 422.600.000; hai khách 34 và 40 chưa giao dịch nào nên hiện 0 và 0.00 nhờ LEFT JOIN + COALESCE.

    Lưu ý: CAST(ROUND(..., 2) AS DECIMAL(20,2)) giúp cả hai hệ in đúng hai chữ số thập phân; T-SQL chia/trung bình số nguyên khác MySQL nên luôn ép kiểu rõ ràng khi cần kết quả giống nhau.

    Ví dụ nối nhiều bảng rồi gom nhóm theo một thuộc tính khác (sàn giao dịch), kèm điều kiện lọc trên kết quả tổng hợp bằng HAVING:

    SQL · chạy thử được
    SELECT s.exchange, s.sector, COUNT(*) AS trade_count, SUM(t.quantity * t.price) AS total_value
FROM Trades t
INNER JOIN Securities s ON s.symbol = t.symbol
GROUP BY s.exchange, s.sector
HAVING COUNT(*) >= 5
ORDER BY total_value DESC, s.exchange, s.sector;

    7.2. Sản phẩm bán chạy nhất (bài 8.3b)

    "Bán chạy" ở đây tính theo tổng khối lượng khớp của lệnh BÁN. Cần chiều lệnh (Orders.side) và tên công ty (Securities):

    SQL Server

    SQL · chạy thử được
    SQL Server
    SELECT TOP 1 s.symbol, s.company_name, SUM(t.quantity) AS sold_qty
FROM Trades t
JOIN Orders o     ON o.order_id = t.order_id
JOIN Securities s ON s.symbol   = t.symbol
WHERE o.side = 'SELL'
GROUP BY s.symbol, s.company_name
ORDER BY SUM(t.quantity) DESC, s.symbol;

    MySQL: giữ nguyên câu trên nhưng bỏ TOP 1 và thêm LIMIT 1 ở cuối.

    SQL · chạy thử được
    MySQL
    SELECT s.symbol, s.company_name, SUM(t.quantity) AS sold_qty
FROM Trades t
JOIN Orders o     ON o.order_id = t.order_id
JOIN Securities s ON s.symbol   = t.symbol
WHERE o.side = 'SELL'
GROUP BY s.symbol, s.company_name
ORDER BY SUM(t.quantity) DESC, s.symbol
LIMIT 1;

    Kết quả: PVS - Tong Cong ty Dich vu Ky thuat Dau khi - 9500.

    Cảnh báo: luôn có khóa sắp xếp phụ (s.symbol) để khi hai mã đồng hạng kết quả vẫn xác định. Thiếu nó, hai hệ (hoặc hai lần chạy) có thể chọn hai mã khác nhau.

    7.3. Trung bình giá trị giao dịch của khách hàng (bài 8.3c)

    Cùng khuôn mẫu, thay SUM bằng AVG; khách chưa giao dịch có AVG = NULL nên dùng COALESCE(..., 0). Muốn so với trung bình toàn sàn, nhúng thêm truy vấn con (SELECT AVG(quantity * price) FROM Trades). Ví dụ đầy đủ cho ba khách 1, 2, 6:

    SQL · chạy thử được
    SELECT c.customer_id,
       CAST(ROUND(COALESCE(x.av, 0), 2) AS DECIMAL(20,2)) AS avg_value,
       CASE WHEN x.av > (SELECT AVG(quantity * price) FROM Trades) THEN 'ABOVE' ELSE 'BELOW' END AS vs_market
FROM Customers c
LEFT JOIN (SELECT a.customer_id, AVG(t.quantity * t.price) AS av
           FROM Accounts a
           INNER JOIN Orders o ON o.account_id = a.account_id
           INNER JOIN Trades t ON t.order_id   = o.order_id
           GROUP BY a.customer_id) x ON x.customer_id = c.customer_id
WHERE c.customer_id IN (1, 2, 6)
ORDER BY c.customer_id;

    Đọc kết quả: khách 6 trung bình 50083333.33 → ABOVE; khách 2 12950000.00 → BELOW; khách 1 chưa giao dịch nên 0.00 → BELOW (so sánh NULL > số cho UNKNOWN, rơi vào nhánh ELSE).

    Ví dụ nghiệp vụ: ban quản lý muốn phân khúc khách hàng theo "quy mô giao dịch trung bình" để chăm sóc khác nhau: khách ABOVE được phân cho chuyên viên cao cấp. Cột vs_market chính là nhãn phân khúc đơn giản nhất.

    Sai lầm thường gặp: (1) AVG trên cột đã nhân đôi dòng do JOIN thừa bảng (mục 2.4); (2) quên rằng AVG bỏ qua NULL nên khách chưa giao dịch bị loại chứ không phải bằng 0; (3) so sánh chuỗi trung bình đã làm tròn thay vì giá trị gốc; (4) quên GROUP BY đủ các cột không nằm trong hàm tổng hợp.

    Vừa học xong cách tổng hợp bằng JOIN + GROUP BY, hãy làm bài 8.3 để tính tổng giá trị giao dịch từng khách, tìm mã bán chạy nhất và so sánh trung bình giá trị giao dịch với thị trường.

    Sai lầm thường gặp

    Sai lầm Hậu quả Cách đúng
    Quên ON hoặc viết kiểu nối cũ FROM a, b WHERE ... thiếu điều kiện Tích Descartes: hàng nghìn dòng vô nghĩa Luôn dùng JOIN ... ON
    Đặt điều kiện lọc bảng phải của LEFT JOIN trong WHERE LEFT JOIN "biến" thành INNER JOIN, mất khách chưa có dữ liệu Đặt điều kiện bảng phải trong ON
    Nối thiếu một vế của khóa kép Mỗi dòng ghép với nhiều dòng, số dòng phình ra Nối đủ mọi cột của khóa
    Cộng/đếm sau khi nối bảng "n" thứ hai (fan-out) Tổng bị nhân lên, số liệu sai mà không báo lỗi Tổng hợp ở bảng dẫn xuất trước, rồi mới nối
    Dùng COUNT(*) sau LEFT/RIGHT JOIN để đếm "số giao dịch" Khách/mã không có giao dịch vẫn ra 1 COUNT(cột_bảng_phải)
    Tìm "chưa có" bằng NOT IN khi cột có NULL Kết quả rỗng/thiếu dòng LEFT JOIN ... IS NULL hoặc NOT EXISTS
    Self join dùng <> thay vì > để bỏ cặp lặp Mỗi cặp xuất hiện hai lần c2.id > c1.id
    FULL JOIN trên MySQL Lỗi cú pháp LEFT JOIN UNION RIGHT JOIN
    Sắp xếp Top-N không có khóa phụ Kết quả không tất định khi đồng hạng Thêm khóa phụ (symbol, id)

    Tóm tắt

    Chủ đề Điểm cần nhớ
    Tổng quan Các loại JOIN khác nhau ở cách xử lý dòng không khớp (hình Venn)
    INNER JOIN Chỉ giữ dòng khớp cả hai bảng; nhiều bảng: nối lần lượt theo chuỗi khóa ngoài; khóa kép nối đủ cột
    LEFT JOIN Giữ mọi dòng bảng trái; không khớp → NULL; điều kiện bảng phải đặt ở ON
    Anti-join LEFT JOIN ... WHERE bảng_phải.khóa IS NULL = "chưa có ..."
    RIGHT JOIN Ảnh gương của LEFT JOIN (đổi thứ tự bảng); COUNT(cột) không COUNT(*)
    FULL JOIN T-SQL: FULL JOIN; MySQL: LEFT JOIN UNION RIGHT JOIN (hoặc UNION ALL + IS NULL)
    CROSS JOIN Tích Descartes n1 x n2; dựng lưới báo cáo rồi LEFT JOIN số đếm
    Self Join Hai bí danh của cùng một bảng; c2.id > c1.id để bỏ cặp lặp; cây phân cấp qua referrer_id
    Phân tích Tổng hợp trước (bảng dẫn xuất) rồi nối; COALESCE; sắp xếp có khóa phụ
    Hiệu năng Index cho cột ON/FK; không bọc hàm quanh cột lọc; xem kế hoạch thực thi

    Bài tập thực hành của buổi

    Nội dung không chấm tự động được: kế hoạch thực thi và hiệu năng Index (xem mục 1.4, tự thử CREATE INDEX và so sánh Execution Plan / EXPLAIN trên máy của bạn).

    Gợi ý mở rộng

    • Đếm số khách hàng được mỗi người giới thiệu (GROUP BY r.customer_id) và tìm "người giới thiệu xuất sắc nhất".
    • Viết lại các truy vấn NOT EXISTS ở Buổi 7 thành LEFT JOIN ... IS NULL và so sánh kế hoạch thực thi.
    • Tìm hiểu CROSS APPLY / OUTER APPLY (SQL Server) và LATERAL (MySQL 8) - "JOIN với truy vấn con phụ thuộc dòng".
    • Chuẩn bị Buổi 9: các thao tác tính tổng giá trị giao dịch như 8.3a sẽ được tự động cập nhật vào CustomerStats bằng trigger.