Làm quen với CTE: WITH

Trong lập trình, bạn có thể tách riêng một đoạn code và đặt tên cho nó — tạo function. CTE cũng vậy thôi. Bạn có thể tách SELECT-subquery ra khỏi query chính, đặt tên cho nó rồi dùng lại trong SQL query chính.

CTE (Common Table Expressions, hay còn gọi là biểu thức bảng chung) — giống như một làn gió mới cho dev nào mệt mỏi vì subquery lồng nhau. Nó làm cho code SQL không chỉ dễ hiểu mà còn cực kỳ "ngầu". Nếu trước đây bạn phải vật lộn với những đống subquery rối rắm và hoa mắt — thì giờ là lúc làm quen với "ma thuật" CTE.

Hãy tưởng tượng bạn đang xây nhà. Bình thường thì ai cũng muốn lắp cửa sổ, gắn cửa (tức là quăng subquery vào liền), dù tường còn chưa xong. Nhưng với CTE thì khác: đầu tiên bạn phác thảo bản nháp gọn gàng — tạo bảng tạm, giống như lên layout cho căn nhà. Sau đó, từng bước một, bạn xây tầng cho query. Đẹp, chắc chắn, kỹ thuật.

Bản chất, CTE — là bảng ảo mà bạn tạo ra ngay lập tức bằng SELECT-query. Kiểu như subquery, nhưng xịn hơn. Nếu trong lập trình bạn có thể tách logic ra thành function với tên rõ ràng, thì trong SQL, CTE đảm nhận vai trò đó. Bạn viết SELECT, đặt tên cho nó — rồi dùng nó như một phần của query lớn và phức tạp. Đẹp không? Quá đẹp luôn.

Ví dụ SQL-query với subquery:

-- truy vấn chính
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- subquery, được đặt bí danh filtered_students

Tách subquery ra riêng:

-- CTE/subquery, được đặt bí danh filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- truy vấn chính
SELECT *
FROM filtered_students;

Ngạc nhiên chưa, subquery xuất hiện trước CTE tận 20 năm! Chuẩn SQL-89 đã có subquery, còn CTE chỉ xuất hiện từ chuẩn SQL-2009 thôi.

Cú pháp WITH

CTE bắt đầu bằng từ khóa WITH và trông như này:

WITH cte_name AS (
    SELECT ... -- query của bạn ở đây
)

SELECT ...
FROM cte_name;

Ở đây:

• cte_name — là tên CTE của bạn. Bạn có thể đặt bất kỳ tên nào có ý nghĩa, ví dụ high_scores, filtered_data hoặc best_students.
• Bên trong dấu ngoặc () là query chuẩn bị dữ liệu để dùng tiếp theo.
• Sau khi định nghĩa CTE, bạn có thể dùng nó như một bảng bình thường trong query chính.

Ví dụ 1: CTE đơn giản

Cùng xem CTE hoạt động thế nào qua ví dụ thực tế. Giả sử bạn có bảng students — danh sách sinh viên với điểm số của họ:

Mục tiêu của chúng ta — chọn tất cả sinh viên có điểm trên 85 và in ra dữ liệu của họ.

Cách không dùng CTE:

Có thể làm bằng subquery:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

Còn đây là cách dùng CTE — nhìn thích mắt hơn hẳn:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Công nhận là nhìn sạch và dễ hiểu hơn hẳn. Ta tách rõ phần chuẩn bị dữ liệu (WITH) khỏi phần chính của query (SELECT). Kiểu như dọn bàn cho gọn rồi mới bắt đầu làm việc — thở cái là thấy nhẹ người liền.

Ví dụ 2: Nhiều CTE

Bạn có thể định nghĩa nhiều CTE trong một query. Cực kỳ hữu ích nếu cần chuẩn bị dữ liệu theo từng bước.

Cho: bảng grades, lưu điểm của sinh viên theo từng khóa học:

Bài toán: với mỗi sinh viên, tìm điểm trung bình, rồi chọn những ai có điểm này trên 85.

Giải bằng nhiều CTE:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- tham chiếu đến CTE đầu tiên - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- tham chiếu đến CTE thứ hai - high_achievers

Ở đây:

1. student_averages chuẩn bị dữ liệu tạm — điểm trung bình của sinh viên.
2. high_achievers dùng dữ liệu đó để chọn những ai có điểm trên 85.

Khác biệt giữa CTE và subquery

Spoiler: CTE không thay thế subquery, nhưng nhiều trường hợp dùng tiện hơn nhiều.

Subquery — là query nằm trong query. Dùng nhanh thì ổn, nhưng nhiều quá là code thành mớ hỗn độn ngay.

Ví dụ:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Subquery có thể nằm trong SELECT, trong FROM, trong WHERE và trong HAVING. Ngoài ra, nó còn có thể tham chiếu đến cột của query ngoài. CTE thì hơi khó với vụ này.

Ngược lại, CTE giúp code dễ đọc hơn nhiều, nên dễ bảo trì và ít lỗi hơn. Thay vì lồng query này vào query khác, CTE cho phép bạn "đặt tên" cho kết quả subquery và dùng lại sau đó.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTE đặc biệt hữu ích nếu bạn cần dùng dữ liệu đã chuẩn bị nhiều lần trong cùng một query.

Khi nào nên dùng CTE?

• Khi bạn muốn chia nhỏ query phức tạp thành nhiều bước logic.
• Nếu query cần dễ đọc, dễ bảo trì. Không ai muốn mò vào đống code lồng nhau như mì spaghetti đâu.
• Để chuẩn bị dữ liệu tạm thời, chỉ dùng cho query hiện tại.

Ví dụ cuối: phân tích khóa học

Cùng tổng hợp lại những gì đã học:

1. Tìm sinh viên có điểm trung bình cao.
2. In ra tên và các khóa học mà họ đã đăng ký.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Lưu ý cách mọi thứ được tổ chức:

1. Đầu tiên chuẩn bị điểm trung bình.
2. Sau đó chọn ra sinh viên xuất sắc nhất.
3. Rồi liên kết họ với các khóa học.

Giờ thì bạn đã sẵn sàng dùng CTE để viết những SQL-query đẹp, dễ đọc và mạnh mẽ rồi đó.

Lên đường — làm project của riêng bạn thôi!