Subquery và CTE (WITH) trong SQL: chia nhỏ truy vấn khó thành từng bước (Bài 4)
Trong bài này
- Subquery là gì?
- Subquery trả về một giá trị: so với trung bình
- Subquery với IN: ai đã mua hàng Âm thanh?
- Subquery tương quan và NOT EXISTS
- Bẫy NOT IN gặp NULL
- Subquery trong FROM: tính trước, gộp sau
- CTE với WITH: đặt tên cho từng bước
- Dùng một CTE nhiều lần
- Nâng cao: CTE đệ quy để không bỏ sót tháng trống
- Subquery hay CTE, chọn cái nào?
- Những lỗi hay gặp
- Bài tập
- Bài tiếp theo
Series · Bài 4/5 SQL từ cơ bản đến nâng cao
- SQL là gì? Truy vấn đầu tiên với SELECT và WHERE
- ORDER BY, LIMIT, hàm gộp COUNT, SUM, AVG và GROUP BY, HAVING
- JOIN trong SQL: nối nhiều bảng với INNER JOIN và LEFT JOIN
- Subquery và CTE (WITH): chia nhỏ truy vấn khó
- Window function (ROW_NUMBER, RANK, SUM OVER) và bài tập tổng hợp
Có những câu hỏi nghe đơn giản nhưng một lần SELECT không trả lời được. "Sản phẩm nào đắt hơn giá trung bình?" cần biết giá trung bình trước, rồi mới lọc. "Phân hạng khách VIP theo tổng chi tiêu" cần tính tổng mỗi đơn, rồi tổng mỗi khách, rồi mới xếp hạng. Cách giải là chia nhỏ: tính từng bước, bước sau dùng kết quả bước trước. SQL có hai công cụ cho việc này: subquery (truy vấn con) và CTE (mệnh đề WITH).
Đây là bài 4/5 của series SQL từ cơ bản đến nâng cao. Bài này dùng nhiều kiến thức trước: bài 1 (SELECT, WHERE, NULL), bài 2 (GROUP BY, hàm gộp) và bài 3 (JOIN). Mọi câu lệnh được mình chạy ngày 15/11/2026 trên trang thực hành (SQLite 3.45.2 trong trình duyệt), dữ liệu là giả lập. Như các bài trước, "doanh thu" là tổng tiền của các đơn không bị hủy.
Subquery là gì?
Subquery là một câu SELECT nằm bên trong câu SQL khác, đặt trong ngoặc tròn. SQL chạy câu bên trong trước, rồi dùng kết quả của nó cho câu bên ngoài. Tùy kết quả trả về, subquery được dùng ở ba chỗ chính:
| Subquery trả về | Đặt ở đâu | Ví dụ |
|---|---|---|
| Một giá trị (1 dòng, 1 cột) | WHERE, SELECT, như một con số | gia > (SELECT AVG(gia) ...) |
| Một cột nhiều dòng | sau IN, NOT IN | id IN (SELECT khach_hang_id ...) |
| Cả một bảng | trong FROM, như một bảng tạm | FROM (SELECT ...) AS t |
Subquery trả về một giá trị: so với trung bình
Sản phẩm nào đắt hơn giá trung bình của cửa hàng?
SELECT ten_sp, gia
FROM san_pham
WHERE gia > (SELECT AVG(gia) FROM san_pham)
ORDER BY gia DESC;

Vì sao không viết WHERE gia > AVG(gia)? Bài 2 đã nói: không được đặt hàm gộp trong WHERE. Subquery giải quyết bằng cách tính AVG ở một câu riêng, trả về một con số, rồi WHERE so sánh với con số đó.
Subquery với IN: ai đã mua hàng Âm thanh?
Lấy danh sách khách đã mua ít nhất một sản phẩm thuộc danh mục Âm thanh, không tính đơn hủy:
SELECT ho_ten, thanh_pho
FROM khach_hang
WHERE id IN (
SELECT d.khach_hang_id
FROM don_hang d
JOIN chi_tiet_don_hang ct ON ct.don_hang_id = d.id
JOIN san_pham s ON s.id = ct.san_pham_id
WHERE s.danh_muc = 'Âm thanh' AND d.trang_thai <> 'Đã hủy'
);

Câu bên trong trả về một danh sách mã khách (có thể trùng, ví dụ anh An mua tai nghe ở 2 đơn). IN chỉ hỏi "id có nằm trong danh sách không", nên mỗi khách hiện đúng một lần. Nếu bạn viết bằng JOIN thẳng, anh An sẽ hiện 2 lần và phải thêm DISTINCT.
Subquery tương quan và NOT EXISTS
Khách nào không có đơn nào từ 01/09/2026? Đây là danh sách cần chăm sóc lại:
SELECT k.id, k.ho_ten
FROM khach_hang k
WHERE NOT EXISTS (
SELECT 1 FROM don_hang d
WHERE d.khach_hang_id = k.id
AND d.ngay_dat >= '2026-09-01'
);

Subquery này nhắc tới k.id của câu bên ngoài, nên gọi là subquery tương quan (correlated): với từng khách, SQL kiểm tra xem có tồn tại đơn nào thỏa điều kiện không. EXISTS chỉ quan tâm có dòng hay không, nên bên trong viết SELECT 1 là đủ. Kết quả này cũng làm được bằng LEFT JOIN và IS NULL như bài 3, hai cách đều đúng.
Bẫy NOT IN gặp NULL
Vì sao nhiều người khuyên dùng NOT EXISTS thay cho NOT IN? Chạy thử hai câu nhỏ:
SELECT 'có kết quả' AS kiem_tra WHERE 3 NOT IN (1, 2);
SELECT 'có kết quả' AS kiem_tra WHERE 3 NOT IN (1, 2, NULL);

Số 3 rõ ràng không nằm trong (1, 2, NULL), vậy mà câu thứ hai không trả về gì. 3 NOT IN (1, 2, NULL) nghĩa là 3 <> 1 AND 3 <> 2 AND 3 <> NULL, mà so sánh với NULL cho kết quả "không biết", nên cả biểu thức không bao giờ đúng. Nếu subquery sau NOT IN trả về một cột có thể có NULL (như cột email), kết quả của bạn sẽ rỗng mà không có lỗi nào. Hành vi này giống nhau ở SQLite và SQL Server. NOT EXISTS không gặp bẫy này.
Subquery trong FROM: tính trước, gộp sau
Giá trị trung bình mỗi đơn hàng là bao nhiêu? Không thể viết AVG(SUM(...)). Phải tính tổng từng đơn trước, rồi lấy trung bình trên các tổng đó:
SELECT AVG(tong_tien) AS gia_tri_don_tb,
MAX(tong_tien) AS don_lon_nhat
FROM (
SELECT ct.don_hang_id, SUM(ct.so_luong * ct.don_gia) AS tong_tien
FROM chi_tiet_don_hang ct
JOIN don_hang d ON d.id = ct.don_hang_id
WHERE d.trang_thai <> 'Đã hủy'
GROUP BY ct.don_hang_id
) AS t;

Câu bên trong tạo ra một bảng tạm có 18 dòng (mỗi đơn một dòng), câu bên ngoài coi nó như một bảng bình thường tên t. Đây cũng là cách tránh lỗi cộng lặp khi JOIN mà bài 3 nhắc tới: gộp xong ở bên trong rồi mới JOIN ra ngoài.
Khác biệt SQL Server: subquery trong FROM bắt buộc có bí danh (AS t), thiếu là báo lỗi cú pháp. SQLite thì cho phép bỏ (mình đã chạy thử không có AS t, vẫn ra kết quả). Cứ luôn đặt bí danh để câu lệnh chạy được ở cả hai nơi.
Lưu ý thêm về số thập phân: trung bình ra 1786111.111111111. Trên SQL Server, cột tiền kiểu INT thì AVG sẽ trả về số nguyên (bài 2 đã nói), muốn có phần thập phân phải đổi kiểu trước khi tính.
CTE với WITH: đặt tên cho từng bước
Subquery lồng nhau hai, ba tầng rất khó đọc vì phải đọc từ trong ra ngoài. CTE (Common Table Expression) cho phép viết các bước từ trên xuống dưới, mỗi bước có một cái tên. Ví dụ: phân hạng khách theo tổng chi tiêu, gồm 3 bước:
tong_don: tổng tiền mỗi đơn không bị hủy.theo_khach: số đơn và tổng chi tiêu mỗi khách, tính từ bước 1.- Câu SELECT cuối: nối tên khách, phân hạng bằng
CASE.
WITH tong_don AS (
SELECT d.id, d.khach_hang_id,
SUM(ct.so_luong * ct.don_gia) AS tong_tien
FROM don_hang d
JOIN chi_tiet_don_hang ct ON ct.don_hang_id = d.id
WHERE d.trang_thai <> 'Đã hủy'
GROUP BY d.id, d.khach_hang_id
),
theo_khach AS (
SELECT khach_hang_id, COUNT(*) AS so_don, SUM(tong_tien) AS chi_tieu
FROM tong_don
GROUP BY khach_hang_id
)
SELECT k.ho_ten, t.so_don, t.chi_tieu,
CASE
WHEN t.chi_tieu >= 4000000 THEN 'VIP'
WHEN t.chi_tieu >= 2000000 THEN 'Thân thiết'
ELSE 'Thường'
END AS hang
FROM theo_khach t
JOIN khach_hang k ON k.id = t.khach_hang_id
ORDER BY t.chi_tieu DESC;

Cách đọc: WITH ten AS (SELECT ...) định nghĩa một "bảng tạm có tên", chỉ tồn tại trong câu lệnh này. Nhiều CTE cách nhau bằng dấu phẩy, CTE sau dùng được CTE trước. CASE WHEN ... THEN ... ELSE ... END giống hàm IF: xét điều kiện từ trên xuống, gặp điều kiện đúng đầu tiên thì dừng. Vì vậy điều kiện "từ 4 triệu" phải đứng trước "từ 2 triệu".
Ưu điểm lớn nhất của CTE là dễ gỡ lỗi: khi kết quả sai, bạn chạy riêng từng bước (thay câu SELECT cuối bằng SELECT * FROM tong_don) để xem bước nào sai.
Dùng một CTE nhiều lần
Một CTE có thể được gọi nhiều lần trong cùng câu lệnh. Tháng nào có doanh thu cao hơn trung bình các tháng?
WITH theo_thang AS (
SELECT strftime('%Y-%m', d.ngay_dat) AS thang,
SUM(ct.so_luong * ct.don_gia) AS doanh_thu
FROM don_hang d
JOIN chi_tiet_don_hang ct ON ct.don_hang_id = d.id
WHERE d.trang_thai <> 'Đã hủy'
GROUP BY strftime('%Y-%m', d.ngay_dat)
)
SELECT thang, doanh_thu
FROM theo_thang
WHERE doanh_thu > (SELECT AVG(doanh_thu) FROM theo_thang);

theo_thang được dùng hai lần: một lần ở FROM, một lần trong subquery tính trung bình. Không có CTE, bạn phải chép nguyên đoạn tính doanh thu theo tháng hai lần.
Khác biệt SQL Server: strftime là hàm của SQLite, SQL Server không có. Trên SQL Server, cột ngày nên là kiểu DATE và lấy năm-tháng bằng FORMAT(d.ngay_dat, 'yyyy-MM') (từ SQL Server 2012) hoặc dùng YEAR(d.ngay_dat), MONTH(d.ngay_dat) rồi GROUP BY cả hai. Mình chưa chạy phần này trên SQL Server trong bài, bạn hãy kiểm tra lại trên máy của lớp.
Nâng cao: CTE đệ quy để không bỏ sót tháng trống
Bảng trên không có tháng 11 vì chưa có đơn nào. Báo cáo thật thường cần hiện cả tháng có doanh thu 0. Cách làm: sinh ra danh sách tháng bằng CTE đệ quy (CTE tự gọi lại chính nó), rồi LEFT JOIN với doanh thu:
WITH RECURSIVE thang AS (
SELECT '2026-06-01' AS ngay
UNION ALL
SELECT date(ngay, '+1 month') FROM thang
WHERE ngay < '2026-11-01'
),
doanh_thu AS (
SELECT strftime('%Y-%m', d.ngay_dat) AS thang,
SUM(ct.so_luong * ct.don_gia) AS tien
FROM don_hang d
JOIN chi_tiet_don_hang ct ON ct.don_hang_id = d.id
WHERE d.trang_thai <> 'Đã hủy'
GROUP BY strftime('%Y-%m', d.ngay_dat)
)
SELECT strftime('%Y-%m', t.ngay) AS thang,
COALESCE(dt.tien, 0) AS doanh_thu
FROM thang t
LEFT JOIN doanh_thu dt ON dt.thang = strftime('%Y-%m', t.ngay);

CTE đệ quy có hai phần nối bằng UNION ALL: phần mở đầu (tháng 6) và phần lặp (cộng thêm 1 tháng từ dòng trước), dừng khi điều kiện WHERE sai. COALESCE(dt.tien, 0) đổi NULL thành 0 cho tháng không có đơn.
Khác biệt SQL Server: SQL Server viết WITH, không có chữ RECURSIVE, và dùng DATEADD thay cho date(..., '+1 month'). Phần sinh tháng sẽ trông như sau (mình chưa chạy trên SQL Server):
-- SQL Server (không chạy trên trang thực hành)
WITH thang AS (
SELECT CAST('2026-06-01' AS DATE) AS ngay
UNION ALL
SELECT DATEADD(MONTH, 1, ngay) FROM thang
WHERE ngay < '2026-11-01'
)
SELECT ngay FROM thang;
Subquery hay CTE, chọn cái nào?
- Điều kiện ngắn kiểu "lớn hơn trung bình", "nằm trong danh sách": subquery trong WHERE là gọn nhất.
- Từ 2 bước tính trở lên, hoặc cần dùng lại một kết quả: dùng CTE cho dễ đọc, dễ gỡ lỗi.
- Về kết quả, CTE và subquery trong FROM cho ra như nhau. Còn tốc độ thì tùy hệ quản trị và tùy câu lệnh, đừng chọn cái nào chỉ vì nghe nói "nhanh hơn".
Những lỗi hay gặp
- Subquery trả về nhiều dòng khi cần một giá trị.
WHERE gia = (SELECT gia FROM san_pham WHERE danh_muc = 'Phụ kiện'): bên trong có 5 dòng. SQL Server báo lỗi Subquery returned more than 1 value. SQLite thì không báo lỗi mà lặng lẽ lấy dòng đầu tiên, nguy hiểm hơn vì kết quả sai mà bạn không biết. DùngINnếu thật sự muốn so với nhiều giá trị. - NOT IN với cột có NULL: kết quả rỗng, như ví dụ ở trên.
- Thiếu bí danh cho subquery trong FROM: chạy được trên SQLite nhưng lỗi trên SQL Server.
- Khác biệt SQL Server: thiếu dấu chấm phẩy trước WITH. Nếu câu lệnh đứng ngay trước
WITHtrong cùng một batch chưa kết thúc bằng;, SQL Server báo lỗi cú pháp. Nhiều người viết;WITHcho chắc. - Khác biệt SQL Server: ORDER BY bên trong CTE hoặc subquery. SQL Server không cho phép (trừ khi đi kèm TOP hoặc OFFSET). Đặt ORDER BY ở câu SELECT cuối cùng.
Bài tập
- Đơn hàng nào (không tính đơn hủy) có tổng tiền lớn nhất, của khách nào? Gợi ý: CTE
tong_donnhư trên, rồi so vớiMAX. - Liệt kê các sản phẩm có giá cao hơn giá trung bình của chính danh mục đó. Gợi ý: subquery tương quan, bên trong lọc theo danh mục của dòng bên ngoài.
Xem đáp án (đã chạy thử)
WITH tong_don AS (
SELECT d.id, d.khach_hang_id,
SUM(ct.so_luong * ct.don_gia) AS tong_tien
FROM don_hang d
JOIN chi_tiet_don_hang ct ON ct.don_hang_id = d.id
WHERE d.trang_thai <> 'Đã hủy'
GROUP BY d.id, d.khach_hang_id
)
SELECT t.id AS ma_don, k.ho_ten, t.tong_tien
FROM tong_don t
JOIN khach_hang k ON k.id = t.khach_hang_id
WHERE t.tong_tien = (SELECT MAX(tong_tien) FROM tong_don);
| ma_don | ho_ten | tong_tien |
|---|---|---|
| 17 | Đặng Ngọc Lan | 4500000 |
Đơn 6 cũng 4.500.000 nhưng đã hủy nên không tính. Viết = (SELECT MAX ...) thay vì ORDER BY ... LIMIT 1 có lợi: nếu hai đơn cùng lớn nhất, cả hai đều hiện ra.
SELECT s.ten_sp, s.danh_muc, s.gia
FROM san_pham s
WHERE s.gia > (
SELECT AVG(s2.gia)
FROM san_pham s2
WHERE s2.danh_muc = s.danh_muc
)
ORDER BY s.danh_muc, s.gia DESC;
| ten_sp | danh_muc | gia |
|---|---|---|
| Ổ cứng SSD 512GB | Lưu trữ | 1350000 |
| Màn hình 27 inch V27 | Màn hình | 4500000 |
| Bàn phím cơ K87 | Phụ kiện | 850000 |
| Webcam HD C1 | Phụ kiện | 700000 |
| Tai nghe Bluetooth H5 | Âm thanh | 1200000 |
| Micro thu âm R2 | Âm thanh | 950000 |
Bảng san_pham được dùng hai lần với hai bí danh s và s2. Với mỗi sản phẩm s, câu bên trong tính trung bình giá các sản phẩm s2 cùng danh mục. "Âm thanh" nằm cuối vì SQLite sắp chữ có dấu theo mã byte, như bài 2 đã giải thích.
Bài tiếp theo
Ở bài tập 2, mỗi sản phẩm phải chạy lại một subquery để biết trung bình danh mục. Ở bài 5, bài cuối của series, mình sẽ học window function (ROW_NUMBER, RANK, SUM OVER): tính trung bình nhóm, xếp hạng, cộng dồn mà vẫn giữ nguyên từng dòng, kèm một bộ bài tập tổng hợp cả series.