JOIN cho phép ghép dữ liệu thành bức tranh hoàn chỉnh, đồng thời có thể âm thầm nhân số dòng nếu quan hệ giữa các bảng không được hiểu đúng. Bài viết xây dataset chi tiết bán hàng từ customers, orders, order_items, products và stores, kèm kiểm tra cardinality sau từng phép nối.

Mục lục

1. Bài toán thực tế
2. Sản phẩm hoàn thành
3. Kiến thức cần dùng
4. Chuẩn bị môi trường
5. Dữ liệu thực hành
6. Xây dựng từng bước
7. Chương trình hoàn chỉnh
8. Kiểm tra kết quả
9. Lỗi thường gặp
10. Đưa vào thực tế
11. Bài tập thực hành
12. Project mở rộng
13. Tổng kết
14. Tài liệu tham khảo

1. Bài toán thực tế

Nhóm phân tích cần một bảng chi tiết gồm tên khách hàng, cửa hàng, sản phẩm, số lượng và doanh thu. Năm mảnh thông tin nằm ở năm bảng khác nhau. Một truy vấn JOIN chạy được chưa đủ bảo đảm số liệu đúng.

Rủi ro lớn nhất là fan-out. Nếu products vô tình có hai dòng cho cùng product_id, mỗi dòng order_items sẽ khớp hai lần và doanh thu tăng gấp đôi. Báo cáo vẫn trông hợp lý nếu không đối chiếu.

2. Sản phẩm hoàn thành

Sản phẩm là view sales_detail ở grain một dòng mỗi order_id + product_id. View giữ cả khóa và thuộc tính mô tả, đồng thời có cột net_revenue.

Bộ kiểm tra đi kèm đo tỷ lệ khóa không khớp, tính duy nhất của dimension và tổng doanh thu trước, sau JOIN.

• Phân biệt INNER, LEFT, RIGHT và FULL JOIN.
• Xác định quan hệ một-một, một-nhiều và nhiều-nhiều.
• Kiểm tra uniqueness trước khi JOIN.
• Giữ bản ghi nguồn bằng LEFT JOIN và đo unmatched rate.
• Ngăn fan-out bằng khóa và constraint.

3. Kiến thức cần dùng

INNER JOIN chỉ giữ dòng có khóa khớp ở cả hai phía. LEFT JOIN giữ toàn bộ phía trái và điền NULL cho phía phải khi không khớp. Chọn loại JOIN dựa trên ý nghĩa nghiệp vụ, không dựa trên thói quen.

Cardinality mô tả số dòng có thể khớp. customers đến orders là một-nhiều; orders đến order_items là một-nhiều; products đến order_items là một-nhiều nhìn từ products. JOIN hai bảng nhiều-nhiều qua khóa không hoàn chỉnh tạo số dòng ngoài dự kiến.

Khóa surrogate như product_id ổn định hơn tên sản phẩm. JOIN bằng chuỗi dễ sai do khoảng trắng, viết hoa hoặc đổi tên.

4. Chuẩn bị môi trường

Tạo constraint primary key ở các dimension và composite key ở order_items. Nếu dữ liệu staging chưa sạch, chạy truy vấn duplicate trước khi thêm constraint.

Chọn 10 order_id làm mẫu kiểm tra. Ghi lại số dòng order_items và doanh thu của mẫu trước khi JOIN.

SELECT order_id, COUNT(*) AS item_lines,
       SUM(quantity*unit_price-discount_amount) AS revenue
FROM order_items
WHERE order_id BETWEEN 1001 AND 1010
GROUP BY order_id
ORDER BY order_id;

5. Dữ liệu thực hành

customers, products và stores là bảng mô tả, mỗi khóa dự kiến duy nhất. orders chứa khóa đến customer và store. order_items chứa order_id và product_id, vì vậy grain chi tiết là tổ hợp hai khóa.

Bản ghi mồ côi có thể xuất hiện trong staging trước khi constraint được áp dụng. LEFT JOIN giúp tìm chúng thay vì làm biến mất như INNER JOIN.

ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
ALTER TABLE products ADD CONSTRAINT products_pk PRIMARY KEY (product_id);
ALTER TABLE stores ADD CONSTRAINT stores_pk PRIMARY KEY (store_id);
ALTER TABLE order_items ADD CONSTRAINT order_items_pk PRIMARY KEY (order_id, product_id);

6. Xây dựng từng bước

Bước 1: Kiểm tra khóa phía dimension

Mỗi dimension phải trả về không dòng trong truy vấn HAVING. Nếu có duplicate, cần chọn quy tắc hợp nhất trước khi JOIN.

SELECT product_id, COUNT(*) AS duplicate_count
FROM products
GROUP BY product_id
HAVING COUNT(*) > 1;

SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

Kết quả mong đợi: Không có product_id hoặc customer_id trùng.

Bước 2: INNER JOIN đơn hàng với chi tiết

Một đơn không có item thường là dữ liệu lỗi. INNER JOIN phù hợp khi dataset phân tích chỉ cần giao dịch có dòng sản phẩm.

SELECT o.order_id, o.order_date, oi.product_id,
       oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';

Kết quả mong đợi: Một đơn có nhiều sản phẩm xuất hiện nhiều dòng.

Bước 3: Thêm dimension bằng khóa ổn định

Nối từng bảng một và đếm dòng sau mỗi bước. Không viết toàn bộ năm JOIN rồi mới kiểm tra cuối.

SELECT o.order_id, c.customer_name, s.store_name,
       p.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id=o.order_id
JOIN customers c ON c.customer_id=o.customer_id
JOIN stores s ON s.store_id=o.store_id
JOIN products p ON p.product_id=oi.product_id
WHERE o.status='completed';

Kết quả mong đợi: Dòng giao dịch có đầy đủ thuộc tính mô tả.

Bước 4: Tìm khóa không khớp bằng LEFT JOIN

Đặt điều kiện p.product_id IS NULL để tìm item tham chiếu sản phẩm không tồn tại.

SELECT oi.order_id, oi.product_id
FROM order_items oi
LEFT JOIN products p ON p.product_id=oi.product_id
WHERE p.product_id IS NULL;

Kết quả mong đợi: Danh sách orphan product; kết quả tốt là 0 dòng.

Bước 5: Không biến LEFT JOIN thành INNER JOIN

Điều kiện trên bảng phải được đặt trong ON nếu vẫn muốn giữ dòng không khớp. Đặt ở WHERE sẽ loại NULL.

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id=c.customer_id
 AND o.status='completed';

Kết quả mong đợi: Giữ cả khách chưa có đơn completed.

Bước 6: So sánh EXISTS với JOIN

Khi chỉ cần biết khách đã từng mua, EXISTS tránh nhân khách theo số đơn.

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id=c.customer_id
    AND o.status='completed'
);

Kết quả mong đợi: Mỗi khách xuất hiện tối đa một lần.

Vẽ quan hệ trước khi JOIN

Trên giấy, viết customers 1—N orders, orders 1—N order_items, products 1—N order_items. Sau đó ghi grain mong muốn ở cuối. Sơ đồ nhỏ này cho biết thuộc tính nào sẽ lặp và phép tổng hợp nào cần distinct.

Nếu thêm promotion và một đơn có nhiều promotion, quan hệ orders—promotions trở thành nhiều-nhiều qua bridge. JOIN đồng thời order_items và order_promotions sẽ nhân số sản phẩm với số promotion. Phải tổng hợp hoặc phân bổ từng nhánh trước khi ghép.

Đặt kỳ vọng số dòng

Trước INNER JOIN orders với order_items, số dòng kết quả tối đa không vượt số item nếu order_id ở orders duy nhất. Trước LEFT JOIN fact với dimension duy nhất, số dòng phải giữ nguyên. Viết kỳ vọng thành quality check thay vì chỉ quan sát.

Khi số dòng tăng ngoài kỳ vọng, kiểm tra duplicate key ở phía được nối trước khi dùng DISTINCT. DISTINCT có thể che fan-out nhưng không sửa doanh thu đã bị nhân và còn làm truy vấn tốn tài nguyên.

JOIN lịch sử thuộc tính

Tên cửa hàng hiện tại có thể đủ cho báo cáo vận hành, nhưng khu vực hoặc phân hạng cửa hàng tại thời điểm bán cần lịch sử dimension. Khi đó JOIN phải dùng store business key và điều kiện order_date nằm trong effective_from, effective_to.

Nếu hai phiên bản dimension cùng hiệu lực ở một thời điểm, fact sẽ khớp hai dòng. Constraint hoặc test khoảng thời gian chồng lấn là bắt buộc cho slowly changing dimension.

Ma trận JOIN giúp chọn loại nối

Viết bốn câu hỏi: giao dịch đã có customer, tất cả customer kể cả chưa mua, tìm customer không mua và tìm khóa lỗi hai phía. Chúng lần lượt dẫn đến INNER JOIN, LEFT JOIN, LEFT JOIN kết hợp IS NULL và FULL JOIN dùng cho đối soát.

Không có loại JOIN tốt nhất cho mọi bài toán. Việc giải thích dòng nào được giữ khi không khớp mới là tiêu chí lựa chọn.

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

SELECT COALESCE(a.key,b.key) business_key,a.value left_value,b.value right_value
FROM source_a a FULL JOIN source_b b ON b.key=a.key
WHERE a.key IS NULL OR b.key IS NULL;

Đo fan-out bằng hệ số

Hệ số fan-out bằng số dòng sau JOIN chia số dòng fact trước JOIN. Với fact order_items nối dimension unique, hệ số phải bằng 1 nếu mọi khóa khớp. Hệ số lớn hơn 1 báo duplicate dimension; nhỏ hơn 1 ở INNER JOIN báo orphan.

Lưu kiểm tra này trong pipeline giúp phát hiện dimension hỏng trước khi dashboard nhận số liệu. DISTINCT không được dùng để ép hệ số về 1 nếu chưa hiểu nguyên nhân.

WITH before_join AS (SELECT COUNT(*) n FROM order_items),
after_join AS (
 SELECT COUNT(*) n FROM order_items oi JOIN products p ON p.product_id=oi.product_id
)
SELECT a.n before_rows,b.n after_rows,
       b.n::numeric/NULLIF(a.n,0) fanout_factor
FROM before_join a CROSS JOIN after_join b;

Semi join và anti join bằng EXISTS

EXISTS chỉ hỏi có ít nhất một dòng khớp, vì vậy phù hợp khi không cần cột từ bảng con. Nó tránh việc khách xuất hiện nhiều lần theo số đơn. NOT EXISTS tìm phần không có quan hệ và xử lý NULL rõ hơn NOT IN.

Planner có thể chuyển EXISTS thành semi join hiệu quả. Quan trọng hơn, cú pháp truyền đạt đúng ý “có tồn tại” thay vì ghép dữ liệu.

SELECT c.customer_id,c.customer_name
FROM customers c
WHERE EXISTS(SELECT 1 FROM orders o WHERE o.customer_id=c.customer_id AND o.status='completed');

SELECT p.product_id,p.product_name
FROM products p
WHERE NOT EXISTS(SELECT 1 FROM order_items oi WHERE oi.product_id=p.product_id);

JOIN nhiều-nhiều và phân bổ

Nếu một đơn có hai promotion và ba item, nối thẳng hai bảng con tạo sáu dòng. Để phân tích promotion, quyết định discount được phân bổ đều, theo doanh thu item hoặc giữ ở cấp order. Sau đó tổng hợp bridge về grain tương thích.

Một thiết kế rõ thường tạo CTE item_totals một dòng mỗi order và promotion_totals một dòng mỗi order trước khi nối. Không có kỹ thuật SQL nào thay thế quyết định phân bổ nghiệp vụ.

WITH item_totals AS (
 SELECT order_id,SUM(quantity*unit_price) gross
 FROM order_items GROUP BY order_id
), promotion_counts AS (
 SELECT order_id,COUNT(*) promo_count
 FROM order_promotions GROUP BY order_id
)
SELECT i.order_id,i.gross,COALESCE(p.promo_count,0) promo_count
FROM item_totals i LEFT JOIN promotion_counts p ON p.order_id=i.order_id;

7. Chương trình hoàn chỉnh

View sales_detail giữ khóa ở đầu danh sách cột để hỗ trợ truy vết. Doanh thu tính từ order_items, không lấy total_amount lặp lại từ orders.

Nếu total_amount cần hiển thị, không cộng nó trên dataset cấp item. Muốn đối chiếu, tổng hợp item về order_id trước rồi JOIN với orders.

CREATE OR REPLACE VIEW sales_detail AS
SELECT o.order_id, oi.product_id, o.order_date,
       o.customer_id, c.customer_name,
       o.store_id, s.store_name,
       p.product_name, p.category,
       oi.quantity, oi.unit_price, oi.discount_amount,
       oi.quantity*oi.unit_price-oi.discount_amount AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id=o.order_id
JOIN customers c ON c.customer_id=o.customer_id
JOIN stores s ON s.store_id=o.store_id
JOIN products p ON p.product_id=oi.product_id
WHERE o.status='completed';

8. Kiểm tra kết quả

Số dòng view phải bằng số dòng order_items thuộc đơn completed nếu mọi khóa dimension đều khớp. Tổng net_revenue phải bằng tổng trực tiếp từ order_items với cùng điều kiện status.

Đếm distinct order_id trong view và so với orders completed có ít nhất một item. Tỷ lệ orphan phải bằng 0 trước khi công bố báo cáo.

SELECT
 (SELECT COUNT(*) FROM sales_detail) AS joined_rows,
 (SELECT COUNT(*) FROM order_items oi JOIN orders o ON o.order_id=oi.order_id WHERE o.status='completed') AS source_rows;

SELECT
 (SELECT SUM(net_revenue) FROM sales_detail) AS joined_revenue,
 (SELECT SUM(oi.quantity*oi.unit_price-oi.discount_amount)
  FROM order_items oi JOIN orders o ON o.order_id=oi.order_id
  WHERE o.status='completed') AS source_revenue;

9. Lỗi thường gặp

• JOIN thiếu ON: Tạo Cartesian product và số dòng tăng cực lớn.
• JOIN bằng tên: Tên không ổn định và có thể trùng.
• Dimension có duplicate key: Mỗi fact bị nhân theo số bản ghi dimension.
• Cộng total_amount sau JOIN item: Tổng đơn bị lặp theo số sản phẩm.
• Lọc bảng phải ở WHERE sau LEFT JOIN: NULL bị loại và kết quả trở thành INNER JOIN.

10. Đưa vào thực tế

Warehouse thường dùng surrogate key và lưu phiên bản dimension để theo dõi thuộc tính thay đổi. Khi JOIN fact với slowly changing dimension, cần thêm điều kiện hiệu lực thời gian, không chỉ nối business key.

Theo dõi unmatched rate như một quality metric. Tỷ lệ tăng đột ngột có thể báo hiệu pipeline dimension chạy trễ hoặc nguồn phát sinh mã mới chưa được nạp.

11. Bài tập thực hành

1. Tìm khách chưa từng có đơn bằng LEFT JOIN.
2. Viết lại bằng NOT EXISTS và so sánh kết quả.
3. Tìm sản phẩm chưa từng bán.
4. Tạo bảng bridge cho sản phẩm có nhiều tag.
5. Chứng minh tổng doanh thu không đổi sau từng JOIN.

12. Project mở rộng

Mở rộng view với sales_channel và promotion. Trước khi JOIN promotion nhiều-nhiều, tạo bridge ở grain order_id + promotion_id và quyết định cách phân bổ discount để không nhân doanh thu.

13. Tổng kết

JOIN đúng bắt đầu từ hiểu cardinality, không bắt đầu từ việc chọn INNER hay LEFT. Mỗi phép nối cần kỳ vọng về số dòng và số kiểm soát.

Dataset sales_detail sẽ được dùng ở bài CASE WHEN để phân nhóm khách hàng, nhưng KPI khách phải được tổng hợp về customer_id trước khi áp dụng quy tắc.

14. Tài liệu tham khảo

• Joined Tables
• Constraints
• EXISTS

TechData.AI - Leading the Future.
Tham khảo các khoá học theo link: https://techdata.ai/techdata-ai-course/
Hoàng Minh.