Mệnh đề WITH — Phân tầng truy vấn bằng kết quả trung gian có tên

Dữ liệu dùng trong bài này — employee và department

CTE (Common Table Expression, biểu thức bảng chung) là cơ chế cho phép bạn viết WITH tên AS (SELECT ...) để đặt tên cho một truy vấn con, rồi tham chiếu tên đó trong các truy vấn theo sau.

Vì bạn có thể tách một truy vấn con lồng nhau sâu thành nhiều tầng, mã của bạn trở nên dễ đọc hơn nhiều.

Tài liệu là bảng employee (30 hàng; cột manager_id trỏ tới emp_id của quản lý, một cấu trúc tự tham chiếu) và bảng department (6 hàng).

Bạn sẽ làm các bài tập dùng CTE để viết các phép tổng hợp nhiều tầng dễ đọc hơn.

Trước khi bắt đầu các bài tập, hãy xem qua định nghĩa cột và dữ liệu mẫu của hai bảng mà bài này dùng — employee và department.

① Chạy PRAGMA table_info(employee); và PRAGMA table_info(department); để kiểm tra định nghĩa cột của cả hai bảng.

② Chạy SELECT * FROM employee LIMIT 5; và SELECT * FROM department LIMIT 5; để xem trước 5 hàng đầu của mỗi bảng. Cũng hãy quan sát cách NULL xuất hiện ở cột manager_id (nhân viên không có quản lý).

Tạo kết quả trung gian có tên bằng WITH — làm cho truy vấn nhiều tầng dễ đọc

Khi bạn viết WITH tên AS (SELECT ...), kết quả của SELECT bên trong nhận một cái tên, và bạn có thể tham chiếu nó từ FROM hoặc JOIN trong truy vấn chính theo sau.

Thay vì viết quy trình hai bước "trước tiên dựng kết quả trung gian, rồi dùng nó để tạo kết quả cuối" dưới dạng truy vấn con lồng nhau, bạn có thể chia thành dạng đọc từ trên xuống dưới.

CTE là tên tạm thời, biến mất ngay khi truy vấn chính kết thúc — nó không tạo bảng thật.

Cú pháp là WITH tên AS ( ... ) SELECT ... FROM tên ...;.

-- Gom các nhân viên lương cao vào CTE high_earner,
-- rồi nối với department để gắn tên phòng ban
WITH high_earner AS (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
)
SELECT h.name, h.salary, d.dept_name
FROM high_earner h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

Trong đoạn mã trên, tên high_earner cắt ra kết quả trung gian "các nhân viên có salary >= 7,000,000", và truy vấn chính tham chiếu nó như một bảng bằng FROM high_earner.

Dòng chảy đó nhìn dưới dạng sơ đồ như sau.

Bạn có thể viết cùng xử lý đó bằng truy vấn con (một SELECT lồng trong dấu ngoặc của một SELECT khác).

Hãy viết lại phiên bản WITH trước đó dưới dạng truy vấn con thay vì CTE.

-- Cùng xử lý viết dưới dạng truy vấn con (bảng dẫn xuất)
-- Một SELECT nằm trong dấu ngoặc của FROM, nên bạn đọc ngoài → trong → ngoài
SELECT h.name, h.salary, d.dept_name
FROM (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
) AS h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

Kết quả giống hệt phiên bản WITH, nhưng với phiên bản truy vấn con bạn phải đi sâu vào một SELECT lồng trong FROM để hiểu SELECT bên trong đang làm gì.

Phiên bản WITH cắt kết quả trung gian ra với tên `high_earner`, nên hai tầng "① lọc nhân viên lương cao → ② gắn tên phòng ban" đọc thẳng từ trên xuống dưới.

Khoảng cách càng lớn khi bạn thêm nhiều tầng trung gian — khi đã có 3, 4 tầng, các truy vấn con lồng sâu trở nên khó theo dõi.

Yêu cầu: "Tôi muốn cắt ra các nhân viên không có quản lý (nhân viên cấp điều hành với manager_id = NULL) và liệt kê họ cùng với tên phòng ban." Hãy giải bằng hai tầng với một CTE. (Nếu bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Với WITH, định nghĩa một CTE có tên executive lấy các hàng từ employee nơi manager_id là NULL (emp_id, name, dept_id).

② LEFT JOIN CTE đó với department trên dept_id, và trả về name, dept_id, dept_name.

③ Sắp xếp kết quả theo name tăng dần.

Nối nhiều mệnh đề WITH — chia tổng hợp nhiều tầng thành từng bước một

Sau WITH, bạn có thể liệt kê nhiều CTE ngăn cách bằng dấu phẩy.

Dạng là WITH a AS (...), b AS (...) SELECT ..., và một CTE đứng sau có thể tham chiếu CTE đứng trước.

Điều này cho phép bạn chia một phép tổng hợp nhiều tầng ("tính trung bình theo phòng ban" → "chọn nhân viên trên mức trung bình đó" → "gắn tên phòng ban") thành các tầng có tên, từng bước một, để ngay cả trong một truy vấn dài, bạn vẫn giữ được vai trò của từng tầng tách biệt.

-- ① dept_avg: lương trung bình theo phòng ban
-- ② top_earner: nhân viên trên mức trung bình của phòng mình
-- ③ Truy vấn chính: gắn tên phòng ban và xuất kết quả
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
top_earner AS (
  SELECT e.name, e.dept_id, e.salary
  FROM employee e
  JOIN dept_avg da ON e.dept_id = da.dept_id
  WHERE e.salary > da.avg_salary
)
SELECT d.dept_name, t.name, t.salary
FROM top_earner t
JOIN department d ON t.dept_id = d.dept_id
ORDER BY d.dept_name, t.salary DESC;

Yêu cầu: "Tôi muốn tính lương trung bình của từng phòng, rồi liệt kê chỉ những nhân viên có lương vượt mức trung bình đó, kèm theo tên phòng ban." Hãy giải bằng cách nối hai CTE.

① Với CTE đầu tiên, tổng hợp employee theo dept_id và tạo ra lương trung bình theo phòng. Loại các nhân viên có dept_id là NULL khỏi phép trung bình.

② Với CTE thứ hai, nối employee với CTE đầu tiên trên dept_id và chọn các nhân viên (name, dept_id, salary) có salary lớn hơn mức trung bình của phòng đó.

③ Trong truy vấn chính, nối CTE thứ hai với department trên dept_id và trả về dept_name, name, salary. Sắp xếp theo tên phòng tăng dần, và trong cùng một phòng thì theo lương giảm dần.