Biểu thức bảng chung (CTE) được nhập vào SQL để đơn giản hóa nhiều lớp của Ngôn ngữ truy vấn có cấu trúc (SQL) cho một bảng dẫn xuất, không phù hợp. Nó đã được giới thiệu trong phiên bản SQL SERVER 2005.
Các bài viết liên quan:
Biểu thức bảng chung (CTE) là một tập kết quả mà chúng tôi tham chiếu với câu lệnh SELECT, INSERT, UPDATE hoặc DELETE. Trong SQL 2008, chúng tôi thêm một CTE cho câu lệnh MERGE duy nhất.
Giới thiệu về CTE trong SQL
CTE (Common Table Expression) là một công cụ mạnh mẽ trong SQL cho phép bạn xây dựng và sử dụng các truy vấn tạm thời trong cùng một truy vấn lớn. Nó cho phép bạn tạo ra các bảng ảo tạm thời (temporary tables) trong bộ nhớ khi thực thi truy vấn.
Một CTE thường được xác định bởi một cú pháp đặc biệt trong câu lệnh SELECT. Nó cho phép bạn đặt tên cho một truy vấn con và sử dụng nó như một bảng tạm thời trong truy vấn chính.
Việc sử dụng CTE giúp làm cho truy vấn SQL dễ đọc hơn và giảm độ phức tạp của câu lệnh. Bạn có thể sử dụng CTE để xử lý dữ liệu phức tạp, kết hợp các truy vấn phức tạp và tạo ra báo cáo phức tạp.
CTE cũng cung cấp tính năng tái sử dụng, cho phép bạn sử dụng kết quả của một truy vấn tạm thời trong nhiều truy vấn khác nhau trong cùng một truy vấn lớn.
Để sử dụng CTE, bạn cần biết cú pháp và quy tắc cơ bản. CTE thường được sử dụng với các câu lệnh SELECT, INSERT, UPDATE và DELETE.
Trong các phiên bản SQL hiện đại, CTE đã trở thành một phần quan trọng của ngôn ngữ SQL và được hỗ trợ rộng rãi trong hầu hết các hệ quản trị cơ sở dữ liệu.
Việc hiểu và sử dụng CTE trong SQL sẽ giúp bạn tối ưu hóa truy vấn và cải thiện hiệu suất của ứng dụng của mình.
Lợi ích của việc sử dụng CTE
Việc sử dụng CTE (Common Table Expression) trong SQL mang lại nhiều lợi ích quan trọng cho việc xử lý dữ liệu và tối ưu hóa truy vấn. Dưới đây là một số lợi ích chính:
Giảm độ phức tạp của truy vấn SQL
CTE cho phép bạn tạo ra các truy vấn tạm thời và sử dụng chúng như một bảng tạm trong truy vấn chính. Điều này giúp giảm độ phức tạp của câu lệnh SQL bằng cách phân tách nó thành các phần nhỏ hơn, dễ đọc hơn và dễ hiểu hơn. Bạn có thể sử dụng các CTE liên tiếp nhau để xử lý dữ liệu phức tạp một cách có tổ chức.
Xem thêm Hướng dẫn về SQL
Tính tái sử dụng và khả năng mở rộng
CTE cho phép bạn xác định các truy vấn tạm thời mà bạn có thể tái sử dụng trong nhiều truy vấn khác nhau trong cùng một truy vấn lớn. Điều này giúp giảm lặp lại mã truy vấn và cung cấp tính modular cho mã SQL của bạn. Ngoài ra, khi bạn cần mở rộng truy vấn hoặc thêm logic mới, bạn có thể dễ dàng mở rộng CTE hiện có mà không ảnh hưởng đến các phần khác của truy vấn.
Tăng hiệu suất và tối ưu hóa truy vấn
CTE có thể giúp tăng hiệu suất của truy vấn SQL. Bằng cách tạo ra các bảng tạm thời trong bộ nhớ, CTE có thể giảm số lần truy cập đến ổ đĩa và tối ưu hóa các phép tính truy vấn. Ngoài ra, CTE cũng cho phép bạn sử dụng các biểu thức cùng tên trong truy vấn chính và các CTE liên quan, giúp tối ưu hóa và tái sử dụng mã truy vấn.
Việc sử dụng CTE trong SQL không chỉ giúp làm cho mã SQL của bạn dễ đọc hơn mà còn cung cấp các lợi ích quan trọng về hiệu suất và khả năng mở rộng. Nắm vững và sử dụng CTE một cách hiệu quả sẽ giúp bạn xử lý dữ liệu phức tạp và tối ưu hóa truy vấn trong các ứng dụng SQL của mình.
Làm thế nào để sử dụng CTE trong T-SQL?
Việc sử dụng Biểu thức Văn bản Thông thường là thêm mệnh đề “WITH” trước câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc MERGE. Mệnh đề WITH chứa một hoặc nhiều CTE, được phân tách bằng dấu phẩy.
Chúng tôi tạo CTE khi chúng tôi tham chiếu đến bất kỳ bảng nào. Tập kết quả CTE không thể truy cập vào bất kỳ câu lệnh nào khi chúng tôi chạy câu lệnh cụ thể.
Xem thêm Truy vấn Plan Cache Commands trong MongoDB
Tạo biểu thức bảng đệ quy
CTE đệ quy được sử dụng khi chúng ta đang làm việc với dữ liệu phân cấp. Một ví dụ về dữ liệu phân cấp trong bảng là danh sách sinh viên trong nhóm. Đối với mỗi sinh viên, bộ đếm tạo một ReferenceID và một NAME. ReferenceID tự tham chiếu giống như ID nhân viên trong bảng đệ quy. Chúng tôi sử dụng CTE để hiển thị vị trí trong cơ sở dữ liệu của nhân viên.
Nếu CTE được tạo sai, nó sẽ đi vào vòng lặp vô hạn.
Để ngăn vòng lặp vô tận, MAXRECURSION sẽ được thêm vào mệnh đề TÙY CHỌN của câu lệnh INSERT, DELETE, UPDATE, SELECT hoặc MERGE.
Sử dụng mã dưới đây để tạo bảng:
Sau khi bảng Employee được tạo, một câu lệnh SELECT, đứng trước một mệnh đề WITH bao gồm một CTE có tên cteReports sẽ được tạo:
Nó là một công cụ cần thiết để tạo tập kết quả không nhất quán và được truy xuất trong câu lệnh SELECT, UPDATE, INSERT, MERGE hoặc DELETE.
Xem thêm Ánh xạ SQL sang MongoDB
Ví dụ về việc sử dụng CTE trong SQL
Dưới đây là một ví dụ về việc sử dụng CTE trong SQL để tìm danh sách nhân viên có mức lương cao nhất:
WITH top_employees AS (
SELECT employee_id, employee_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
)
SELECT employee_id, employee_name, salary
FROM top_employees
WHERE row_num = 1;Trong ví dụ này, chúng ta sử dụng CTE với tên top_employees để tạo một bảng tạm thời chứa thông tin về các nhân viên và mức lương của họ. Câu lệnh SELECT trong CTE sử dụng hàm ROW_NUMBER() để đánh số thứ tự (row number) cho mỗi nhân viên dựa trên mức lương, sắp xếp theo thứ tự giảm dần (DESC).
Sau đó, chúng ta thực hiện một truy vấn SELECT chính để lấy thông tin về nhân viên có mức lương cao nhất. Trong truy vấn SELECT chính, chúng ta tham chiếu đến bảng tạm thời top_employees và sử dụng điều kiện WHERE để chỉ lấy nhân viên có row_num bằng 1, tức là nhân viên có mức lương cao nhất.
Kết quả của truy vấn sẽ trả về danh sách nhân viên có mức lương cao nhất, bao gồm thông tin về employee_id, employee_name và salary.
Lưu ý rằng ví dụ này chỉ để minh họa cách sử dụng CTE trong SQL. Trong thực tế, CTE có thể được sử dụng để xử lý dữ liệu phức tạp hơn và có thể kết hợp với các truy vấn khác để tạo ra các báo cáo hoặc tính toán phức tạp hơn.
Xem thêm Toán tử trong SQL
So sánh CTE với các phương pháp khác
Dưới đây là một so sánh giữa CTE và một số phương pháp khác trong SQL:
- So sánh với bảng tạm (temporary table):
- CTE: CTE là một bảng tạm trong bộ nhớ được tạo ra và sử dụng trong cùng một truy vấn. Nó không tạo bảng tạm vĩnh viễn trên đĩa.
- Bảng tạm: Bảng tạm là một bảng được tạo ra trong cơ sở dữ liệu và lưu trữ trên đĩa. Nó có thể được sử dụng trong nhiều truy vấn và tồn tại cho đến khi nó được xóa hoặc phiên làm việc kết thúc.
- So sánh với bảng dẫn (derived table):
- CTE: CTE cho phép bạn tạo ra các truy vấn tạm thời và sử dụng chúng như một bảng tạm trong truy vấn chính. CTE có thể tái sử dụng và mở rộng.
- Bảng dẫn: Bảng dẫn là một bảng phụ thuộc được tạo trong câu lệnh SELECT. Nó chỉ tồn tại trong phạm vi câu lệnh SELECT và không thể được tái sử dụng trong các câu lệnh khác.
So sánh giữa CTE và các phương pháp khác:
- CTE có thể giúp làm cho mã SQL dễ đọc hơn và giảm độ phức tạp của truy vấn. Nó cung cấp tính tái sử dụng và khả năng mở rộng.
- Bảng tạm và bảng dẫn thích hợp khi cần lưu trữ dữ liệu tạm thời hoặc sử dụng nhiều lần trong các truy vấn khác nhau.
- Sử dụng CTE thường đơn giản hơn và tạo ra mã SQL ngắn gọn hơn so với việc sử dụng bảng tạm hoặc bảng dẫn.
- Tuy nhiên, CTE chỉ tồn tại trong phạm vi của truy vấn chính và không thể được sử dụng trong các truy vấn khác. Trong khi đó, bảng tạm và bảng dẫn có thể được sử dụng lại trong nhiều truy vấn khác nhau.
Tùy thuộc vào tình huống cụ thể và yêu cầu của bạn, bạn có thể lựa chọn sử dụng CTE, bảng tạm hoặc bảng dẫn để xử lý dữ liệu và tối ưu hóa truy vấn trong SQL.
Xem thêm index trong sql là gì ?
