Ở các buổi trước, mỗi câu SELECT của chúng ta trả lời một câu hỏi: "khách hàng nào ở Hà Nội?", "tổng số dư mỗi loại tài khoản là bao nhiêu?". Nhưng bài toán nghiệp vụ trong công ty chứng khoán hiếm khi đơn giản như vậy. Ví dụ phòng Quản lý rủi ro hỏi: "Những khách hàng nào có tổng tài sản vượt mức trung bình của toàn bộ khách hàng?" Để trả lời, ta phải tính trung bình trước, rồi mới so sánh từng khách hàng với con số đó. Một câu hỏi được xây dựng từ kết quả của một câu hỏi khác — đó chính là lý do ta cần Subquery, CTE và bảng tạm.

Bài giảng này dùng lại bộ dữ liệu mẫu của khóa (xem mục "Bộ dữ liệu mẫu của khóa học" ở Buổi 1): các bảng Customers, Accounts, Securities, MarketPrices, Orders, Trades, Portfolio, CustomerStats, AuditLog. "Hôm nay" của bộ dữ liệu cố định là 2026-09-25. Các ví dụ có nút Chạy thử đều chạy trên bộ dữ liệu này — hãy bấm chạy, đọc kết quả rồi mới đọc lời giải thích.

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

Sau buổi này bạn có thể phân biệt subquery vô hướng (scalar) và subquery nhiều hàng, và biết dùng toán tử nào cho từng loại.
Sau buổi này bạn có thể viết subquery trong WHERE, SELECT, FROM (derived table) và HAVING.
Sau buổi này bạn có thể dùng đúng IN / NOT IN / EXISTS / NOT EXISTS / ALL / ANY / SOME và tránh bẫy NULL của NOT IN.
Sau buổi này bạn có thể phân biệt correlated và non-correlated subquery và nhận biết chi phí của chúng.
Sau buổi này bạn có thể chia một bài toán phân tích thành nhiều bước bằng CTE (WITH, multiple CTE, CTE lồng nhau).
Sau buổi này bạn có thể dùng bảng tạm (#tmp / TEMPORARY TABLE) cho dữ liệu trung gian và chọn đúng giữa Subquery, CTE, TEMP TABLE.

Bản đồ buổi học

Buổi học 180 phút: khoảng 28% lý thuyết + demo (~50 phút) và 72% thực hành (~130 phút, gồm cả thời gian chữa bài). Bài tập nằm xen kẽ trong bài giảng — học xong phần nào, làm ngay bài của phần đó. Buổi có 13 bài nên phần lý thuyết giảng gọn, ưu tiên ví dụ chạy trực tiếp; bài khó chưa xong trong giờ thì hoàn thành ở nhà.

Phần	Nội dung	Thời lượng
Mục 1	Subquery là gì, vị trí đặt, scalar vs nhiều hàng	5 phút
Mục 2	Subquery trong `WHERE`: so sánh, `IN`/`NOT IN`, `EXISTS`, `ALL`/`ANY`	13 phút
Mục 3	Subquery trong `SELECT`, `FROM` (derived table), `HAVING`	7 phút
Mục 4	Correlated subquery, chi phí	4 phút
Thực hành 5.1	9 bài về Subquery (5.1a–5.1i)	78 phút
Mục 5	CTE: cú pháp, multiple CTE, CTE lồng nhau	7 phút
Thực hành 5.2	Phân tích nhiều bước bằng CTE (2 bài, có 1 bài khó)	27 phút
Mục 6	TEMP TABLE: tạo, dùng, xóa	5 phút
Thực hành 5.3	Xử lý dữ liệu trung gian bằng bảng tạm	11 phút
Mục 7	So sánh Subquery → CTE → TEMP TABLE trên cùng một bài toán	4 phút
Thực hành 5.4	Bài tổng hợp (bài khó): chọn cách giải tự do	14 phút
Tổng kết	Sai lầm thường gặp, hỏi đáp	5 phút
	Tổng	180 phút — lý thuyết + demo ~50 phút, thực hành ~130 phút (72%)

1. Subquery là gì? Tại sao cần?

Subquery (truy vấn con) là một câu SELECT được đặt bên trong một câu truy vấn khác, luôn nằm trong cặp ngoặc đơn ( ... ). Câu ngoài gọi là outer query (truy vấn ngoài).

Ví dụ nhu cầu: tìm tài khoản có số dư lớn hơn mức trung bình. Không có subquery, ta phải làm hai bước thủ công. Bước 1 — tính trung bình:

SQL · chạy thử được

SELECT CAST(ROUND(AVG(balance), 2) AS DECIMAL(18,2)) AS avg_balance FROM Accounts;

Kết quả 720290322.58. Bước 2 — gõ tay con số vừa tính vào truy vấn thứ hai: SELECT account_id, balance FROM Accounts WHERE balance > 720290322.58;

Cách này sai về mặt kỹ thuật vì con số bị gõ cứng: ngày mai có tài khoản mới, trung bình đổi, báo cáo lập tức sai. Với subquery, hai bước gộp thành một câu và luôn cập nhật:

SQL · chạy thử được

SELECT account_id, balance
FROM Accounts
WHERE balance > (SELECT AVG(balance) FROM Accounts)
ORDER BY balance DESC, account_id;

account_id	balance
4	8600000000.00
35	8600000000.00
93	8600000000.00
11	3400000000.00
...	... (tổng cộng 24 tài khoản)

Ví dụ nghiệp vụ: phòng Quản lý rủi ro không muốn ai phải "gõ tay" ngưỡng trung bình mỗi sáng. Subquery biến ngưỡng thành một phần của câu truy vấn, nên báo cáo luôn đúng với dữ liệu mới nhất.

1.1. Subquery có thể đặt ở đâu?

Vị trí	Vai trò của subquery	Ví dụ ngắn
`WHERE`	Cung cấp giá trị / danh sách để lọc	`WHERE balance > (SELECT AVG(balance) ...)`
`SELECT`	Tính thêm một cột cho mỗi dòng	`SELECT ..., (SELECT COUNT(*) ...) AS num_accounts`
`FROM`	Tạo "bảng tạm thời" trong câu lệnh (derived table)	`FROM (SELECT customer_id, SUM(balance) ... ) t`
`HAVING`	So sánh kết quả nhóm với một giá trị tính riêng	`HAVING SUM(balance) > (SELECT AVG(...) ...)`

1.2. Scalar subquery và subquery nhiều hàng

Phân loại theo hình dạng kết quả trả về, vì hình dạng quyết định dùng được toán tử nào:

Loại	Kết quả	Dùng với
Scalar (vô hướng)	1 cột, đúng 1 dòng (một giá trị)	`=`, `>`, `<`, `>=`, `<=`, `<>`
Một cột, nhiều dòng	1 cột, 0..n dòng (một danh sách)	`IN`, `NOT IN`, `ANY/SOME`, `ALL`
Nhiều cột, nhiều dòng	một bảng	`EXISTS`, `FROM (...)` (derived table)

Cảnh báo: dùng = với subquery trả về nhiều dòng là lỗi. SQL Server báo "Subquery returned more than 1 value", MySQL báo "Subquery returns more than 1 row". Khi không chắc chỉ có một dòng, dùng IN, hoặc bảo đảm bằng hàm tổng hợp (MAX, AVG...) hoặc khóa duy nhất.

Lưu ý: nếu scalar subquery trả về 0 dòng, kết quả của nó là NULL (không phải lỗi). So sánh với NULL cho UNKNOWN, nên câu truy vấn ngoài trả về rỗng — hãy nhớ điều này khi debug "sao báo cáo trống?".

2. Subquery trong WHERE

2.1. So sánh với kết quả của subquery vô hướng

Các toán tử >, <, =, >=, <=, != (hoặc <>) đặt trước một subquery trả về một giá trị:

Toán tử	Ý nghĩa	Ví dụ
`=`	bằng	`WHERE balance = (SELECT MAX(balance) FROM Accounts)`
`>` / `>=`	lớn hơn / lớn hơn hoặc bằng	`WHERE balance > (SELECT AVG(balance) FROM Accounts)`
`<` / `<=`	nhỏ hơn / nhỏ hơn hoặc bằng	`WHERE close_price <= (SELECT AVG(close_price) FROM MarketPrices)`
`<>` / `!=`	khác	`WHERE customer_id <> (SELECT MIN(customer_id) FROM Customers)`

Ví dụ: số dư lớn nhất trong các tài khoản của khách hàng customer_id = 6, và những tài khoản vượt mức đó:

SQL · chạy thử được

SELECT account_id, customer_id, balance
FROM Accounts
WHERE balance > (SELECT MAX(balance) FROM Accounts WHERE customer_id = 6)
ORDER BY balance DESC, account_id;

Subquery (SELECT MAX(balance) ...) chỉ trả về một giá trị (780.000.000), nên dùng được với >.

2.2. IN và NOT IN

IN kiểm tra một giá trị có nằm trong danh sách mà subquery trả về không. Ví dụ: những mã chứng khoán hiện đang có lệnh chờ khớp:

SQL · chạy thử được

SELECT symbol, company_name
FROM Securities
WHERE symbol IN (SELECT symbol FROM Orders WHERE status = 'PENDING')
ORDER BY symbol;

Kết quả gồm 9 mã: CEO, FPT, HPG, IDC, MBS, NVB, PVS, VCB, VCS.

IN có thể lồng nhiều tầng: "khách hàng ← tài khoản ← lệnh":

SQL · chạy thử được

SELECT customer_id, full_name
FROM Customers
WHERE customer_id IN (SELECT customer_id FROM Accounts
                      WHERE account_id IN (SELECT account_id FROM Orders WHERE status = 'MATCHED'))
ORDER BY customer_id;

Về kết quả, câu này tương đương một JOIN + DISTINCT, nhưng đọc lên rất giống câu hỏi nghiệp vụ. Ưu điểm của IN: không bao giờ nhân dòng (một khách hàng có 10 lệnh vẫn chỉ xuất hiện một lần), trong khi JOIN sẽ lặp lại khách hàng đó 10 lần nếu ta quên DISTINCT.

Bẫy NOT IN và giá trị NULL

Đây là lỗi kinh điển nhất của người mới. Bài toán: tìm khách hàng chưa giới thiệu ai, tức customer_id không nằm trong cột referrer_id. Cách viết trực quan — hãy đếm xem có bao nhiêu khách hàng:

SQL · chạy thử được

SELECT COUNT(*) AS so_khach_hang
FROM Customers
WHERE customer_id NOT IN (SELECT referrer_id FROM Customers);

Kết quả là 0 (cả SQL Server lẫn MySQL) — trong khi thực tế phải có nhiều khách hàng chưa giới thiệu ai. Vì sao? Cột referrer_id chứa nhiều NULL (khách hàng không do ai giới thiệu). x NOT IN (1, 2, NULL) được đánh giá là x <> 1 AND x <> 2 AND x <> NULL; điều kiện cuối luôn là UNKNOWN, nên cả biểu thức không bao giờ là TRUE.

`x`	`x NOT IN (1, 2)`	`x NOT IN (1, 2, NULL)`
3	TRUE	UNKNOWN (dòng bị loại)
1	FALSE	FALSE

Có hai cách sửa. Cách 1: loại NULL khỏi danh sách:

SQL · chạy thử được

SELECT COUNT(*) AS so_khach_hang
FROM Customers
WHERE customer_id NOT IN (SELECT referrer_id FROM Customers WHERE referrer_id IS NOT NULL);

Cách 2 (khuyến nghị): NOT EXISTS — không nhạy cảm với NULL:

SQL · chạy thử được

SELECT c.customer_id, c.full_name
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Customers r WHERE r.referrer_id = c.customer_id)
ORDER BY c.customer_id;

Cả hai cho 35 khách hàng (40 khách hàng trừ 5 người đã giới thiệu: ID 1, 2, 3, 8, 31).

Mẹo: khi viết NOT IN (subquery), luôn tự hỏi "cột trong subquery có thể NULL không?". Nếu có → dùng NOT EXISTS. Đây là quy tắc vàng cần thuộc lòng.

Vừa học xong subquery vô hướng, IN (kể cả lồng nhau) và bẫy NOT IN với NULL, hãy làm ngay bài 5.1a (tài khoản trên mức trung bình), 5.1b (IN lồng nhau) và 5.1d (khách hàng chưa giới thiệu ai — tránh bẫy NULL).

2.3. EXISTS và NOT EXISTS

EXISTS (subquery) trả về TRUE nếu subquery có ít nhất một dòng, bất kể nội dung dòng đó. Vì vậy người ta thường viết SELECT 1 bên trong. Subquery của EXISTS thường tham chiếu tới bảng ngoài (correlated, xem mục 4):

SQL · chạy thử được

-- Khách hàng có ít nhất một lệnh khớp một phần (PARTIAL)
SELECT c.customer_id, c.full_name
FROM Customers c
WHERE EXISTS (SELECT 1
              FROM Accounts a JOIN Orders o ON o.account_id = a.account_id
              WHERE a.customer_id = c.customer_id AND o.status = 'PARTIAL')
ORDER BY c.customer_id;

Kết quả gồm các khách hàng 2, 5, 6, 16, 18. Và NOT EXISTS cho phép tìm "những thứ chưa từng có":

SQL · chạy thử được

-- Khách hàng chưa mở tài khoản nào
SELECT c.customer_id, c.full_name
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Accounts a WHERE a.customer_id = c.customer_id)
ORDER BY c.customer_id;

customer_id	full_name
10	Ngo Thi Nga
15	Vo Van Tuan
28	Dang Thi Linh

NOT EXISTS cũng là mẫu của bài 5.1c (mã chứng khoán chưa có lệnh PENDING). So sánh hai toán tử:

	`IN`	`EXISTS`
Ý nghĩa	giá trị có thuộc danh sách?	có tồn tại dòng thỏa điều kiện?
Subquery trả về	1 cột	bất kỳ (thường `SELECT 1`)
Xử lý NULL	`NOT IN` bị "nhiễm" NULL	`NOT EXISTS` an toàn
Dùng khi	danh sách giá trị ngắn, đơn giản	điều kiện phức tạp, nhiều cột, cần tương quan

2.4. ALL, ANY, SOME

Các từ khóa này đặt sau toán tử so sánh để so sánh với cả một danh sách:

> ALL (danh sách): lớn hơn mọi phần tử (tức lớn hơn giá trị lớn nhất).
> ANY (danh sách) hay > SOME (...): lớn hơn ít nhất một phần tử (tức lớn hơn giá trị nhỏ nhất). SOME là tên khác của ANY.
= ANY (...) tương đương IN (...).
<> ALL (...) tương đương NOT IN (...).

Ví dụ: tài khoản có số dư lớn hơn tất cả tài khoản của khách hàng 6 (số dư 780 triệu, 410 triệu, 410 triệu):

SQL · chạy thử được

SELECT account_id, balance
FROM Accounts
WHERE balance > ALL (SELECT balance FROM Accounts WHERE customer_id = 6)
ORDER BY balance DESC, account_id;

Kết quả gồm 16 tài khoản, bắt đầu bằng tài khoản 4, 35, 93 (8,6 tỷ). Ví dụ với ANY: giá đóng cửa 2026-09-25 của mã nào cao hơn giá vốn của ít nhất một mã mà tài khoản 1 đang nắm (giá vốn 25.500, 24.700, 52.000, 60.700)?

SQL · chạy thử được

SELECT symbol, close_price
FROM MarketPrices
WHERE trade_date = '2026-09-25'
  AND close_price > ANY (SELECT avg_cost FROM Portfolio WHERE account_id = 1)
ORDER BY symbol;

Chỉ những mã có giá đóng cửa cao hơn 24.700 (giá vốn nhỏ nhất) được chọn: 9 mã gồm FPT, HPG, IDC, MBS, PVI, PVS, VCB, VCS, VNM.

Lưu ý: > ALL (danh sách rỗng) là TRUE (vì "không có phần tử nào vi phạm"), còn > ANY (danh sách rỗng) là FALSE. Đây là chỗ hay gây bất ngờ. Nếu danh sách chứa NULL, ALL cũng có thể cho kết quả UNKNOWN giống NOT IN.

Vừa học xong EXISTS/NOT EXISTS và ALL/ANY, hãy làm bài 5.1c (mã chứng khoán chưa có lệnh chờ khớp, dùng NOT EXISTS) và 5.1e (số dư vượt mọi tài khoản của một khách hàng, dùng ALL).

3. Subquery trong SELECT và FROM

3.1. Cột tính toán từ subquery (trong SELECT)

Subquery vô hướng có thể đặt trong danh sách cột để tạo cột thống kê đi kèm mỗi dòng:

SQL · chạy thử được

SELECT c.customer_id, c.full_name,
       (SELECT COUNT(*) FROM Accounts a WHERE a.customer_id = c.customer_id) AS num_accounts,
       COALESCE((SELECT SUM(a.balance) FROM Accounts a WHERE a.customer_id = c.customer_id), 0) AS total_balance
FROM Customers c
WHERE c.customer_id <= 5
ORDER BY c.customer_id;

customer_id	full_name	num_accounts	total_balance
1	Nguyen Van An	1	67000000.00
2	Tran Thi Binh	3	8640000000.00
3	Le Van Cuong	3	168000000.00
4	Pham Thi Dung	2	1480000000.00
5	Hoang Van Em	2	3432000000.00

Ưu điểm: giữ nguyên mỗi khách hàng một dòng, kể cả khách hàng chưa có tài khoản (với INNER JOIN họ sẽ biến mất; với LEFT JOIN + GROUP BY thì phức tạp hơn). Chú ý COALESCE(..., 0) vì SUM trên tập rỗng trả NULL. Subquery trong SELECT bắt buộc là scalar: nếu trả về nhiều dòng sẽ báo lỗi.

3.2. Derived table (subquery trong FROM)

Subquery đặt ở FROM biến thành một bảng ảo tồn tại trong suốt câu lệnh, và bắt buộc phải có alias (cả SQL Server lẫn MySQL). Rất hữu ích khi cần gom nhóm hai lần (tổng theo khách hàng, rồi trung bình theo thành phố):

SQL · chạy thử được

SELECT c.city, COUNT(*) AS num_customers,
       CAST(ROUND(AVG(t.total_assets), 2) AS DECIMAL(18,2)) AS avg_assets
FROM Customers c
JOIN (SELECT customer_id, SUM(balance) AS total_assets
      FROM Accounts GROUP BY customer_id) t
  ON t.customer_id = c.customer_id
GROUP BY c.city
ORDER BY avg_assets DESC, c.city;

city	num_customers	avg_assets
Hai Phong	3	4601333333.33
TP.HCM	7	3167000000.00
Da Nang	4	2672000000.00
Can Tho	3	901666666.67
Ha Noi	20	881050000.00

Không thể viết AVG(SUM(balance)) trực tiếp — hàm tổng hợp lồng nhau không hợp lệ — nên derived table là lời giải tự nhiên. Cách đọc: bên trong tính tổng tài sản mỗi khách hàng (bảng ảo t), bên ngoài nối với Customers để lấy thành phố rồi tính trung bình theo thành phố.

Alias cho derived table là bắt buộc: thiếu alias, SQL Server báo "Incorrect syntax near ')'", MySQL báo "Every derived table must have its own alias". Mọi cột của derived table cũng phải có tên (dùng AS).

SQL Server vs MySQL: trong derived table, SQL Server không cho ORDER BY nếu không kèm TOP/OFFSET (báo lỗi "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries..."); MySQL cho phép nhưng bỏ qua. Vì vậy hãy đặt ORDER BY ở truy vấn ngoài cùng.

Vừa học xong subquery trong SELECT (cột tính từ subquery) và trong FROM (derived table), hãy làm bài 5.1f (hồ sơ khách hàng với cột tính từ subquery) và 5.1g (tài sản trung bình theo thành phố).

3.3. Subquery trong HAVING

HAVING lọc nhóm sau GROUP BY; ta có thể so sánh tổng của nhóm với một con số tính từ subquery:

SQL · chạy thử được

SELECT c.customer_id, c.full_name, SUM(a.balance) AS total_assets
FROM Customers c JOIN Accounts a ON a.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
HAVING SUM(a.balance) > (SELECT AVG(t.total)
                         FROM (SELECT SUM(balance) AS total FROM Accounts GROUP BY customer_id) t)
ORDER BY total_assets DESC, c.customer_id;

Trung bình tổng tài sản/khách hàng khoảng 1,81 tỷ, nên 7 khách hàng lọt vào: 17 (12,067 tỷ), 40 (9,5 tỷ), 2 (8,64 tỷ), 34, 38, 32, 5.

Lưu ý: cần phân biệt trung bình số dư tài khoản (khoảng 0,72 tỷ) với trung bình tổng tài sản khách hàng (1,81 tỷ). Bài 5.1h kiểm tra bạn có hiểu đúng "mức trung bình của nhóm" hay không.

3.4. Khi nào dùng subquery ở SELECT và FROM?

Vị trí	Dùng khi	Lưu ý
`SELECT`	cần 1–3 con số thống kê phụ và muốn giữ nguyên số dòng của bảng chính	phải là scalar; mỗi cột là một lần quét bảng phụ — quá nhiều cột thì chuyển sang `JOIN`
`FROM`	cần tính trước một bước (gom nhóm, xếp hạng, lọc) rồi mới nối/gom tiếp	bắt buộc alias; đặt `ORDER BY` ở ngoài cùng
`HAVING`	so sánh kết quả nhóm với một con số tính riêng	phân biệt "trung bình theo dòng" và "trung bình theo nhóm"

4. Correlated Subquery (subquery tương quan)

Non-correlated subquery độc lập với truy vấn ngoài: chạy được riêng lẻ (như SELECT AVG(balance) FROM Accounts). Về nguyên tắc chỉ cần tính một lần.

Correlated subquery tham chiếu tới cột của truy vấn ngoài, nên không chạy riêng được và về mặt logic phải tính lại cho từng dòng của truy vấn ngoài.

	Non-correlated	Correlated
Chạy riêng được?	có	không (dùng cột của bảng ngoài)
Số lần tính (về logic)	1 lần	1 lần cho mỗi dòng ngoài
Ví dụ	`WHERE balance > (SELECT AVG(balance) FROM Accounts)`	`WHERE balance > (SELECT AVG(b.balance) FROM Accounts b WHERE b.account_type = a.account_type)`

Bài toán kinh điển: tìm tài khoản có số dư lớn hơn mức trung bình của cùng loại (CASH / MARGIN):

SQL · chạy thử được

SELECT a.account_id, a.account_type, a.balance,
       CAST(ROUND((SELECT AVG(b.balance) FROM Accounts b
                   WHERE b.account_type = a.account_type), 2) AS DECIMAL(18,2)) AS type_avg
FROM Accounts a
WHERE a.balance > (SELECT AVG(b.balance) FROM Accounts b WHERE b.account_type = a.account_type)
ORDER BY a.account_type, a.balance DESC, a.account_id;

Trung bình của CASH là 831.875.000, của MARGIN là 551.405.405,41; mỗi tài khoản được so với mức của đúng loại của nó. Chú ý cách đặt alias khác nhau (a cho bảng ngoài, b cho bảng trong) — nếu quên alias, điều kiện account_type = account_type luôn đúng và bạn so sánh với trung bình toàn bảng.

Chi phí và lưu ý

Về logic, subquery tương quan chạy N lần (N = số dòng ngoài). Trình tối ưu của SQL Server và MySQL 8 thường tự biến đổi thành join/semi-join, nhưng không phải lúc nào cũng vậy.
Với bảng lớn (hàng triệu lệnh), hãy đặt index trên cột tương quan (Accounts(account_type), Orders(account_id)).
Nếu thấy chậm, thử viết lại thành JOIN với derived table (tính trung bình một lần cho mỗi loại, nối lại) hoặc dùng CTE (mục 5).
EXISTS / NOT EXISTS dừng ngay khi tìm thấy dòng đầu tiên nên thường rất hiệu quả.

Mẹo: cách nhận biết nhanh một subquery là correlated: xóa nó khỏi câu lệnh, dán riêng vào cửa sổ Query. Nếu báo lỗi "invalid column" (thiếu cột của bảng ngoài) thì nó là correlated.

Vừa học xong subquery trong HAVING (mục 3.3) và correlated subquery, hãy làm bài 5.1h (khách hàng có tổng tài sản trên trung bình) và 5.1i (tài khoản vượt trung bình cùng loại) để kết thúc phần Subquery trước khi sang CTE.

5. CTE — Common Table Expression

5.1. Khái niệm và cú pháp WITH

CTE là một "bảng tạm có tên" chỉ tồn tại trong một câu lệnh, khai báo bằng WITH:

SQL

WITH ten_cte AS (
    SELECT ...
)
SELECT ... FROM ten_cte ...;

Ví dụ: khách hàng có tổng tài sản trên 5 tỷ:

SQL · chạy thử được

WITH t AS (
    SELECT customer_id, SUM(balance) AS tot
    FROM Accounts
    GROUP BY customer_id
)
SELECT customer_id, tot FROM t WHERE tot > 5000000000 ORDER BY tot DESC;

customer_id	tot
17	12067000000.00
40	9500000000.00
2	8640000000.00

SQL Server vs MySQL: với SQL Server, nếu WITH không phải câu lệnh đầu tiên trong batch, câu trước nó phải kết thúc bằng dấu ;, nếu không sẽ báo "Incorrect syntax near the keyword 'WITH'". Thói quen tốt: luôn kết thúc câu lệnh bằng ;. MySQL hỗ trợ CTE từ 8.0 (bộ Judge0 của khóa dùng MySQL 8).

Trước khi so sánh CTE với subquery, hãy nhìn một CTE nhiều bước như một quy trình: bước sau đọc kết quả của bước trước.

CTE chia bài toán thành các bước nối tiếp

Bước 1 · WITH tong_tai_san

SUM(balance) theo từng khách hàng

customer_id	tong
1	230
2	45
3	350

→

Bước 2 · , phan_loai

CASE: VIP nếu tổng > 200

customer_id	nhom
1	VIP
2	Thường
3	VIP

→

Bước 3 · SELECT cuối

Lọc nhóm mục tiêu: nhom = 'VIP'

customer_id	nhom
1	VIP
3	VIP

Mỗi CTE là một 'bảng tạm có tên' chỉ sống trong đúng câu lệnh này; bước sau đọc kết quả bước trước. Đọc từ trên xuống dưới như một quy trình phân tích.

5.2. CTE so với Subquery

Tiêu chí	Subquery / Derived table	CTE
Đọc từ trên xuống	khó nếu lồng nhiều tầng (đọc từ trong ra ngoài)	dễ: từng bước có tên
Dùng lại trong cùng câu	phải viết lại từng lần	gọi tên nhiều lần
Đệ quy	không	có (recursive CTE)
Lưu kết quả	không	không (chỉ là cách viết; có thể bị tính lại mỗi lần được nhắc tới)

5.3. Multiple CTE và CTE lồng nhau

Nhiều CTE ngăn cách bởi dấu phẩy, chỉ một từ khóa WITH. CTE sau được phép tham chiếu CTE trước (đây là dạng "lồng nhau" thường gặp). Báo cáo phân tầng khách hàng kiểu bài 5.2a là ví dụ điển hình — chia thành 4 bước: tổng tài sản → trung bình → phân loại → lọc nhóm mục tiêu:

SQL · chạy thử được

WITH total_assets AS (                       -- Bước 1: tổng tài sản từng khách hàng (tài khoản ACTIVE)
    SELECT customer_id, SUM(balance) AS total
    FROM Accounts WHERE status = 'ACTIVE' GROUP BY customer_id
), avg_assets AS (                           -- Bước 2: trung bình (dùng CTE ở bước 1)
    SELECT AVG(total) AS avg_total FROM total_assets
), tiers AS (                                -- Bước 3: phân loại
    SELECT t.customer_id, t.total, a.avg_total,
           CASE WHEN t.total >= 2 * a.avg_total THEN 'CAO'
                WHEN t.total >= a.avg_total     THEN 'TRUNG_BINH'
                ELSE 'THAP' END AS tier
    FROM total_assets t CROSS JOIN avg_assets a
)
SELECT customer_id, total AS total_assets,   -- Bước 4: lọc nhóm mục tiêu
       CAST(ROUND(avg_total, 2) AS DECIMAL(18,2)) AS avg_assets, tier
FROM tiers WHERE tier = 'CAO'
ORDER BY total DESC, customer_id;

Kết quả: 6 khách hàng nhóm CAO (17, 40, 2, 34, 38, 32) so với mức trung bình 1.826.628.571,43.

Mẹo: mỗi bước có thể kiểm tra riêng bằng cách đổi câu cuối thành SELECT * FROM <tên CTE> — kỹ thuật debug rất hữu ích. Thử: thay câu SELECT cuối bằng SELECT * FROM tiers ORDER BY customer_id; để xem toàn bộ khách hàng và tầng của họ.

Bài 5.2b nâng cấp lên bài toán "giá trị danh mục theo giá thị trường" với chuỗi CTE px → acc_value → cust_value → summary, trong đó mỗi CTE dựa vào CTE trước.

5.4. Tái sử dụng kết quả trong một câu SQL

Một CTE có thể được dùng nhiều lần trong câu lệnh cuối; ví dụ total_assets ở trên vừa dùng để tính trung bình vừa để phân loại. Với subquery ta phải sao chép đoạn code hai lần:

SQL · chạy thử được

WITH t AS (SELECT customer_id, SUM(balance) AS total FROM Accounts GROUP BY customer_id)
SELECT customer_id, total AS total_assets
FROM t
WHERE total > (SELECT AVG(total) FROM t)   -- dùng lại t lần thứ hai
ORDER BY total DESC, customer_id;

Lưu ý: CTE không bảo đảm được "cache" — trình tối ưu có thể tính lại mỗi lần nhắc đến. Nếu bước tính tốn kém và dùng nhiều lần, hãy cân nhắc bảng tạm (mục 6).

5.5. Mở rộng: CTE đệ quy (giới thiệu)

CTE có thể tự tham chiếu để duyệt cấu trúc cây. Ví dụ: chuỗi người được giới thiệu (trực tiếp và gián tiếp) từ khách hàng 1. Cú pháp khác nhau giữa hai hệ:

SQL Server — chỉ cần WITH (không có từ RECURSIVE):

SQL · chạy thử được

SQL Server

WITH chain AS (
    SELECT customer_id, referrer_id, 1 AS lvl FROM Customers WHERE referrer_id = 1
    UNION ALL
    SELECT c.customer_id, c.referrer_id, ch.lvl + 1
    FROM Customers c JOIN chain ch ON c.referrer_id = ch.customer_id
)
SELECT customer_id, lvl FROM chain ORDER BY lvl, customer_id;

MySQL — thêm từ khóa RECURSIVE:

SQL · chạy thử được

MySQL

WITH RECURSIVE chain AS (
    SELECT customer_id, referrer_id, 1 AS lvl FROM Customers WHERE referrer_id = 1
    UNION ALL
    SELECT c.customer_id, c.referrer_id, ch.lvl + 1
    FROM Customers c JOIN chain ch ON c.referrer_id = ch.customer_id
)
SELECT customer_id, lvl FROM chain ORDER BY lvl, customer_id;

Kết quả: khách hàng 8 và 11 (cấp 1), 27 và 29 (cấp 2). Đây là phần mở rộng, không nằm trong bài tập chấm điểm.

Bạn đã nắm CTE: cú pháp WITH, multiple CTE, CTE lồng nhau và tái sử dụng. Vừa học xong phần này, hãy làm các bài 5.2a (báo cáo phân tầng khách hàng, 4 bước) và 5.2b (giá trị danh mục theo giá thị trường, chuỗi CTE) để tập chia một bài toán lớn thành nhiều bước có tên.

6. TEMP TABLE — Bảng tạm

6.1. Khái niệm và mục đích

Bảng tạm là bảng thật (có cấu trúc, có thể có index) nhưng chỉ tồn tại trong phiên làm việc (session) của bạn và tự biến mất khi đóng phiên. Khác với CTE (chỉ sống trong một câu lệnh), bảng tạm sống qua nhiều câu lệnh, và kết quả được lưu thật nên không bị tính lại. Dùng khi:

kết quả trung gian tốn nhiều thời gian để tính và được dùng nhiều lần;
cần nhiều bước xử lý, mỗi bước kiểm tra riêng;
cần thêm index vào tập trung gian, hoặc cập nhật (UPDATE) dữ liệu trung gian.

Ví dụ nghiệp vụ: chốt danh sách khách hàng đủ điều kiện ưu đãi phí vào một bảng tạm, sau đó dùng danh sách đó cho ba báo cáo khác nhau — không phải tính lại điều kiện phức tạp mỗi lần.

6.2. Tạo, dùng và xóa

SQL Server — tên bắt đầu bằng # (riêng theo phiên) hoặc ## (dùng chung toàn máy chủ, hiếm dùng):

SQL · chạy thử được

SQL Server

-- Cách 1: tạo và đổ dữ liệu cùng lúc (SELECT ... INTO)
SELECT c.customer_id, SUM(a.balance) AS total_assets
INTO #eligible
FROM Customers c JOIN Accounts a ON a.customer_id = c.customer_id
WHERE c.status = 'ACTIVE' AND a.status = 'ACTIVE'
GROUP BY c.customer_id
HAVING SUM(a.balance) >= 100000000;

-- Dùng bảng tạm: ba khách hàng đứng đầu
SELECT TOP 3 customer_id, total_assets FROM #eligible ORDER BY total_assets DESC, customer_id;

Cách 2: khai báo cấu trúc rồi INSERT ... SELECT (kiểm soát kiểu dữ liệu, thêm khóa chính), và xóa sớm khi xong (không bắt buộc):

SQL

CREATE TABLE #eligible2 (customer_id INT PRIMARY KEY, total_assets DECIMAL(18,2));
INSERT INTO #eligible2 SELECT customer_id, SUM(balance) FROM Accounts GROUP BY customer_id;
DROP TABLE IF EXISTS #eligible;

MySQL — thêm từ khóa TEMPORARY, không dùng ký hiệu #:

SQL · chạy thử được

MySQL

CREATE TEMPORARY TABLE eligible AS
SELECT c.customer_id, SUM(a.balance) AS total_assets
FROM Customers c JOIN Accounts a ON a.customer_id = c.customer_id
WHERE c.status = 'ACTIVE' AND a.status = 'ACTIVE'
GROUP BY c.customer_id
HAVING SUM(a.balance) >= 100000000;

SELECT customer_id, total_assets FROM eligible ORDER BY total_assets DESC, customer_id LIMIT 3;

Xóa bảng tạm trong MySQL: DROP TEMPORARY TABLE IF EXISTS eligible;. Với dữ liệu mẫu, bảng eligible gồm 25 khách hàng; khách hàng 40 (9,5 tỷ), 2 (8,64 tỷ), 34 (4,69 tỷ) là ba người đứng đầu.

Khác biệt cần nhớ

	SQL Server	MySQL
Ký hiệu	`#tên` (phiên), `##tên` (toàn cục)	`CREATE TEMPORARY TABLE tên`
Tạo từ SELECT	`SELECT ... INTO #t`	`CREATE TEMPORARY TABLE t AS SELECT ...`
Xóa	`DROP TABLE #t`	`DROP TEMPORARY TABLE t`
Nhắc tới nhiều lần trong một truy vấn	được	không (lỗi "Can't reopen table")
Tên trùng bảng thật	tên `#` không trùng	bảng tạm che khuất bảng thật cùng tên

Cảnh báo (MySQL): lỗi "Can't reopen table" xảy ra khi bạn JOIN một bảng tạm với chính nó hoặc dùng nó trong cả truy vấn ngoài lẫn subquery. Cách khắc phục: tạo bảng tạm thứ hai (như bài 5.3a làm) hoặc dùng CTE.

Ngoài #tmp, SQL Server còn có biến bảng DECLARE @t TABLE (...) (nhẹ hơn, phạm vi trong batch) — tham khảo thêm khi làm stored procedure ở các buổi sau.

6.3. TEMP TABLE, CTE và Derived table — chọn cái nào?

Tiêu chí	Derived table	CTE	Bảng tạm
Phạm vi sống	trong một câu	trong một câu	cả phiên
Dùng lại nhiều câu lệnh	không	không	có
Lưu kết quả thật	không	không	có
Đánh index	không	không	có
Độ dễ đọc	thấp nếu lồng sâu	cao	cao (từng bước rõ ràng)
Cần quyền tạo bảng	không	không	tạo trong tempdb (SQL Server)
Phù hợp	bước gom nhóm đơn giản	phân tích nhiều bước trong một báo cáo	dữ liệu trung gian lớn, dùng lại, debug từng bước

Quy tắc chọn nhanh: bắt đầu bằng subquery/CTE (đơn giản, không "rác" dữ liệu). Chuyển sang bảng tạm khi kết quả trung gian lớn, tốn kém, và được dùng lại trong nhiều câu lệnh.

Vừa học xong bảng tạm, hãy làm bài 5.3a: tạo bảng tạm chứa tập khách hàng đủ điều kiện, rồi dùng nó cho bước phân tích tiếp theo.

7. Thực hành tổng hợp: Subquery → CTE → TEMP TABLE

Cùng một bài toán, ba cách viết. Bài toán: "Khách hàng có tổng tài sản lớn hơn hẳn mức trung bình của tất cả khách hàng" (kết quả gồm 7 khách hàng, đứng đầu là 17, 40, 2).

Cách 1 — Subquery: gọn nhất khi logic đơn giản (mục 3.3).

SQL · chạy thử được

SELECT customer_id, SUM(balance) AS total_assets
FROM Accounts
GROUP BY customer_id
HAVING SUM(balance) > (SELECT AVG(t.total) FROM (SELECT SUM(balance) AS total FROM Accounts GROUP BY customer_id) t)
ORDER BY total_assets DESC, customer_id;

Cách 2 — CTE: dễ đọc, tổng tài sản chỉ khai báo một lần.

SQL · chạy thử được

WITH t AS (SELECT customer_id, SUM(balance) AS total FROM Accounts GROUP BY customer_id)
SELECT customer_id, total AS total_assets
FROM t
WHERE total > (SELECT AVG(total) FROM t)
ORDER BY total DESC, customer_id;

Cách 3 — TEMP TABLE: từng bước tách rời, kiểm tra được. SQL Server:

SQL · chạy thử được

SQL Server

SELECT customer_id, SUM(balance) AS total INTO #t FROM Accounts GROUP BY customer_id;

SELECT customer_id, total AS total_assets FROM #t
WHERE total > (SELECT AVG(total) FROM #t)
ORDER BY total DESC, customer_id;

MySQL không dùng được một bảng tạm hai lần trong một truy vấn, nên phải tính trung bình sang bảng tạm thứ hai:

SQL · chạy thử được

MySQL

CREATE TEMPORARY TABLE t  AS SELECT customer_id, SUM(balance) AS total FROM Accounts GROUP BY customer_id;
CREATE TEMPORARY TABLE av AS SELECT AVG(total) AS avg_total FROM t;

SELECT t.customer_id, t.total AS total_assets FROM t JOIN av ON t.total > av.avg_total
ORDER BY t.total DESC, t.customer_id;

Cả ba cho cùng kết quả. So sánh:

Cách	Dễ đọc	Tái sử dụng	Hiệu năng (dữ liệu lớn)	Khi nào chọn
Subquery	vừa (lồng 2 tầng)	không	tốt cho truy vấn đơn	điều kiện lọc đơn giản, một lần
CTE	tốt	trong 1 câu	tương đương subquery	báo cáo nhiều bước trong một câu
TEMP TABLE	tốt	nhiều câu	tốt nhất khi dùng lại nhiều lần / có index	dữ liệu trung gian lớn, dùng lại, cần debug từng bước

Mẹo: ba tiêu chí chọn cách viết: (1) dễ đọc — CTE thắng khi có từ 3 bước; (2) tái sử dụng — nếu kết quả dùng qua nhiều câu lệnh, bảng tạm; (3) hiệu năng — đo bằng kế hoạch thực thi trước khi kết luận, đừng đoán.

Bài 5.4a (khách hàng nắm giữ lớn nhất từng mã) là bài tổng hợp: bạn tự chọn cách giải. Gợi ý: CTE holdings (số lượng nắm giữ theo cặp khách hàng–mã), CTE totals (lớn nhất và tổng theo mã), rồi nối lại; chú ý xử lý đồng hạng bằng MIN(customer_id).

Sai lầm thường gặp

Sai lầm	Hậu quả	Cách đúng
`NOT IN` với cột có `NULL`	kết quả rỗng oan	`NOT EXISTS`, hoặc thêm `IS NOT NULL` trong subquery
Dùng `=` với subquery trả về nhiều dòng	lỗi "returned more than 1 value/row"	dùng `IN`, hoặc `MAX`/`AVG`, hoặc khóa duy nhất
Quên alias cho derived table	lỗi cú pháp	luôn đặt alias: `FROM (...) t`
Quên `;` trước `WITH` trong SQL Server	Incorrect syntax near the keyword 'WITH'	kết thúc mọi câu bằng `;`
Correlated subquery không đặt alias	so sánh cột với chính nó	`a` cho bảng ngoài, `b` cho bảng trong
Nhầm "trung bình số dư tài khoản" với "trung bình tổng tài sản khách hàng"	sai nhóm mục tiêu	gom theo khách hàng trước, rồi mới lấy trung bình
MySQL: dùng một bảng tạm hai lần trong một truy vấn	lỗi Can't reopen table	tạo bảng tạm thứ hai, hoặc dùng CTE
Dùng `GETDATE()`/`NOW()` để lấy "hôm nay"	kết quả đổi theo ngày, chấm sai	dùng ngày cố định `'2026-09-25'`
Chia số nguyên: `100 * a / b` trong T-SQL	kết quả nguyên, mất phần thập phân	`100.0 * a / b` và `CAST(ROUND(x, 2) AS DECIMAL(18,2))`

Tóm tắt

Chủ đề	Ghi nhớ
Scalar subquery	1 giá trị; dùng với `= > < ...`
`IN`/`NOT IN`	danh sách 1 cột; `NOT IN` + `NULL` = bẫy
`EXISTS`/`NOT EXISTS`	có/không tồn tại dòng; an toàn với `NULL`
`ALL`/`ANY`/`SOME`	so sánh với cả danh sách; `> ALL` = lớn hơn MAX
SELECT subquery	cột thống kê phụ; phải scalar
Derived table	subquery ở `FROM`, bắt buộc alias
Correlated	tham chiếu bảng ngoài; nhớ alias và index
CTE	`WITH a AS (...), b AS (...)`; đọc từng bước; sống trong 1 câu
TEMP TABLE	`#t` / `TEMPORARY TABLE`; sống cả phiên, lưu thật

Gợi ý mở rộng

Viết lại bài 5.1i bằng JOIN với derived table và so sánh thời gian chạy (bật SET STATISTICS TIME ON trong SSMS).
Thử bài 5.2a với các ngưỡng phân loại khác (1,5 lần, 3 lần trung bình) và thêm nhóm 'TRUNG_BINH' vào kết quả.
Dùng CTE đệ quy để tính "cây giới thiệu" đầy đủ của chương trình Bạn giới thiệu bạn.
Tìm hiểu hàm cửa sổ (ROW_NUMBER, RANK) — cách hiện đại để giải "top-N mỗi nhóm" mà không cần subquery tương quan.

CTE, TEMP TABLE và Subquery (truy vấn con)

Buổi 5: CTE, TEMP TABLE và Subquery (truy vấn con)

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

Bản đồ buổi học

1. Subquery là gì? Tại sao cần?

1.1. Subquery có thể đặt ở đâu?

1.2. Scalar subquery và subquery nhiều hàng

2. Subquery trong WHERE

2.1. So sánh với kết quả của subquery vô hướng

2.2. IN và NOT IN

Bẫy NOT IN và giá trị NULL

2.3. EXISTS và NOT EXISTS

2.4. ALL, ANY, SOME

3. Subquery trong SELECT và FROM

3.1. Cột tính toán từ subquery (trong SELECT)

3.2. Derived table (subquery trong FROM)

3.3. Subquery trong HAVING

3.4. Khi nào dùng subquery ở SELECT và FROM?

4. Correlated Subquery (subquery tương quan)

5. CTE — Common Table Expression

5.1. Khái niệm và cú pháp WITH

5.2. CTE so với Subquery

5.3. Multiple CTE và CTE lồng nhau

5.4. Tái sử dụng kết quả trong một câu SQL

5.5. Mở rộng: CTE đệ quy (giới thiệu)

6. TEMP TABLE — Bảng tạm

6.1. Khái niệm và mục đích

6.2. Tạo, dùng và xóa

6.3. TEMP TABLE, CTE và Derived table — chọn cái nào?

7. Thực hành tổng hợp: Subquery → CTE → TEMP TABLE

Sai lầm thường gặp

Tóm tắt

Gợi ý mở rộng

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