SQL cho Tester: tìm đơn hàng bị thiếu hoặc trùng bằng JOIN và GROUP BY

Bài toán đối soát đơn hàng là gì?

Khi test một hệ thống thương mại điện tử hoặc thanh toán, bạn thường gặp yêu cầu: "Kiểm tra xem có đơn hàng nào chưa có bản ghi thanh toán không?" hoặc "Có đơn hàng nào bị ghi nhận thanh toán hai lần không?". Đây là bài toán đối soát dữ liệu (data reconciliation) - một kỹ năng SQL thực tế mà Tester cần nắm.

Bài này tập trung vào hai tình huống cụ thể:

• Đơn hàng bị thiếu (missing): có trong bảng orders nhưng không có bản ghi tương ứng trong bảng payments.
• Đơn hàng bị trùng (duplicate): cùng một order_id xuất hiện nhiều hơn một lần trong bảng payments.

Tất cả schema và dữ liệu trong bài là giả định minh họa, không lấy từ hệ thống thật.

Hai bảng, một kính lúp - đủ để phát hiện lỗi dữ liệu phổ biến

Công cụ cần có: bất kỳ SQL client nào hỗ trợ standard SQL - MySQL Workbench, DBeaver, pgAdmin, hoặc SQLite online đều được. Query trong bài tuân theo cú pháp SQL chuẩn (ISO/IEC 9075), không dùng stored procedure hay câu lệnh sửa đổi dữ liệu (INSERT/UPDATE/DELETE).

Schema và dữ liệu mẫu

Dưới đây là schema giả định dùng cho toàn bộ bài. Bạn có thể tạo các bảng này trong bất kỳ môi trường SQL nào để chạy thử.

-- Bảng đơn hàng (giả định)
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(10,2),
    order_date  DATE
);

-- Bảng thanh toán (giả định)
CREATE TABLE payments (
    payment_id  INT PRIMARY KEY,
    order_id    INT,
    paid_amount DECIMAL(10,2),
    paid_date   DATE
);

Dữ liệu mẫu (giả định - dùng để minh họa):

INSERT INTO orders VALUES
(1001, 1, 250000, '2024-06-01'),
(1002, 2, 180000, '2024-06-01'),
(1003, 3, 320000, '2024-06-02'),
(1004, 4, 150000, '2024-06-02'),
(1005, 5, 400000, '2024-06-03');

INSERT INTO payments VALUES
(1, 1001, 250000, '2024-06-01'),
(2, 1003, 320000, '2024-06-02'),
(3, 1003, 320000, '2024-06-02'), -- trùng order_id 1003
(4, 1005, 400000, '2024-06-03');

Phân tích nhanh dữ liệu mẫu trên:

• order_id 1002 và 1004: có trong orders, không có trong payments → bị thiếu.
• order_id 1003: xuất hiện 2 lần trong payments → bị trùng.
• order_id 1001 và 1005: bình thường (1 đơn - 1 thanh toán).

Dữ liệu mẫu nhỏ nhưng đủ để test cả hai loại lỗi: thiếu và trùng

Ghi chú: trong thực tế, bảng payments thường có thêm cột status (pending/completed/failed). Bài này bỏ qua để tập trung vào logic JOIN và GROUP BY.

Tìm đơn hàng bị thiếu bằng LEFT JOIN

Tư duy: nếu một order_id có trong bảng orders nhưng không có bản ghi nào trong payments, thì khi LEFT JOIN hai bảng, phía payments sẽ trả về NULL.

Đây là câu truy vấn (chạy trên dữ liệu mẫu giả định ở trên):

-- Tìm đơn hàng chưa có thanh toán (giả định)
SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    o.order_date
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL;

Kết quả mong đợi khi chạy trên dữ liệu mẫu:

Tiêu chí pass/fail:

• Pass: query trả về đúng 2 dòng, với order_id là 1002 và 1004.
• Fail: trả về số dòng khác, hoặc xuất hiện order_id 1001/1003/1005.

LEFT JOIN giữ lại tất cả dòng bên trái; NULL bên phải = chưa có thanh toán

Giới hạn cần biết: nếu một đơn hàng có bản ghi trong payments nhưng status = 'failed', query này vẫn coi là đã có thanh toán. Trong thực tế, bạn cần thêm điều kiện lọc status, ví dụ WHERE p.payment_id IS NULL OR p.status != 'completed'. Bài này dùng schema đơn giản nên bỏ qua cột status.

Tìm đơn hàng bị trùng bằng GROUP BY và HAVING

Đơn hàng bị trùng là khi cùng một order_id xuất hiện nhiều hơn một lần trong bảng payments. GROUP BY + HAVING là công cụ chuẩn để phát hiện điều này.

-- Tìm đơn hàng bị ghi thanh toán nhiều lần (giả định)
SELECT
    order_id,
    COUNT(*) AS so_lan_thanh_toan
FROM payments
GROUP BY order_id
HAVING COUNT(*) > 1;

Kết quả mong đợi khi chạy trên dữ liệu mẫu:

Tiêu chí pass/fail:

• Pass: query trả về đúng 1 dòng, order_id = 1003, so_lan_thanh_toan = 2.
• Fail: không trả về dòng nào, hoặc trả về order_id khác.

GROUP BY gom nhóm, HAVING lọc nhóm - bộ đôi phát hiện trùng lặp

Muốn xem chi tiết hơn (tổng số tiền thanh toán trùng so với số tiền đơn hàng), có thể JOIN thêm:

-- Xem chi tiết đơn trùng kèm tổng tiền (giả định)
SELECT
    p.order_id,
    COUNT(*)          AS so_lan_thanh_toan,
    SUM(p.paid_amount) AS tong_tien_da_thanh_toan,
    o.amount          AS tien_don_hang
FROM payments p
JOIN orders o ON p.order_id = o.order_id
GROUP BY p.order_id, o.amount
HAVING COUNT(*) > 1;

Kết quả mong đợi của query mở rộng:

Dòng này cho thấy order_id 1003 đã bị thu tiền gấp đôi so với giá trị đơn hàng - một lỗi nghiêm trọng cần báo cáo ngay.

Giới hạn: HAVING COUNT(*) > 1 chỉ phát hiện trùng theo order_id. Trong thực tế có thể cần kiểm tra thêm tổ hợp (order_id, paid_amount) nếu hệ thống cho phép thanh toán từng phần.

Kết hợp cả hai: báo cáo tổng hợp một lần query

Trong thực tế, bạn có thể muốn chạy một báo cáo duy nhất liệt kê cả đơn thiếu lẫn đơn trùng. Cách làm là dùng CTE (Common Table Expression) hoặc subquery để gộp hai kết quả lại.

CTE là gì? CTE (Common Table Expression) là cú pháp WITH tên_cte AS (...) cho phép đặt tên cho một kết quả trung gian rồi dùng lại trong cùng câu query. Tương đương với viết subquery nhưng dễ đọc hơn. Được hỗ trợ từ MySQL 8.0+, PostgreSQL 8.4+, SQLite 3.35+.

-- Báo cáo tổng hợp: thiếu + trùng (giả định)
WITH missing_payments AS (
    SELECT
        o.order_id,
        'THIEU_THANH_TOAN' AS loai_loi,
        o.amount           AS gia_tri
    FROM orders o
    LEFT JOIN payments p ON o.order_id = p.order_id
    WHERE p.payment_id IS NULL
),
duplicate_payments AS (
    SELECT
        p.order_id,
        'TRUNG_THANH_TOAN' AS loai_loi,
        SUM(p.paid_amount) AS gia_tri
    FROM payments p
    GROUP BY p.order_id
    HAVING COUNT(*) > 1
)
SELECT * FROM missing_payments
UNION ALL
SELECT * FROM duplicate_payments
ORDER BY order_id;

Kết quả mong đợi (giả định, dựa trên dữ liệu mẫu):

Tiêu chí pass/fail:

• Pass: 3 dòng, đúng order_id, đúng loai_loi, gia_tri khớp.
• Fail: thiếu dòng, sai loai_loi, hoặc gia_tri tính sai.

Một query, hai loại lỗi - UNION ALL gộp kết quả từ hai CTE

Nếu muốn tải file SQL mẫu để chạy thử ngay: Tải file SQL mẫu

Lỗi thường gặp khi viết query đối soát

Ba lỗi phổ biến nhất khi mới bắt đầu:

1. Dùng INNER JOIN thay vì LEFT JOIN khi tìm đơn thiếu

INNER JOIN chỉ trả về các dòng có match ở cả hai bảng. Nếu dùng INNER JOIN, đơn hàng chưa có thanh toán sẽ bị bỏ qua hoàn toàn - đây là lỗi logic, không phải lỗi cú pháp, nên SQL sẽ không báo lỗi.

-- SAI: INNER JOIN bỏ mất đơn chưa có thanh toán
SELECT o.order_id
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL; -- Luôn trả về 0 dòng!

2. Dùng WHERE thay vì HAVING để lọc sau GROUP BY

WHERE chạy trước khi GROUP BY, nên không thể lọc theo kết quả của hàm tổng hợp như COUNT(). HAVING chạy sau GROUP BY.

-- SAI: WHERE không lọc được COUNT(*)
SELECT order_id, COUNT(*) AS cnt
FROM payments
WHERE COUNT(*) > 1   -- Lỗi cú pháp!
GROUP BY order_id;

3. Quên điều kiện JOIN - ra Cartesian Product

Nếu viết FROM orders, payments mà không có ON hoặc WHERE kết nối, kết quả sẽ là tích Cartesian (số dòng = orders × payments). Với bảng lớn, truy vấn này có thể làm treo database.

Ba lỗi logic phổ biến - lỗi cú pháp dễ phát hiện, lỗi logic thì không

Cách tự kiểm tra sau khi viết query:

1. Chạy query trên dữ liệu mẫu nhỏ (như dữ liệu giả định trong bài) trước khi áp lên dataset thật.
2. Đếm số dòng kết quả và đối chiếu với kết quả mong đợi đã xác định trước.
3. Thêm EXPLAIN hoặc EXPLAIN ANALYZE (tùy database) trước SELECT để kiểm tra kế hoạch thực thi.