Giao diện
CTE (Common Table Expressions): Code sạch hơn, đời đẹp hơn
CTE (thường đi với từ khóa WITH) giúp bạn đặt tên cho một kết quả truy vấn tạm thời, làm cho code dễ đọc hơn gấp 10 lần so với Subquery lồng nhau.
Tại sao dùng CTE thay vì Subquery?
Cách cũ (Subquery lồng nhau - Nightmare):
sql
SELECT * FROM (
SELECT user_id, SUM(amount) as total FROM orders GROUP BY user_id
) as sales
WHERE total > 1000;Cách mới (CTE - Clean):
sql
WITH sales AS (
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
)
SELECT * FROM sales WHERE total > 1000;Đọc code như đọc văn xuôi: "Với bảng sales được định nghĩa thế này... hãy lấy những dòng..."
Recursive CTE (Đệ quy) - Vũ khí bí mật
Đây là tính năng mạnh nhất của CTE, dùng để truy vấn dữ liệu dạng cây (Tree/Hierarchy) như Danh mục đa cấp, Cây thư mục, Cây tổ chức nhân sự.
Bài toán: Lấy toàn bộ danh mục con, cháu, chắt... của danh mục "Laptop" (ID=1). Bảng categories: id, name, parent_id.
sql
WITH RECURSIVE category_tree AS (
-- 1. Anchor Member: Điểm bắt đầu (Gốc)
SELECT id, name, parent_id, 1 as level
FROM categories
WHERE id = 1 -- Bắt đầu từ Laptop
UNION ALL
-- 2. Recursive Member: Đệ quy tìm con
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;Giải thích cơ chế:
- Chạy phần trên
UNION ALLtrước -> Được "Laptop". - Lấy ID của "Laptop" ném xuống dưới
INNER JOIN-> Tìm ra các con trực tiếp (Dell, Asus). - Lấy ID của (Dell, Asus) ném tiếp vào vòng lặp -> Tìm ra cháu (Dell XPS, Asus ROG).
- Lặp lại cho đến khi không tìm thấy con nào nữa.
💡 HPN Pro Tip: Materialized CTE (Postgres)
Trong PostgreSQL 12+, bạn có thể ép Database lưu kết quả CTE vào bộ nhớ tạm (Materialize) để không phải tính lại nếu CTE đó được gọi nhiều lần trong query chính.
sql
WITH heavy_calculation AS MATERIALIZED (
...
)Dùng cái này cẩn thận, chỉ khi bạn biết chắc việc tính lại tốn kém hơn việc chiếm RAM.
⚠️ Common Mistake
Quên điều kiện dừng (Termination Condition) trong Recursive CTE. Nếu dữ liệu của bạn bị vòng lặp (Circular Reference - A là cha B, B là cha A), câu lệnh Recursive sẽ chạy vĩnh viễn (hoặc crash server vì hết stack).
Giải pháp: Luôn đảm bảo dữ liệu cây không bị vòng tròn, hoặc thêm biến đếm depth để dừng nếu sâu quá mức quy định (ví dụ WHERE level < 10).
🧠 Quiz
Câu 1: CTE (WITH clause) có ưu điểm chính nào so với subquery?
- [ ] A) Luôn nhanh hơn subquery
- [x] B) Đặt tên cho subquery, tăng readability và cho phép tái sử dụng trong cùng câu query
- [ ] C) Tự động tạo index
- [ ] D) Thay thế được JOIN trong mọi trường hợp
💡 Giải thích: CTE giúp đặt tên cho logic phức tạp, viết tuần tự từ trên xuống — dễ đọc hơn subquery lồng nhau. CTE còn có thể reference lại nhiều lần trong cùng query (subquery phải copy-paste).
Câu 2: CTE có được materialized (lưu tạm) mặc định không?
- [ ] A) Luôn materialized
- [ ] B) Luôn inline (thay thế vào query)
- [x] C) Tùy database — PostgreSQL 12+ cho chọn MATERIALIZED/NOT MATERIALIZED, các DB khác có hành vi riêng
- [ ] D) Chỉ materialized khi dùng RECURSIVE
💡 Giải thích: Trong PostgreSQL < 12, CTE luôn materialized (optimization fence). Từ v12+, optimizer có thể inline CTE. MySQL 8+ inline mặc định. Hiểu điều này để không bị bất ngờ về performance.
Câu 3: Recursive CTE cần gì để không chạy vô hạn?
- [ ] A) Tự động dừng khi hết memory
- [x] B) Cần termination condition: phần recursive phải dần giảm kết quả về rỗng, hoặc dùng giới hạn depth
- [ ] C) Database tự phát hiện vòng lặp
- [ ] D) Chỉ cần thêm LIMIT
💡 Giải thích: Recursive CTE lặp cho đến khi phần recursive trả về 0 dòng. Nếu dữ liệu có circular reference (A→B→A), nó lặp vĩnh viễn. Luôn thêm
WHERE depth < Nhoặc đảm bảo dữ liệu không có vòng tròn.