[SQL] - MySQL CTE (Common Table Expression)

Hế lô anh em ✌️✌️✌️

Hôm nay mình sẽ cùng anh em tìm hiểu về CTE (Common Table Expression) trong SQL để chúng ta có thể thực hiện các truy vấn phức tạp mà điển hình nhất là truy vấn đệ quy nhé.

Note: 

- Trong bài viết này mình sử dụng hệ quản trị cơ sở dữ liệu MySQL (ver 8.0.15). Anh em có thể sử dụng các hệ quản trị cơ sở dữ liệu khác có hỗ trợ CTE cũng được.

- MySQL từ phiên bản 8.0 trở đi mới hỗ trợ CTE nên nếu anh em đang cài các phiên bản thấp hơn thì nhớ update để thực hiện được chức năng này nha.

1/ CTE (Common Table Expression) là gì?

Cho anh em nào chưa biết thì CTE thực chất là một bảng tạm được đặt tên và lưu trong bộ nhớ tạm. 

So sánh một chút với derived table (bảng dẫn xuất) thì CTE cũng không được lưu dưới dạng object mà chỉ tồn tại trong quá trình thực thi câu lệnh truy vấn như SELECT, UPDATE, DELETE...

Nhưng có một điểm khác so với derived table đó là CTE có thể tự tham chiếu tới nó (recursive CTE) hoặc có thể tham chiếu nhiều lần trong cùng một câu query.

Về ưu điểm của CTE (Common Table Express) thì nó dễ đọc hơn và hiệu năng tốt hơn so với derived table.

2/ Cú pháp (Syntax)

Cú pháp của CTE bao gồm 3 phần:

- Tên của CTE đó | cte_name.

- Danh sách các trường dữ liệu | column list (optional).

- Một câu query định nghĩa cho CTE đó | query

Cụ thể cú pháp của một CTE như sau:

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

Note:

- Sau khi CTE được đinh nghĩa anh em có thể sử dụng nó như là một View trong các câu lệnh SELECT, INSERT, UPDATE, DELETE...

- Ngoài ra khi anh em thêm danh sách các trường dữ liệu (column list) thì lưu ý số lượng phải giống với trong câu query. Nếu không thêm thì mặc định CTE sẽ chọn các cột này từ câu query để định nghĩa column list của CTE.

3/ Ví dụ

Bây giờ mình sẽ đi vào ví dụ để anh em dễ hình dung hơn nhé. Về dữ liệu thì anh em có thể tải database mẫu tại đây.

Ví dụ 1

Ở đây mình sẽ sử dụng bảng customers để làm ví dụ, cấu trúc bảng như sau:

Trong bảng sẽ có hai trường là country và state. Mình mong muốn lấy những state có tên "CA" của nước Mỹ (USA) thì làm thế nào?

Bình thường các bạn chỉ cần chạy câu SELECT với lệnh AND là xong!

SELECT 
	customerName 
FROM customers 
WHERE country = 'USA' 
AND state = 'CA' 
ORDER BY customerName;

Nhưng nếu sử dụng CTE thì sao? Các bạn hãy xem đoạn code bên dưới xem có dài hơn không?

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM customers
    WHERE country = 'USA'
) SELECT 
    customerName
 FROM customers_in_usa
 WHERE state = 'CA'
 ORDER BY customerName;

Kết quả của cả hai đoạn code trên là hoàn toàn giống nhau.

customerName                |
----------------------------+
Boards & Toys Co.           |
Collectable Mini Designs Co.|
Corporate Gift Ideas Co.    |
Men 'R' US Retailers, Ltd.  |
Mini Gifts Distributors Ltd.|
Mini Wheels Co.             |
Signal Collectibles Ltd.    |
Technics Stores Inc.        |
The Sharp Gifts Warehouse   |
Toys4GrownUps.com           |
West Coast Collectables Co. |

Okay, như anh em thấy thì dùng CTE sẽ dài hơn. Nhưng đây chỉ là một một ví dụ đơn giản để anh em hiểu được cách thức hoạt động của CTE thôi. 

Bây giờ mình sẽ giải thích quá trình thực thi đoạn code trên như sau.

- Đầu tiên thì tên của CTE chính là customers_in_usa. Câu query định nghĩa CTE trả về hai trường là customerName và state.

- Lúc này customers_in_usa CTE trả về tất cả customerName và state của nước Mỹ (USA)

- Sau khi customers_in_usa CTE được định nghĩa thì chúng ta tham chiếu tới chính nó rồi thông qua câu lệnh SELECT để chọn ra những khách hàng ở bang Califonia (CA)

Ví dụ 2

Bây giờ mình sẽ cùng anh em qua ví dụ phức tạp hơn một chút. 

Ở đây mình sẽ dùng 4 bảng là: orders, orderdetails, customers và employees với mục đích lấy ra thông tin của 5 nhân viên có thành tích bán hàng tốt nhất.

Thế nào là thành tích tốt nhất? Đó là với mỗi nhân viên bán hàng chúng ta sẽ tính tổng số tiền họ bán được (SUM (quantityOrdered * priceEach) - trong bảng orderdetails).

SELECT 
    salesRepEmployeeNumber,
    SUM(quantityOrdered * priceEach) sales
FROM
    orders INNER JOIN
    orderdetails USING (orderNumber) INNER JOIN
    customers USING (customerNumber)
GROUP BY salesRepEmployeeNumber
ORDER BY sales DESC
LIMIT 5

Okay, có vẻ đơn giản nhỉ! Chúng ta chỉ cần JOIN 3 bảng orders, orderdetails, customers lại rồi GROUP BY theo salesRepEmployeeNumber như trên là xong.

Output sẽ như sau:

employeeNumber|sales     |
--------------+----------+
          1370|1258577.81|
          1165|1081530.54|
          1401| 868220.55|
          1501| 732096.79|
          1504| 704853.91|

Câu hỏi là bây giờ chúng ta phải dựa vào dữ liệu bên trên để lấy ra được thông tin của nhân viên đó. Việc đầu tiên chúng ta nghĩ đến là JOIN với bảng employees. 

Nhưng JOIN thế nào khi đó là kết quả trả về từ một câu query? Vậy tại sao không biến nó thành một CTE rồi JOIN như sau.

WITH topsales AS (
    SELECT 
        salesRepEmployeeNumber employeeNumber,
        SUM(quantityOrdered * priceEach) sales
    FROM
        orders INNER JOIN
        orderdetails USING (orderNumber) INNER JOIN
        customers USING (customerNumber)
    GROUP BY employeeNumber
    ORDER BY sales DESC
    LIMIT 5
)
SELECT 
    employeeNumber, 
    firstName, 
    lastName, 
    sales
FROM employees emp JOIN topsales top ON emp.employeeId = top.employeeNumber;

Output sẽ như sau:

employeeNumber|firstName|lastName |sales     |
--------------+---------+---------+----------+
          1370|Gerard   |Hernandez|1258577.81|
          1165|Leslie   |Jennings |1081530.54|
          1401|Pamela   |Castillo | 868220.55|
          1501|Larry    |Bott     | 732096.79|
          1504|Barry    |Jones    | 704853.91|

Ở đây mình đã đẩy kết quả trả về vào một CTE sau đó dùng chính CTE đó JOIN với bảng employees để lấy thêm các thông tin cần thiết.

4/ Lời kết

Hi vọng qua bài này sẽ giúp anh em hiểu hơn về CTE (Common Table Expression) trong SQL nói chung và trong MySQL nói riêng.

Ngoài ra anh em có thể tham khảo thêm một số bài toán có sử dụng CTE tại đây. Hẹn gặp lại anh em trong các bài viết tiếp theo nhé!

Tham khảo:

https://www.mysqltutorial.org/mysql-cte/

Thanks all ❤️❤️❤️