CTE và Subquery trong SQL

CTE và Subquery trong SQL là gì? Khi nào cần dùng CTE hay subquery. Bài viết này SmartData sẽ giúp bạn hiểu rõ vấn đề đó.

Trong quá trình giảng dạy, khi được giới thiệu đến CTE (Common Table Expressions) và Subquery, đa phần các học viên đều có nhận định về CTE rằng : “Đó cũng đơn thuần là Subquery”. Để trả lời câu hỏi này, hãy nhìn vào cách mà bạn có thể làm với SQL Subquery và những lợi thế to lớn khi sử dụng CTE.

Vậy Subquery là gì ?

Subquery được hiểu nôm na là truy vấn con, là 1 câu truy vấn phụ của 1 câu truy vấn chính. Ví dụ sau đây sẽ giải thích rõ ràng nhất về subquery. Lưu ý, trong nội dung bài viết này ngôn ngữ truy vấn được sử dụng là SQL Sever nên sẽ có đôi chút khác biệt về cú pháp.

Ví dụ:

Giả sử rằng, chủ cửa hàng muốn tặng voucher discount để trí ân những khách hàng có sức mua lớn hơn sức mua trung bình của tất cả các khách hàng. Như vậy, câu truy vấn trả về đúng như đề bài trên là:

SELECT

  account_no,

  name

FROM customers

WHERE annual_purchases >

  (SELECT AVG(annual_purchases) FROM customers);

Ta có thể thấy, câu truy vấn chính tìm danh sách khách hàng và câu truy vấn phụ (subquery) tính ra sức mua trunh bình.

Còn CTE là gì ?

CTE là một/hoặc một chuỗi các câu truy vấn được đặt tên và được xác định trước mỗi đầu cầu truy vấn chính và sau đó, truy vấn chính có thể truy cập được chúng giống như truy cập các bảng. Giả sử, chúng ta cần so sánh mức lương của các nhân viên với mức lương trung bình cho vị trí đó. Câu truy vấn như sau:

WITH avg_salary AS (

  SELECT

    role,

    avg(salary) AS average

  FROM employee

  GROUP BY role

)

SELECT

  employee.role,

  name,

  salary,

  avg_salary

FROM employee

JOIN avg_salary ON avg_salary.role = employee.role

ORDER BY role, name

CTE được xác định bằng mệnh đề WITH AS và kết quả của nó được lưu trữ trong một bảng tạm thời. Trong ví dụ trên, kết quả của CTE được lưu trữ trong bảng avg_salary và được sử đụng bởi câu truy vấn chính để chọn ra mức lương trung bình cho từng vị trí.

Ở mức độ đơn giản, không có nhiều sự khác biệt giữa CTE và Subquery, nhưng ở những yêu cầu phức tạp, sử dụng từ 3 Subquery trở lên thì CTE sẽ phát huy được điểm mạnh của mình.

CTEs và Subqueries, điểm khác biệt nằm ở đâu ?

SmartData sẽ bắt đầu bằng những liệt kê ngắn gọn và sau đó sẽ đào sâu vài chi tiết quan trọng để có thể làm rõ hơn bản chất của sự khác biệt.

• CTEs được xác định trước mỗi câu truy vấn, trong khi đó Subqueries được xác định bên trong câu truy vấn.
• CTEs luôn được đặt tên, trong khi đó Subqueries chỉ phải đặt tên trong 1 vài trường hợp, đặc biệt là ở PostgreSQL việc đặt tên là bắt buộc
• CTEs có thể sử dụng để đệ quy.
• CTEs dễ đọc hơn Subqueries ở những câu truy vấn báo cáo phức tạp.
• Một CTE có thể được sử dụng nhiều lần trong câu truy vấn, trong khi Subquery chỉ có thể sử dụng 1 lần. Điều này giúp câu code SQL ngắn hơn.
• Subqueries có thể sử dụng trong mệnh đề WHERE kết hợp với từ khoá IN hoặc EXISTS, nhưng CTEs thì không được.
• Subqueries có thể lấy 1 phần dữ liệu từ 1 bảng để cập nhật giá trị cho 1 bảng khác.

Có 1 vài chức năng mà chỉ có Subqueries có thể làm được, 2 ví dụ sau đây sẽ chứng minh viện subqueries không thể bị thay thế bởi CTEs. Ví dụ đầu tiên, Subquery sử dụng với WHERE. Ví dụ thứ 2, gán giá trị cho 1 bảng khác. SQL không cho phép CTEs thực hiện các tác vụ này.

Ví dụ 1: Sử dụng Subqueries trong mệnh đề WHERE

Giả sử, 1 ngân hàng lữu trữ các giao dịch trong ngày ở 1 bảng gọi là daily_trans. Dữ liệu bao gồm account_number, transaction_code, amount.

Cơ sở dữ liệu này cũng có 1 bảng gọi là transaction_types và bao gồm transaction_code, 1 chỉ báo có tên là debit_credit, được gán cho giá trị 1 là credit và 2 là debit.

Nếu ngân hàng muôn 1 danh sách tất cả các giao dịch credit trong ngày, truy vấn như sau:

SELECT

  account_no,

  tran_code,

  amount

FROM daily_trans

WHERE tran_code IN

  (SELECT tran_code

   FROM transaction_types

   WHERE debit_credit = 1);

Truy vấn này chỉ có thể sử dụng Subqueries vì CTE không thể thực hiện với mệnh đề WHERE

Ví dụ 2: Sử dụng Subqueries trong UPDATE

Giả sử rằng, cũng ngân hàng đó có 1 bảng gọi là customer, bao gồm: account_number, customer_name, employee_number của nhân viên hỗ trợ cho khách hàng đó.

Ngân hàng tiến hành cải tổ và muốn phân phối lại nhân viên để chăm sóc cho khách hàng. Để làm việc này, họ đã tạo 1 bảng gọi là reassignments, bao gồm: số employee_number cũ và số employee_number mới. Để thực hiện yêu cầu này, chúng ta viết 1 truy vấn như sau:

UPDATE customer

SET support_person =

  (SELECT new_employee

   FROM reassignments

   WHERE old_employee = customer.support_person);

CTEs biến 1 câu truy vấn phức tạp dễ đọc hơn

Để hiểu rõ vì sao CTEs có thể khiến 1 câu truy vấn phức tạp dễ hiểu hơn hãy xem ví dụ sau đây. Ví dụ này sử dụng 1 câu truy vấn phức tạp, sử dùng nhiều subquery lồng ghép và chúng ta sẽ viết lại câu đó bằng phương pháp sử dụng CTEs.

Ví dụ:

Giả sử rằng, chúng ta có 1 cửa hàng bán 3 loại sản phẩm: book, music và videos. Quản lý muốn biết sức mua từng loại của mỗi khách hàng.

Bài báo cáo sẽ như sau:

và đây là cách sử dụng subqueries để báo cáo:

SELECT

    customer,

    sum(purchases) AS Total,

    total_books AS Books,

    total_music AS Music,

    total_videos AS Videos

FROM sales

JOIN

    (SELECT account_no, sum(purchases) AS total_books FROM sales

    WHERE product_type = 'Books'

    GROUP BY account_no) books

ON books.account_no = sales.account_no

JOIN

    (SELECT account_no, sum(purchases) AS total_music FROM sales

    WHERE product_type = 'Music'

    GROUP BY account_no) music

ON music.account_no = sales.account_no

JOIN

    (SELECT account_no, sum(purchases) AS total_videos FROM sales

    WHERE product_type = 'Videos'

    GROUP BY account_no) videos

ON videos.account_no = sales.account_no

GROUP BY customer

ORDER BY customer

Như các bạn có thể thấy, đoạn truy vấn khá phức tạp và khó có thể cho người khác chỉnh sửa sau này.

Bây giờ hãy viết đoạn truy vấn trên sử dụng CTEs:

WITH books AS (

  SELECT

    customer,

    sum(purchases) AS total_books

  FROM sales

  WHERE product_type = 'Books'

  GROUP BY customer

),

music AS (

  SELECT

    customer,

    sum(purchases) AS total_music

  FROM sales

  WHERE product_type = 'Music'

  GROUP BY customer

),

videos as (

  SELECT

    customer,

    sum(purchases) AS total_videos

  FROM sales

  WHERE product_type = 'Videos'

  GROUP BY customer

)

SELECT

  customer,

  sum(purchases) AS Total,

  total_books AS Books,

  total_music AS Music,

  total_videos AS Videos

FROM sales

JOIN books ON books.customer = sales.customer

JOIN music ON music.customer = sales.customer

JOIN videos ON videos.customer = sales.customer

GROUP BY customer

ORDER BY customer

Dù cho cả 2 câu truy vấn này đều cho ra kết quả như nhau nhưng hãy nhìn vào truy vấn sử dụng CTEs, câu truy vấn nhìn đơn giản và trực quan hơn rất nhiều.

Thế nào là dùng CTEs đệ quy ?

Như đã liệt kê ở trên, CTEs có thể dùng để đệ quy. Vậy thế nào là 1 câu truy vấn đệ quy ? Truy vấn đệ quy cho phép bạn điều hướng dữ liệu có thứ bậc và tạo báo cáo phù hợp với dữ liệu dạng cây và biểu đồ. Ví dụ về dữ liệu phân cấp bao gồm:

• Trong một tổ chức, một nhân viên có thể báo cáo với người quản lý phụ; người quản lý phụ báo cáo cho người quản lý chính và người quản lý chính báo cáo cho ban giám đốc.
• Trong sản xuất, một sản phẩm có thể được cấu thành từ nhiều phần. Mỗi phần cũng có thể được tạo thành từ nhiều phần phụ và các phần phụ có thể được làm từ nhiều nguyên liệu thô khác nhau.

Hãy xem ví dụ sau đây. Một tiệm kem có một số món trong thực đơn. Mỗi món trong thực đơn có thể được làm từ nhiều nguyên liệu: một phần Banana Split được làm từ Banana, Chocolate Sauce và Ice cream. Nhưng Chocolate Sauce cũng có một số thành phần. Chúng có thể bao gồm bột Cocoa, Sugar và một số thứ khác.

Người chủ muốn có một danh sách đầy đủ của từng món trong menu theo sau đó là tất cả các thành phần của nó. 1 phần của danh sách có thể trông như thế này:

Item	Ingredient
Menu	150
Menu > Banana Split	Banana Split
Menu > Banana Split > Banana	Banana
Menu > Banana Split > Chocolate Sauce	Chocolate Sauce
Menu > Banana Split > Chocolate Sauce > Cocoa	Cocoa
Menu > Banana Split > Chocolate Sauce > Margarine	Margarine
Menu > Banana Split > Chocolate Sauce > Sugar	Sugar
Menu > Banana Split > Ice cream – American	Ice cream – American
Menu > Banana Split > Ice cream – American > Cream	Cream
Menu > Banana Split > Ice cream – American > Milk	Milk
Menu > Banana Split > Ice cream – American > Sugar	Sugar
Menu > Banana Split > Ice cream – American > Vanilla Extract	Vanilla Extract
Menu > Choc Nut Sundae	Choc Nut Sundae
Menu > Choc Nut Sundae > Chocolate Sauce	Chocolate Sauce
Menu > Choc Nut Sundae > Chocolate Sauce > Cocoa	Cocoa
Menu > Choc Nut Sundae > Chocolate Sauce > Margarine	Margarine
Menu > Choc Nut Sundae > Chocolate Sauce > Sugar	Sugar
Menu > Choc Nut Sundae > Ice cream – Rich	Ice cream – Rich
Menu > Choc Nut Sundae > Ice cream – Rich > Cream	Cream
Menu > Choc Nut Sundae > Ice cream – Rich > Egg	Egg
Menu > Choc Nut Sundae > Ice cream – Rich > Sugar	Sugar
Menu > Choc Nut Sundae > Ice cream – Rich > Vanilla Extract	Vanilla Extract
Menu > Choc Nut Sundae > Mixed Nuts	Mixed Nuts

Trong cơ sở dữ liệu của tiệm kem, ta có 2 bảng như sau:

• Bảng Items chứa 1 danh sách các món trong menu và mỗi thành phần
• bản Bill_of_materials chứa liên kết giữa món và các thành phần của nó.

Bảng Items chứa những thông tin sau:

id	Description	unit	price
15	Menu	NULL	NULL
14	Egg	Each	0.1
13	Banana	Each	0.2
12	Banana Split	Each	2
11	Margarine	Kg	4
10	Cocoa	Kg	10
9	Chocolate Sauce	Litre	8
8	Mixed Nuts	Kg	2
7	Choc Nut Sundae	Each	1.5
6	Ice Cream – Rich	Litre	6
5	Sugar	Kg	2
4	Vanilla Extract	Bottle	1
3	Milk	Litre	1.5
2	Cream	Litre	4
1	Ice Cream – American	Litre	5

Dưới đây là một số ví dụ về các mục nhập trong bảng Bill_of_materials. Cột item_id giữ liên kết đến mục chính trong bảng Items, trong khi component_id giữ liên kết đến một trong các thành phần của nó. Do đó, mục đầu tiên thể hiện component_ID 10: Cocoa là một thành phần của Item_id 9: Chocolate Sauce.

Sau đây là câu truy vấn SQL đệ quy để duyệt qua thông tin này.

WITH menu_ingredients (id, path, description, item_id)

AS (

  SELECT

CAST (id AS bigint),

CAST (description as varchar (255)),

CAST ('' AS varchar(40)),

CAST (id AS bigint)

   FROM items

   WHERE description = 'Menu'

   UNION ALL

   SELECT

CAST (bom.component_id AS bigint),

CAST (m.path + ' > ' + i.description AS varchar(255)),

i.description,

CAST (bom.item_id AS bigint)

   FROM menu_ingredients m, bill_of_materials bom

   JOIN items i

   ON i.id = bom.component_id

   WHERE bom.item_id = m.id

)

SELECT

  path,

  description

FROM menu_ingredients

ORDER BY path

Kết quả của truy vấn này là khi một hàng được thêm vào tập kết quả trong một truy vấn đệ quy, nó có thể tìm hàng trước đó và sử dụng hàng đó để lấy một phần thông tin có thể dùng để tìm hàng tiếp theo.

Truy vấn này bắt đầu bằng cách chọn mục cao nhất trong hóa đơn nguyên vật liệu: chính là menu. Từ đó, nó có thể truy vấn lặp qua tất cả các hàng con của nó – những thành phần mà nó được tạo ra. Và mỗi thành phần có thể chọn các hàng con của riêng mình, nếu có.

Tổng Kết

Để tổng kết lại toàn bộ kiến thức trên, SmartData đưa ra những kết luật sau:

Chọn CTE khi:

• Chúng ta muốn truy vấn dễ đọc và trông có vẻ “Pro” hơn
• Chunngs ta cần truy vấn đệ quy

Chọn Subquery khi:

• Sử dụng mệnh đề WHERE với từ khoá IN hoặc EXISTS để truy vấn điều kiện từ 1 bảng khác
• Khi bạn muốn lấy 1 phần dữ liệu từ 1 bảng và UPDATE phần dữ liệu đó bảng mới.

Còn những trường hợp khác, sử dụng phương pháp nào cũng khả thi, miễn là bạn cảm thấy thoải mái.

Nếu bạn là newbie có thể tham khảo bài viết này để tìm hiểu lộ trình học DA trong 3 tháng của SmartData.

Nếu bạn thấy bài viết hay và hữu ích, bạn có thể tham gia các kênh sau của SmartData để nhận được nhiều hơn nữa:

• Fanpage
• Youtube
• Trang chủ
• Cộng đồng Data Analyst