CTE là gì? Tất cả những gì bạn cần biết về CTE trong SQL

1. CTE là gì?

Common Table Expression (CTE) là tập hợp kết quả được đặt tên trong truy vấn SQL. CTE giúp sắp xếp code và cho phép thực hiện các phép tổng hợp nhiều cấp trên dữ liệu, chẳng hạn như tìm giá trị trung bình của một tập hợp số đếm.

CTE đã được đưa vào SQL tiêu chuẩn để đơn giản hóa các lớp truy vấn SQL khác nhau mà bảng dẫn xuất không phù hợp. CTE được giới thiệu trong SQL Server 2005, biểu thức bảng chung (CTE) là tập kết quả được đặt tên tạm thời mà chúng ta có thể tham chiếu trong câu lệnh SELECT, INSERT, UPDATE hoặc DELETE. Cũng có thể sử dụng CTE trong chế độ xem TẠO, như một phần của truy vấn CHỌN của chế độ xem.

1.1. Ưu điểm của CTE

• CTE giúp code dễ đọc hơn và làm cho các truy vấn dễ gỡ lỗi hơn.
• CTE có thể tham chiếu kết quả nhiều lần trong suốt truy vấn. Bằng cách lưu trữ kết quả của truy vấn phụ, chúng ta có thể sử dụng lại chúng trong toàn bộ truy vấn lớn hơn.
• CTE giúp thực hiện việc tổng hợp nhiều cấp độ. Sử dụng CTE để lưu trữ kết quả tổng hợp, sau đó có thể tóm tắt kết quả này trong truy vấn chính.

1.2. Cú pháp CTE

Cú pháp của CTE sử dụng từ khóa WITH và tên biến để tạo một loại bảng tạm thời để tham chiếu trong các phần khác của truy vấn.

WITH cte_name(column1, column2, etc.) AS (SELECT ...)

Từ khóa AS ở đây hơi khác thường. Thông thường AS được sử dụng để chỉ định một bí danh, như consumables_orders AS order, với order là bí danh ở bên phải AS. Với CTE, biến cte_name đứng trước (nằm ở bên trái) từ khóa AS, theo sau là truy vấn con. Lưu ý rằng danh sách cột (column1, column2, etc.) là tùy chọn, miễn là mỗi cột trong câu lệnh SELECT có một tên duy nhất.

2. Một số ví dụ với CTE trong SQL

Ví dụ: Xem danh sách tất cả các đơn đặt hàng có tổng số tiền lớn hơn tổng số đơn đặt hàng trung bình.

SELECT

  id,

  total

FROM

  orders

WHERE

-- filter for orders with above-average totals

  total > (

    SELECT

      AVG(total)

    FROM

      orders

  )

Kết quả trả về từ truy vấn:

|ID  |TOTAL  |

|----|-------|

|2   |117.03 |

|4   |115.22 |

|5   |134.91 |

|... |...    |

Ở đây chúng ta có một truy vấn con, các đơn hàng SELECT AVG(total) FROM, được lồng trong mệnh đề WHERE để tính tổng đơn hàng trung bình. Nhưng điều gì sẽ xảy ra nếu việc lấy mức trung bình có liên quan nhiều hơn? Ví dụ: Giả sử bạn cần lọc các đơn hàng thử nghiệm hoặc loại trừ các đơn hàng trước khi khởi chạy ứng dụng:

SELECT

  id,

  total

FROM

  orders

WHERE

  total > (

    -- calculate average order total

    SELECT

      AVG(total)

    FROM

      orders

    WHERE

      -- exclude test orders

      product_id > 10

      AND -- exclude orders before launch

      created_at > '2016-04-21'

      AND -- exclude test accounts

      user_id > 10

  )

ORDER BY

  total DESC

Bây giờ truy vấn đã trở nên phức tạp hơn. Có thể viết lại truy vấn con dưới dạng biểu thức bảng chung bằng cách sử dụng câu lệnh WITH để gói gọn các kết quả trả về:

-- CTE to calculate average order total

-- with the name for the CTE (avg_order) and column (total)

WITH avg_order(total) AS (

-- CTE query

  SELECT

    AVG(total)

  FROM

    orders

  WHERE

    -- exclude test orders

    product_id > 10

    AND -- exclude orders before launch

    created_at > '2016-04-21'

    AND -- exclude test accounts

    user_id > 10

)

-- our main query:

-- orders with above-average totals

SELECT

  o.id,

  o.total

FROM

  orders AS o

  -- join the CTE: avg_order

  LEFT JOIN avg_order AS a

WHERE

  -- total is above average

  o.total > a.total

ORDER BY

  o.total DESC

CTE gói gọn logic để tìm mức trung bình và tách logic đó khỏi truy vấn cốt lõi.

Ví dụ: tìm ID đơn đặt hàng có tổng số trên mức trung bình. Lưu ý kết quả của CTE này không được lưu ở bất cứ đâu; truy vấn con của nó được thực thi mỗi khi bạn chạy truy vấn.

Việc lưu trữ truy vấn này dưới dạng CTE cũng giúp việc sửa đổi truy vấn dễ dàng hơn. Giả sử chúng ta cũng muốn biết đơn hàng nào có:

• above-average totals: tổng số trên trung bình,
• below-average quantities of items ordered: số lượng mặt hàng được đặt hàng dưới mức trung bình.

Có thể dễ dàng mở rộng truy vấn như vậy:

-- CTE to calculate average order total and quantity

WITH avg_order(total, quantity) AS (

  SELECT

    AVG(total),

    AVG(quantity)

  FROM

    orders

  WHERE

    -- exclude test orders

    product_id > 10

    AND -- exclude orders before launch

    created_at > '2016-04-21'

    AND -- exclude test accounts

    user_id > 10

)

-- orders with above-average totals

SELECT

  o.id,

  o.total,

  o.quantity

FROM

  orders AS o -- join the CTE avg_order

  LEFT JOIN avg_order AS a

WHERE

  -- above-average total

  o.total > a.total

  -- below-average quantity

  AND o.quantity < a.quantity

ORDER BY

  o.total DESC,

  o.quantity ASC

Một điểm đặc biệt chúng ta có thể chọn và chỉ chạy truy vấn phụ trong CTE.

Ngoài ra, chúng ta cũng có thể lưu truy vấn con của CTE dưới dạng đoạn code, nhưng tốt hơn hết nên lưu truy vấn phụ dưới dạng câu hỏi. Nguyên tắc chung để quyết định giữa một đoạn mã và một câu hỏi đã lưu là nếu một khối mã có thể tự trả về kết quả thì bạn có thể cân nhắc việc lưu nó dưới dạng câu hỏi.

Sử dụng mệnh đề WHERE nắm bắt logic để lọc đơn đặt hàng của khách hàng.

2.1. CTE với một Question trong SQL

Sử dụng câu lệnh with để tham chiếu đề Question đã được lưu ở đây là #2.

WITH avg_order(total, quantity) AS {{#2}}

-- orders with above-average totals

SELECT

  o.id,

  o.total,

  o.quantity

FROM

  orders AS o -- join the CTE avg_order

  LEFT JOIN avg_order AS a

WHERE

  -- above-average totals

  o.total > a.total

  -- below-average quantity

  AND o.quantity < a.quantity

ORDER BY

  o.total DESC,

  o.quantity ASC

Có thể xem câu hỏi được tham chiếu bởi {{#2}} bằng thanh bên Varialbes. Trong trường hợp này, 2 là ID của question.

Bằng cách lưu truy vấn con đó dưới dạng câu hỏi độc lập, nhiều câu hỏi sẽ có thể tham chiếu kết quả của nó. Và nếu cần thêm các mệnh đề WHERE bổ sung để loại trừ nhiều yêu cầu kiểm tra khỏi phép tính, thì mỗi câu hỏi tham chiếu đến phép tính đó sẽ được hưởng lợi. Nhược điểm của hành động này là nếu thay đổi câu hỏi đã lưu để trả về các cột khác nhau, thì sẽ làm hỏng các truy vấn phụ thuộc vào kết quả của nó.

2.2. Sử dụng nhiều CTE trong một truy vấn - query

Có thể sử dụng nhiều CTE trong cùng một truy vấn. Chúng ta cần phân tách tên và truy vấn phụ của chúng bằng dấu phẩy, như sau:

-- first CTE

WITH avg_order(total) AS (

  SELECT

    AVG(total)

  FROM

    orders

),

-- second CTE (note the preceding comma)

avg_product(rating) AS (

  SELECT

    AVG(rating)

  FROM

    products

)

3. Hướng dẫn cách viết truy vấn với CTE 

Dưới đây là một số nguyên tắc cơ bản cần được tuân theo để viết truy vấn CTE tốt:

• Theo sau CTE phải là một câu lệnh SELECT, INSERT, UPDATE, or DELETE duy nhất tham chiếu đến một số hoặc tất cả các cột CTE.
• Nhiều định nghĩa truy vấn CTE có thể được xác định trong CTE không đệ quy.
• Một CTE có thể tham chiếu chính nó và các CTE được xác định trước đó trong cùng một mệnh đề WITH
• Chỉ có thể sử dụng một mệnh đề WITH trong CTE
• Không thể sử dụng ORDER BY, INTO, COMPUTE hoặc COMPUTE BY, OPTION, FOR XML trong định nghĩa truy vấn CTE không đệ quy
• SELECT DISTINCT, GROUP BY, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (cho phép INNER JOIN) không thể sử dụng các truy vấn con trong định nghĩa truy vấn CTE đệ quy.

3. Lời Kết

Trong bài viết trên đây, ICANTECH đã cùng bạn tìm lời giải đáp cho câu hỏi CTE là gì cũng như ví dụ và hướng dẫn cách viết truy vấn CTE trong SQL. Hi vọng bạn sẽ áp dụng các kiến thức trên để thao tác cơ sở dữ liệu với ứng dụng CTE hiệu quả hơn. 

Cảm ơn bạn đã đọc bài viết, nếu bạn đang quan tâm đến học lập trình online thì hãy tham khảo ngay các khóa học lập trình dưới đây tại ICANTECH nhé

Nguồn ảnh: ICANTECH.