[SQL] - Derived Tables trong MySQL

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

Trong bài viết hôm nay mình sẽ cùng anh em tìm hiểu về Derived Table - Bảng dẫn xuất trong SQL nói chung và cách triển khai nó trong MySQL sẽ như thế nào nhé.

1/ Derived Tables là gì?

Bản chất derived table cũng là một bảng tạm giống như temporary table. Nhưng việc sử dụng derived table sẽ đơn giản hơn nhiều so với temporary table.

Nguyên nhân vì khi sử dụng temporary table thì anh em phải tạo ra nó trước. Còn với derived table thì nó là kết quả từ một SELECT nên anh em có thể alias rồi dùng như một subquery.

Nói cách khác thì derived table và subquery bản chất là một và khi anh em sử dụng một subquery trong mệnh đề FROM của một câu SELECT thì nó chính là derived table.

Cú pháp tổng quát của derived table như sau:

SELECT column_list
FROM (
	SELECT column_list
    FROM table_1
) derived_table_name
WHERE derived_table_name.c1 > 0;

Note:

- Nếu subquery có thể được thực thi một cách độc lập mà không phụ thuộc và câu query bên ngoài chứa nó thì derived table bắt buộc phải được alias để chúng ta có thể tham chiếu tới nó.

- Nếu anh em không alias thì MySQL sẽ báo lỗi là: "Every derived table must have its own alias".

2/ Ví dụ.

Bây giờ mình sẽ cùng anh em làm một vài ví dụ để hiểu hơn về cách chúng ta triển khai một derived table cũng như sử dụng derived table trong tình huống nào nhé.

Về phần dữ liệu mẫu trong bài viết anh em có thể download tại đây.

- Trong ví dụ đầu tiên này mình mong muốn lấy được thông tin của 5 sản phẩm có doanh thu cao nhất trong cơ sở dữ liệu.

- Kết quả trả ra sẽ là tên sản phẩm (productName) và doanh thu (revenue) tương ứng của 5 sản phẩm đó.

Okay, phân tích một chút có thể thấy. Nếu muốn lấy được doanh thu chúng ta phải sử dụng hai bảng orders và orderdetails (thực chất chỉ cần bảng orderdetails nếu anh em không cần các thông tin về orderDate, requiredDate hay shippedDate từ bảng orders)

Sau đó nếu muốn lấy được thông tin sản phẩm thì phải dựa vào bảng products (products và orderdetails liên kết thông qua trường productCode)

Đầu tiên là lấy ra 5 sản phẩm có doanh thu cao nhất:

SELECT 
    productCode, 
    SUM(quantityOrdered * priceEach) revenue
FROM
    orderdetails
GROUP BY productCode
ORDER BY revenue DESC
LIMIT 5;

Output như sau:

productCode|revenue  |
-----------+---------+
S18_3232   |276839.98|
S12_1108   |190755.86|
S10_1949   |190017.96|
S10_4698   |170686.00|
S12_1099   |161531.48|

Đến đây nếu thực hiện JOIN tiếp với bảng products chúng ta đã hoàn toàn có thể lấy được thông tin tên sản phẩm rồi.

SELECT 
	p.productName, 
    p.productCode, 
    SUM(od.quantityOrdered * od.priceEach) revenue
FROM orderdetails od
    JOIN products p USING(productCode)
GROUP BY productCode
ORDER BY revenue DESC
LIMIT 5;

Và output sẽ là:

productName                         |productCode|revenue  |
------------------------------------+-----------+---------+
1992 Ferrari 360 Spider red         |S18_3232   |276839.98|
2001 Ferrari Enzo                   |S12_1108   |190755.86|
1952 Alpine Renault 1300            |S10_1949   |190017.96|
2003 Harley-Davidson Eagle Drag Bike|S10_4698   |170686.00|
1968 Ford Mustang                   |S12_1099   |161531.48|

Nhưng nếu sử dụng khái niệm derived table vào đây thì sao? Anh em xem thử đoạn code sau nhé.

SELECT 
    p.productName,
    top.productcode,
    top.revenue
FROM (
	SELECT 
        productCode, 
        SUM(quantityOrdered * priceEach) revenue
    FROM orderdetails
    GROUP BY productCode
    ORDER BY revenue DESC
    LIMIT 5
) top
JOIN products p USING (productCode);

Về mặt kết quả thì hoàn toàn giống bên trên nhưng liệu về hiệu năng thì sao? Sử dụng derived table liệu có giúp chúng ta tăng hiệu năng câu query? 

Ở đây mình đã chụp lại execution plan của hai câu query như sau:

 - Sử dụng derived table.

- Sử dụng JOIN với bảng products.

Anh em có thể thấy so với việc sử dụng JOIN thì sử dụng derived table trong trường hợp này có chi phí query thấp hơn rất nhiều.

Nguyên nhân là do derived table sau khi được tạo ra thì số lượng bản ghi đã ít đi rất nhiều. Nếu đem JOIN với bảng products thì rõ ràng sẽ nhanh hơn so với việc chúng ta JOIN trực tiếp hai bảng orderdetails và products.

3/ Lời kết.

Hi vọng qua bài viết này anh em có hiểu hơn về cách chúng ta tạo cũng như sử dụng một  derived table trong MySQL nói riêng và các hệ quản trị cơ sở dữ liệu khác nói chung.

Anh em có thể đọc thêm về CTE - cũng là một cách tạo bảng bảng tạm rất hay tại đây.

Hẹn gặp lại anh em trong các bài viết tiếp theo.

Tham khảo:

https://www.mysqltutorial.org/mysql-derived-table/

Thanks all ❤️❤️❤️