Subquery Và CTE Trong SQL: Viết Truy Vấn Phức Tạp Dễ Đọc Hơn

1. Subquery Là Gì? #

Subquery (truy vấn con) là query nằm bên trong query khác. Nó chạy trước, trả về kết quả, rồi query ngoài dùng kết quả đó. Subquery có thể xuất hiện trong WHERE, FROM, SELECT, hoặc HAVING — mỗi vị trí có ý nghĩa khác nhau.

Ví dụ cơ bản: tìm nhân viên có lương cao hơn trung bình công ty. Thay vì tính AVG riêng rồi viết WHERE, dùng subquery: WHERE salary > (SELECT AVG(salary) FROM employees).

2. Các Loại Subquery #

Scalar subquery: trả về 1 giá trị duy nhất (1 dòng, 1 cột). Table subquery: trả về bảng (nhiều dòng, cột) — dùng trong FROM. Correlated subquery: tham chiếu ngược lại outer query — chạy lại cho mỗi dòng outer, nên chậm nhất.

-- Scalar: 1 giá trị
SELECT name, salary, 
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- Table: dùng trong FROM
SELECT dept, avg_sal FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY department
) sub WHERE avg_sal > 50000;

-- Correlated: tham chiếu outer query
SELECT e.name, e.salary FROM employees e
WHERE salary > (
    SELECT AVG(salary) FROM employees e2 
    WHERE e2.department = e.department  -- Correlated!
);

3. CTE (Common Table Expression) — WITH Clause #

CTE dùng WITH...AS để đặt tên cho subquery, giúp code dễ đọc hơn nhiều. CTE KHÔNG nhanh hơn subquery (hầu hết databases tối ưu giống nhau) nhưng CODE SẠCH HƠN rất nhiều, có thể tham chiếu nhiều lần.

-- CTE thay cho subquery lồng nhau
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_earners AS (
    SELECT e.*, d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.department = d.department
    WHERE e.salary > d.avg_salary * 1.5
)
SELECT * FROM high_earners ORDER BY salary DESC;

4. Recursive CTE — Truy Vấn Đệ Quy #

Recursive CTE xử lý dữ liệu phân cấp (hierarchical): cây tổ chức, danh mục lồng, BOM. Cấu trúc: anchor member (base case) + UNION ALL + recursive member.

-- Cây tổ chức: tìm tất cả subordinates của CEO
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL  -- CEO
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

5. EXISTS Vs IN — Subquery Trong WHERE #

EXISTS kiểm tra subquery có trả về dòng nào không (true/false). IN kiểm tra giá trị có nằm trong tập kết quả không. EXISTS thường nhanh hơn với bảng lớn vì dừng ngay khi tìm thấy 1 match.

-- EXISTS (thường nhanh hơn)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- IN (dễ đọc hơn)
SELECT * FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

6. Performance Tips #

1) Tránh correlated subquery nếu có thể — dùng JOIN thay thế. 2) CTE chỉ là syntactic sugar, không phải materialized view. 3) Dùng EXISTS thay IN khi outer table lớn. 4) Index cột join/where để tăng tốc subquery. 5) Dùng EXPLAIN ANALYZE để check execution plan.

7. Kết Luận #

Subquery và CTE là công cụ thiết yếu để viết SQL phức tạp. CTE giúp code dễ đọc, Recursive CTE xử lý dữ liệu phân cấp, EXISTS/IN cho conditional filtering. Quy tắc vàng: nếu query có > 2 level subquery lồng → refactor thành CTE.