Published thg 1 12, 2023 3:16 SA 13 min read

6.0K

Bài 3: [Intermediate] So sánh Subquery và With(CTE) thằng nào ngon hơn?

I: Giới thiệu

Xin chào mọi người! Trong bài viết này mình xin được giới thiệu đến mọi người về chủ đề subquery và with clause (CTE) trong SQL. Mỗi một chủ đề trên mình sẽ phân tích rõ bộ 3 câu hỏi hủy diệt WWH = What (là cái gì) + Why (tại sao phải dùng nó) + How (dùng nó như thế nào), trong bài viết này sẽ tìm hiểu thêm cả sự khác nhau giữa chúng và có thể thay thế cho nhau được không? Let go ! 👇️👇️👇️

II: Subquery

Trong SQL, subquery:

What : là một truy vấn được lồng bên trong một truy vấn khác.

Why : Nó được sử dụng để trả về dữ liệu sẽ được sử dụng trong truy vấn chính như một điều kiện để hạn chế hơn nữa dữ liệu được truy xuất.

How : Subquery có thể được sử dụng trong các phần khác nhau của câu lệnh SQL, chẳng hạn như các mệnh đề SELECT, FROM, WHERE và HAVING.

Ví dụ, subquery trong mệnh đề WHERE có thể được sử dụng để lọc kết quả của truy vấn chính dựa trên kết quả của subquery.

SELECT column1, column2
FROM table1
WHERE column3 = (SELECT column4 FROM table2 WHERE column5 = 'some value');

Trong ví dụ này, subquery (SELECT column4 FROM table2 WHERE column5 = 'some value') được sử dụng để trả về một giá trị đơn lẻ, giá trị này sau đó được dùng để lọc kết quả của truy vấn chính.

Đây là một ví dụ clear hơn về subquery trong SQL sử dụng dữ liệu:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = 'IT');

Trong ví dụ này, subquery (SELECT AVG(salary) FROM employees WHERE department = 'IT') được sử dụng để tính toán mức lương trung bình của tất cả nhân viên trong bộ phận IT. Sau đó, giá trị này được sử dụng để lọc truy vấn chính truy xuất first_name, last_name và salary của tất cả nhân viên có lương lớn hơn lương trung bình của nhân viên bộ phận IT.

NOTE: Bất kì 1 thứ gì đó đều có 2 mặt của nó, dưới đây mình sẽ đưa ra các ưu nhược điểm của subquery:

Ưu điểm:
- Flexibility: subquery cho phép bạn truy xuất dữ liệu từ nhiều bảng và sử dụng dữ liệu đó theo nhiều cách khác nhau, khiến chúng trở thành một công cụ rất linh hoạt để truy vấn dữ liệu.
- Simplicity: subquery có thể làm cho các truy vấn phức tạp trở nên đơn giản hơn bằng cách chia chúng thành các phần nhỏ hơn, dễ quản lý hơn.
- Powerful filtering: subquery có thể được sử dụng để lọc dữ liệu ở nhiều cấp độ, cho phép truy xuất dữ liệu rất chính xác.
- Reusability: subquery có thể được sử dụng lại trong nhiều phần của truy vấn hoặc trong nhiều truy vấn, làm cho chúng trở thành công cụ hữu ích để viết mã mô-đun và có thể maintainable code.
Nhược điểm:
- Performance: subquery có thể ảnh hưởng đến hiệu suất truy vấn, đặc biệt khi chúng được lồng vào nhau hoặc được sử dụng trong mệnh đề SELECT.
- Readability: subquery có thể làm cho các câu lệnh SQL trở nên phức tạp hơn và khó đọc hơn, điều này có thể khiến chúng khó hiểu và khó maintainable hơn. Hãy tưởng tượng có nhiều cấp độ subquery lồng nhau trong đoạn code SQL thì sẽ rối như thế nào vì vậy mình khuyên nên ưu tiên dùng JOIN và WITH CLAUSE (CTE) để có thể đơn giản hóa truy vấn.
- Debugging: Nếu subquery không hoạt động như mong đợi, có thể khó xác định nguyên nhân của sự cố và khắc phục sự cố.

III: With clause (CTE)

Trong SQL, with clause (CTE):

WHAT : Common Table Expression (CTE) trong SQL là tập hợp kết quả tạm thời được đặt tên mà bạn có thể tham chiếu trong câu lệnh SELECT, INSERT, UPDATE hoặc DELETE.

WHY: Có thể được coi là một cách để đơn giản hóa khả năng đọc và khả năng bảo trì của các truy vấn phức tạp bằng cách chia chúng thành các phần nhỏ hơn, dễ quản lý hơn và có thể được sử dụng nhiều lần trong truy vấn.

HOW: CTE được xác định bằng cách sử dụng từ khóa WITH, theo sau là câu lệnh SELECT chỉ định các cột và hàng của CTE và một tên tùy chọn cho CTE.

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

và sau đó nó có thể được sử dụng trong truy vấn sau:

SELECT * FROM cte_name

Dưới đây là một ví dụ về việc sử dụng CTE trong SQL:

Có một table "Employees" có dữ liệu sau:

EmployeeID | EmployeeName | Salary | ManagerID
----------------------------------------------
1          | John Doe     | 50000  | NULL
2          | Jane Smith   | 60000  | 1
3          | Bob Johnson  | 70000  | 1
4          | Lisa Davis   | 55000  | 2

Chúng tôi muốn truy xuất EmployeeName của tất cả nhân viên có người quản lý với mức lương lớn hơn 60000.

WITH HighSalaryManagers (ManagerID) AS
(
    SELECT EmployeeID FROM Employees WHERE Salary > 60000
)
SELECT EmployeeName FROM Employees
WHERE ManagerID IN (SELECT ManagerID FROM HighSalaryManagers);

Truy vấn này tạo một CTE có tên là "HighSalaryManagers" được sử dụng để chọn tất cả những người quản lý có mức lương lớn hơn 60000, sau đó nó được sử dụng trong truy vấn chính, nơi nó lọc nhân viên theo những người quản lý đó, bạn có thể thấy CTE được xác định chỉ một lần , và nó dễ đọc hơn, dễ bảo trì hơn và bạn không phải lặp lại subquery nhiều lần, nó cũng được đặt tên, giúp dễ hiểu ý định của mã hơn và nó có thể tái sử dụng nhiều hơn.

Nếu cùng bài toán trên mình sử dụng subquery thì sao nhỉ?

SELECT EmployeeName FROM Employees
WHERE ManagerID IN (SELECT EmployeeID FROM Employees WHERE Salary > 60000);

Cũng chưa rối lắm đúng không nhỉ? Nhưng hãy tưởng tượng khoảng 3 - 4 subquery lồng nhau thôi và 1-2 tháng sau bạn quay lại maintain lại đoạn code này thì việc define ra 1 CTE sẽ tạo ra sự khác biệt đấy, nó sẽ tạo những func có tên tuổi rõ ràng và gần như chỉ có select + from table CTE thay vì 1 đoạn subquery nhìn lại thấy khó hiểu, dài ngoằng gồm select + where + from + group by + order by + .... chưa hiểu mục đích để làm gì?

Sau 1 thời gian sử dụng CTE thì thật sự không khoái dùng thằng subquery nữa rồi nhưng CTE có thay thế được subquery hay không thì mình cùng nhau tìm hiểu tiếp ở phần dưới nhé.

NOTE: Bất kì 1 thứ gì đó đều có 2 mặt của nó, dưới đây mình sẽ đưa ra các ưu nhược điểm của CTE:

Ưu điểm:
- Readability: CTE làm cho các truy vấn dễ đọc hơn bằng cách chia chúng thành các phần nhỏ hơn, dễ quản lý hơn. Điều này có thể giúp hiểu logic của truy vấn dễ dàng hơn và dễ bảo trì hơn.
- Performance: CTE có thể cải thiện hiệu suất truy vấn bằng cách cho phép bạn tính toán trước và sử dụng lại các kết quả trung gian, thay vì tính toán chúng nhiều lần trong một truy vấn.
- Modularity: CTE cho phép bạn xây dựng các truy vấn phức tạp bằng cách kết hợp các thành phần nhỏ hơn, có thể tái sử dụng. Điều này có thể giúp viết và duy trì các truy vấn phức tạp dễ dàng hơn.
- Reusability: CTE có thể được sử dụng lại trong nhiều phần của truy vấn hoặc trong nhiều truy vấn, làm cho chúng trở thành một công cụ hữu ích để viết mã mô-đun và có thể bảo trì.
Nhược điểm:
- Limited support: CTE không được hỗ trợ bởi 1 vài hệ thống quản lý cơ sở dữ liệu như MS Access và MYSQL version < 8.0 . Do đó, có thể cần phải viết lại truy vấn trong các cơ sở dữ liệu khác không hỗ trợ CTE hoặc sử dụng các giải pháp thay thế như subquery.
- Nesting: CTE có thể được lồng vào nhau nhưng nó có thể làm cho truy vấn phức tạp hơn và khó đọc hơn. Nên tránh lồng nhau để duy trì khả năng đọc của truy vấn.
- Overhead: CTE có thể thêm một số xử lý vào quá trình thực thi truy vấn và làm giảm hiệu suất. Điều này có thể được giảm thiểu bằng cách sử dụng chỉ mục và các kỹ thuật nâng cao hiệu suất khác.

IV: Phân biệt Subquery với CTE

Đây là so sánh sự khác biệt giữa CTE và subquery trong SQL:

Như bạn có thể thấy, CTE và subquery có một số khác biệt về định nghĩa, khả năng đọc, khả năng sử dụng lại và đặt tên. CTE thường được ưu tiên khi một truy vấn phức tạp và cần được chia thành các phần nhỏ hơn, dễ quản lý hơn, đồng thời nó cũng làm tăng khả năng sử dụng lại và giúp duy trì truy vấn. Trong khi subquery rất hữu ích khi truy vấn cần truy xuất dữ liệu cần thiết cho truy vấn bên ngoài.

V: CTE có thể thay thế Subquery?

Có, CTE có thể được sử dụng để thay thế subquery trong nhiều trường hợp trong SQL.

CTE cung cấp một cách để đặt tên và sắp xếp tập hợp kết quả của subquery, giúp việc tham chiếu và hiểu toàn bộ truy vấn trở nên dễ dàng hơn. CTE cũng được xác định riêng biệt với truy vấn chính, điều này có thể làm cho truy vấn dễ đọc hơn và dễ bảo trì hơn. Ngoài ra, CTE có thể được sử dụng nhiều lần trong cùng một truy vấn, trong khi truy vấn con thường chỉ được sử dụng một lần.

Trong trường hợp một subquery được sử dụng nhiều lần trong cùng một truy vấn, việc thay thế truy vấn đó bằng CTE có thể cải thiện khả năng đọc và làm cho truy vấn hiệu quả hơn. Nó loại bỏ nhu cầu lặp lại cùng một truy vấn con nhiều lần và làm cho nó có thể tái sử dụng nhiều hơn.

Tuy nhiên, không phải lúc nào cũng cần thay thế subquery bằng CTE, CTE thật sự hữu ích khi bạn có các truy vấn phức tạp và bạn muốn đơn giản hóa chúng cũng như chia nhỏ chúng thành các phần nhỏ hơn, trong khi subquery hữu ích khi truy vấn cần truy xuất dữ liệu cần thiết cho truy vấn bên ngoài. Vì vậy, thật tốt khi biết khi nào nên sử dụng subquery và khi nào nên sử dụng CTE, điều này phụ thuộc vào mức độ phức tạp của truy vấn và các mục tiêu về khả năng bảo trì.

VI: Tổng kết

Ở trên mình đã giới thiệu và phân biệt cho các bạn về subquery và CTE trong SQL theo đúng quy tắc bộ 3 hủy diệt WWH , kèm theo đó là các ví dụ thực tiễn để dễ hình dung nhất có thể. Các bạn có thể tìm hiểu ở những nguồn khác để có thể so sánh và bổ sung kiến thức cho bản thân nhé. Nếu trong quá trình viết bài mình có sai sót hoặc bạn thắc mắc có thể comment vào bài viết này giúp mình nhé. Mình vẫn tiếp tục ra những bài viết mới trong series này và bài tiếp theo mình sẽ làm về views trong SQL mong các bạn ủng hộ. Cảm ơn mọi người ❤️