Common Table Expression (CTE) là gì?

Common Table Expression (CTE) là một tập kết quả tạm thời được định nghĩa trong phạm vi thực thi của một câu lệnh đơn lẻ như SELECT, INSERT, UPDATE hoặc DELETE. CTE giúp mã nguồn SQL trở nên sạch sẽ, dễ đọc hơn và đặc biệt mạnh mẽ khi xử lý các cấu trúc dữ liệu phân cấp thông qua truy vấn đệ quy.

Cấu trúc cơ bản của một CTE bao gồm ba phần chính:

• Tên của CTE (đặt ngay sau từ khóa WITH).
• Danh sách các cột (tùy chọn).
• Một câu lệnh SELECT định nghĩa nội dung cho CTE (nằm trong khối AS).

Một trong những ưu điểm lớn nhất của CTE là khả năng tham chiếu nhiều lần trong câu lệnh thực thi ngay sau khi nó được định nghĩa.

WITH CTE_Name (Column1, Column2)
AS
(
    -- Truy vấn định nghĩa CTE
    SELECT Col1, Col2 FROM TableName
)
SELECT * FROM CTE_Name;

CTE không đệ quy (Non-Recursive CTE)

CTE không đệ quy hoạt động tương tự như một view tạm thời hoặc một subquery. Nó thường được sử dụng để tách biệt logic truy vấn phức tạp hoặc hỗ trợ phân trang dữ liệu.

Ví dụ về việc sử dụng CTE để phân trang bằng hàm ROW_NUMBER():

WITH PagedData AS
(
    SELECT 
        EmployeeID, 
        FullName, 
        DepartmentID,
        ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
SELECT * FROM PagedData 
WHERE RowNum BETWEEN 11 AND 20;

Truy vấn đệ quy với CTE (Recursive CTE)

Truy vấn đệ quy là trường hợp CTE tự tham chiếu đến chính nó trong phần định nghĩa. Kỹ thuật này thường được áp dụng để xử lý các bảng có quan hệ cha-con (Self-referencing table), chẳng hạn như sơ đồ tổ chức hoặc danh mục sản phẩm đa cấp.

Giả sử chúng ta có cấu trúc bảng lưu trữ các phòng ban như sau:

CREATE TABLE Organization (
    OrgID INT PRIMARY KEY,
    OrgName NVARCHAR(100),
    ParentID INT
);

Để truy xuất toàn bộ các phòng ban con từ một phòng ban gốc cụ thể, ta sử dụng CTE đệ quy:

WITH OrgTree AS
(
    -- Anchor Member: Thành phần điểm neo (truy vấn cơ sở)
    SELECT OrgID, OrgName, ParentID, 0 AS Level
    FROM Organization
    WHERE OrgName = 'General Office'

    UNION ALL

    -- Recursive Member: Thành phần đệ quy tham chiếu lại OrgTree
    SELECT o.OrgID, o.OrgName, o.ParentID, ot.Level + 1
    FROM Organization o
    INNER JOIN OrgTree ot ON o.ParentID = ot.OrgID
)
SELECT OrgID, OrgName, ParentID, Level FROM OrgTree;

Cơ chế thực thi và giới hạn

Một truy vấn đệ quy CTE bao gồm hai thành phần logic chính:

• Anchor Member: Trả về tập hợp kết quả đầu tiên dùng làm nền tảng cho quá trình đệ quy.
• Recursive Member: Liên kết với chính CTE đó thông qua phép JOIN để tìm các cấp tiếp theo.

Quá trình đệ quy sẽ tự động dừng lại khi một trong hai điều kiện sau xảy ra:

1. Truy vấn đệ quy không còn trả về bất kỳ dòng dữ liệu nào.
2. Vượt quá giới hạn số lần đệ quy cho phép của hệ thống.

Để kiểm soát hiệu năng và tránh vòng lặp vô hạn, bạn có thể sử dụng gợi ý MAXRECURSION để giới hạn số lần lặp tối đa của truy vấn.