Table Expression (Biểu thức bảng) là một công cụ mạnh mẽ trong SQL, đóng vai trò như một bảng ảo được định nghĩa từ một truy vấn SELECT. Việc sử dụng biểu thức bảng không trực tiếp làm tăng hiệu năng xử lý của hệ thống nhưng lại mang lại lợi ích to lớn về mặt logic, giúp mã nguồn trở nên sạch sẽ, mô-đun hóa và giải quyết được các hạn chế về thứ tự thực thi của các mệnh đề trong SQL.

Có bốn loại biểu thức bảng phổ biến bao gồm: Derived Tables (Bảng dẫn xuất), Common Table Expressions (CTE), Views và Inline Table-Valued Functions. Trong nội dung này, chúng ta sẽ tập trung vào hai loại linh hoạt nhất là Derived Tables và CTE.

1. Derived Tables (Bảng dẫn xuất)

Derived Table là một truy vấn con được định nghĩa ngay trong mệnh đề FROM của truy vấn bên ngoài. Ngay khi truy vấn chính kết thúc, bảng dẫn xuất này cũng sẽ bị hủy bỏ.

Để một Derived Table được coi là hợp lệ, nó phải tuân thủ ba nguyên tắc vàng:

• Phải có tên cột: Mọi cột được SELECT trong bảng dẫn xuất đều phải được đặt tên rõ ràng.
• Tên cột phải duy nhất: Không được phép có hai cột trùng tên trong cùng một bảng dẫn xuất.
• Không sử dụng ORDER BY: Trừ khi có sử dụng từ khóa TOP hoặc OFFSET-FETCH, nếu không ORDER BY sẽ không hợp lệ vì bảng trong mô hình quan hệ không có thứ tự dòng mặc định.

Cấu trúc cơ bản

SELECT *
FROM (
    SELECT MaNV, YEAR(NgayBan) AS NamBan
    FROM dbo.HoaDon
) AS BangTam;

Giải quyết hạn chế của bí danh (Alias)

Trong SQL, mệnh đề GROUP BY được thực thi trước SELECT, do đó bạn không thể sử dụng bí danh cột vừa tạo trong SELECT để nhóm dữ liệu. Derived Table sẽ giải quyết vấn đề này:

-- Truy vấn sau sẽ lỗi vì NamBan chưa tồn tại khi GROUP BY thực thi
-- SELECT YEAR(NgayBan) AS NamBan FROM dbo.HoaDon GROUP BY NamBan;

-- Giải pháp với Derived Table
SELECT NamBan, COUNT(*) AS SoLuongDon
FROM (
    SELECT YEAR(NgayBan) AS NamBan
    FROM dbo.HoaDon
) AS HoaDonTheoNam
GROUP BY NamBan;

Sử dụng tham số và lồng nhau

Bạn có thể truyền tham số vào trong Derived Table để lọc dữ liệu động. Ngoài ra, SQL cho phép lồng nhiều bảng dẫn xuất vào nhau, tuy nhiên việc lồng quá nhiều lớp sẽ khiến mã nguồn trở nên cực kỳ khó đọc và bảo trì.

DECLARE @MaNhanVien INT = 5;

SELECT NamBan, TongDoanhThu
FROM (
    SELECT NamBan, SUM(TriGia) AS TongDoanhThu
    FROM (
        SELECT YEAR(NgayBan) AS NamBan, TriGia
        FROM dbo.BanHang
        WHERE IDNhanVien = @MaNhanVien
    ) AS Buoc1
    GROUP BY NamBan
) AS Buoc2
WHERE TongDoanhThu > 10000;

2. Common Table Expressions (CTE)

CTE là một cải tiến so với Derived Table, giúp định nghĩa các bảng tạm thời với cú pháp minh bạch hơn. CTE được khai báo bằng từ khóa WITH và nằm trước truy vấn chính.

Ưu điểm của CTE so với Derived Table

• Tính đọc hiểu: Phân tách logic truy vấn thay vì lồng các khối mã vào nhau.
• Khả năng tái sử dụng: Có thể tham chiếu đến các CTE đã định nghĩa trước đó trong cùng một khối WITH.
• Đệ quy: CTE hỗ trợ truy vấn đệ quy (Recursive), điều mà Derived Table không làm được.

Định nghĩa nhiều CTE cùng lúc

Thay vì lồng nhau như ví dụ trước, chúng ta có thể viết lại bằng cách sử dụng các dấu phẩy để ngăn cách các CTE:

WITH DoanhSoChiTiet AS (
    SELECT YEAR(OrderDate) AS NamHD, CustomerID, TotalAmount
    FROM dbo.Orders
),
ThongKeTheoNam AS (
    SELECT NamHD, COUNT(DISTINCT CustomerID) AS SoKhachHang, SUM(TotalAmount) AS DoanhThu
    FROM DoanhSoChiTiet
    GROUP BY NamHD
)
SELECT NamHD, SoKhachHang, DoanhThu
FROM ThongKeTheoNam
WHERE DoanhThu > 50000;

Ứng dụng phân trang và xếp hạng

Một trong những ứng dụng phổ biến nhất của CTE là kết hợp với các hàm Window Functions như ROW_NUMBER() để thực hiện phân trang dữ liệu.

WITH DonHangPhanTrang AS (
    SELECT 
        OrderID, 
        OrderDate, 
        CustomerID,
        ROW_NUMBER() OVER (ORDER BY OrderDate DESC, OrderID ASC) AS SoThuTu
    FROM dbo.Orders
)
SELECT * 
FROM DonHangPhanTrang
WHERE SoThuTu BETWEEN 21 AND 30; -- Lấy dữ liệu ở trang thứ 3

Bài tập thực hành

Yêu cầu: Tìm đơn hàng có giá trị lớn nhất của mỗi nhân viên trong năm 2023 bằng cách sử dụng Derived Table.

SELECT T1.EmployeeID, T1.MaxVal, T2.OrderID
FROM (
    SELECT EmployeeID, MAX(TotalAmount) AS MaxVal
    FROM dbo.Orders
    WHERE YEAR(OrderDate) = 2023
    GROUP BY EmployeeID
) AS T1
INNER JOIN dbo.Orders AS T2 ON T1.EmployeeID = T2.EmployeeID 
    AND T1.MaxVal = T2.TotalAmount;

Việc nắm vững cách vận dụng Derived Tables và CTE sẽ giúp bạn xử lý các bài toán truy vấn phức tạp một cách có hệ thống, đồng thời tạo tiền đề tốt cho việc tối ưu hóa hiệu năng SQL Server ở các cấp độ chuyên sâu hơn.