SQL - Biểu thức bảng thông thường (CTE)

Xin chào các bạn, những pháp sư SQL tương lai! Hôm nay, chúng ta sẽ bắt đầu một hành trình thú vị vào thế giới của Biểu thức Bảng Thường (CTE). Đừng lo lắng nếu bạn mới bắt đầu học lập trình - tôi sẽ hướng dẫn bạn từng bước, giống như tôi đã làm cho hàng trăm sinh viên trong những năm dạy học của mình. Vậy hãy chuẩn bị đồ uống yêu thích của bạn, ngồi thoải mái, và cùng nhau lặn sâu vào!

Biểu thức bảng thông thường trong SQL

Hãy tưởng tượng bạn đang tổ chức một bữa tiệc lớn (vì ai lại không yêu thích một ví dụ tốt?). Bạn có một danh sách các nhiệm vụ và muốn chia nhỏ chúng thành những phần nhỏ hơn, dễ quản lý hơn. Đó chính là điều mà Biểu thức Bảng Thường (CTE) làm trong SQL - nó giúp chúng ta chia nhỏ các truy vấn phức tạp thành những phần đơn giản hơn, dễ đọc hơn.

Một CTE là như một tập kết quả tạm thời có tên mà bạn có thể tham chiếu trong một SELECT, INSERT, UPDATE, DELETE hoặc MERGE. Nó được định nghĩa trong phạm vi thực thi của một câu lệnh duy nhất. Hãy nghĩ về nó như việc tạo một bảng tạm thời chỉ tồn tại cho truy vấn của bạn.

Hãy xem một ví dụ đơn giản:

WITH cte_example AS (
SELECT 'Hello, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

Trong ví dụ này:

1. Chúng ta bắt đầu với từ khóa WITH, báo hiệu sự bắt đầu của CTE.
2. Chúng ta đặt tên cho CTE của mình: cte_example.
3. Chúng ta định nghĩa nội dung của CTE: trong trường hợp này, một truy vấn SELECT đơn giản tạo ra một cột名叫 'greeting' với giá trị 'Hello, CTE!'.
4. Sau khi định nghĩa CTE, chúng ta có truy vấn chính sử dụng CTE.

Khi bạn chạy truy vấn này, bạn sẽ thấy:

| greeting    |
|-------------|
| Hello, CTE! |

Có phải đó là điều tuyệt vời? Chúng ta vừa tạo ra CTE đầu tiên của mình!

Câu lệnh WITH trong MySQL

Bây giờ, hãy nói về câu lệnh WITH trong MySQL. Đó là pháp thuật giúp CTE của chúng ta sống động. Cú pháp tổng quát trông như thế này:

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

Dưới đây là một ví dụ cụ thể hơn. Giả sử chúng ta có một bảng nhân viên:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

Bây giờ, hãy sử dụng một CTE để tìm mức lương trung bình theo phòng ban:

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

Truy vấn này sẽ cho chúng ta:

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Finance    | 55000.00   |
| HR         | 51000.00   |

Ở đây, chúng ta đã sử dụng một CTE để tính toán mức lương trung bình cho mỗi phòng ban, sau đó chúng ta chọn từ CTE này để hiển thị kết quả.

CTE từ nhiều bảng

CTE không chỉ giới hạn ở một bảng duy nhất. Chúng ta có thể sử dụng nhiều bảng trong định nghĩa CTE, giống như trong các truy vấn thông thường. Hãy thêm một bảng phòng ban vào ví dụ của chúng ta:

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

Bây giờ, hãy sử dụng một CTE để kết hợp thông tin từ cả hai bảng:

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

Điều này sẽ cho chúng ta:

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | San Francisco |
| Finance    | 55000.00   | Chicago       |
| HR         | 51000.00   | New York      |

Trong ví dụ này, CTE của chúng ta kết hợp bảng nhân viên và phòng ban, tính toán mức lương trung bình và bao gồm thông tin về địa điểm.

CTE đệ quy

Bây giờ, hãy chuyển sang phần thú vị nhất - CTE đệ quy! Những CTE này giống như những con búp bê Nga嵌套 doll. Một CTE đệ quy tham chiếu đến chính nó, cho phép bạn làm việc với dữ liệu phân cấp hoặc cấu trúc cây.

Hãy tạo một ví dụ đơn giản về hierarchies nhân viên:

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

Bây giờ, hãy sử dụng một CTE đệ quy để hiển thị toàn bộ hierarchies:

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

Truy vấn này sẽ tạo ra:

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

CTE đệ quy này bắt đầu từ nhân viên cấp cao nhất (Big Boss) và sau đó đệ quy để tìm tất cả nhân viên báo cáo cho mỗi quản lý.

Lợi ích của CTE

CTE mang lại nhiều lợi ích:

Lợi ích	Mô tả
Đọcability	CTE giúp làm cho các truy vấn phức tạp dễ đọc hơn bằng cách chia nhỏ chúng thành các truy vấn con có tên.
Tái sử dụng	Bạn có thể tham chiếu đến một CTE nhiều lần trong một truy vấn.
Đệ quy	CTE cho phép bạn viết các truy vấn đệ quy, rất hữu ích cho dữ liệu phân cấp.
Đơn giản hóa	Chúng có thể đơn giản hóa các kết hợp phức tạp và truy vấn con.
Dễ bảo trì	CTE giúp làm cho các truy vấn dễ bảo trì và修改.

Nhược điểm của CTE

Mặc dù CTE rất mạnh mẽ, chúng cũng có một số hạn chế:

Nhược điểm	Mô tả
Hiệu suất	Trong một số trường hợp, CTE có thể không hiệu suất tốt như các bảng con hoặc các view.
Phạm vi	CTE chỉ hợp lệ trong phạm vi của câu lệnh duy nhất mà chúng được định nghĩa.
Độ phức tạp	Đối với các truy vấn đơn giản, việc sử dụng CTE có thể thêm độ phức tạp không cần thiết.
Hỗ trợ cơ sở dữ liệu	Không phải tất cả các hệ thống cơ sở dữ liệu đều hỗ trợ CTE, mặc dù hầu hết các hệ thống hiện đại đều có.

Và đó là tất cả, các bạn! Chúng ta đã cùng nhau hành trình qua thế giới của Biểu thức Bảng Thường, từ khái niệm cơ bản đến các truy vấn đệ quy. Nhớ rằng, như với bất kỳ kỹ năng mới nào, việc thành thạo CTE đòi hỏi sự thực hành. Vậy đừng ngần ngại thử nghiệm chúng trong các truy vấn của riêng bạn. Trước khi bạn biết điều đó, bạn sẽ viết CTE như một chuyên gia, làm hài lòng đồng nghiệp và làm cho các truy vấn cơ sở dữ liệu của bạn sạch sẽ và hiệu quả hơn. Chúc các bạn thành công trong việc truy vấn!

Credits: Image by storyset