TM Data School – Việc tổ chức và thực hiện các truy vấn SQL phức tạp một cách hiệu quả là rất quan trọng trong quản lý dữ liệu. Để làm được điều này, có 2 “công cụ” thường được sử dụng để phân tầng và quản lý độ phức tạp của truy vấn là Common Table Expressions (CTE) và Subqueries. Vậy 2 kỹ thuật này có gì khác nhau? Hãy cùng tìm hiểu rõ hơn trong bài viết này nhé!
Đọc thêm: SQL là gì? Tất tần tật những điều bạn cần biết về SQL
Common Table Expressions (CTE) là gì?
1. CTE là gì?
Common Table Expression (CTE) là tập kết quả của một truy vấn SQL được đặt tên. CTE giúp:
- Sắp xếp truy vấn gọn gàng
- Đảm bảo tính tường minh, giúp người đọc dễ hiểu “ý đồ” từng bước của một truy vấn phức tạp, từ đó cũng dễ dàng tìm ra logic sai trong truy vấn
- Giúp tận dụng kết quả của một truy vấn phụ nhiều lần trong suốt một truy vấn chính vấn
Tóm tắt các ưu điểm của CTE – Slide khóa SQL for Data Analysis
2. Syntax của CTE
Syntax của CTE sử dụng từ khóa WITH và tên tạm của kết quả truy vấn để có thể định danh kết quả truy vấn đó và dùng lại nhiều lần trong suốt truy vấn chính:
WITH cte_name AS (SELECT …)
3. Ưu và nhược điểm khi sử dụng CTE
| Ưu điểm | Nhược điểm |
| – CTE giúp việc duy trì và sửa lỗi code/query dễ dàng hơn. – CTE giúp code/query dễ đọc hiểu hơn – CTE giúp tăng hiệu suất và giảm thời gian thực hiện truy vấn – CTE cho phép thực hiện truy vấn đệ quy (vòng lặp nhiều lần) | – CTE chỉ có thể được nhắc tới một lần trong một truy vấn đệ quy – CTE không được coi là một object được lưu trữ riêng biệt trong cơ sở dữ liệu – Một CTE có thể thay thế cho view, nhưng khác view ở chỗ không thể được dùng trong một query khác biệt (nested) |
4. Các loại CTE
Non-Recursive CTE (CTE không đệ quy)
Non-Recursive CTE là những CTE không sử dụng bất kỳ vòng lặp nào, tức là kết quả được trả về từ CTE không được tiếp tục sử dụng để thực hiện chính truy vấn đó. Nói cách khác, CTE không đệ quy được sử dụng để tạo ra một bảng tạm thời trong phạm vi của một truy vấn, và bảng này chỉ tồn tại cho đến khi truy vấn hoàn thành.
Giả sử, để tạo ra một truy vấn chứa 10 hàng với 10 con số từ 1 tới 10, có thể truy vấn:
WITH example AS (
SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases
WHERE database_id <= 10
)SELECT * FROM example
Kết quả:
Recursive CTE (CTE đệ quy)
Recursive CTE sử dụng các vòng lặp hay còn gọi là đệ quy. CTE đệ quy sẽ sử dụng kết quả truy vấn đầu tiên để tiếp tục thực hiện truy vấn và sẽ chỉ dừng khi thỏa mãn điều kiện chấm dứt.
CTE đệ quy phải chứa ít nhất hai thành phần truy vấn, thành phần gốc (anchor member) và thành phần đệ quy (recursive member). Một truy vấn có thể bao gồm nhiều thành phần gốc và nhiều thành phần đệ quy. Tuy nhiên, tất cả thành phần gốc cần phải đứng trước thành phần đệ quy. Tất cả CTE đều là thành phần gốc nếu không tự sử dụng kết quả truy vấn của chính mình để tiếp tục thực hiện truy vấn.
CTE đệ quy thường được dùng khi làm việc với dữ liệu phân cấp vì CTE tiếp tục thực thi cho đến khi truy vấn trả về toàn bộ hệ thống phân cấp.
Ví dụ về dữ liệu phân cấp là một bảng danh sách bao gồm tất cả nhân viên của một công ty. Với mỗi nhân viên, sẽ có thêm một field là mentor (người hỗ trợ trực tiếp trong công việc). Giá trị của field chính là id của một nhân viên khác trong công ty. CTE đệ quy được sử dụng để xác định thứ bậc của dữ liệu nhân viên, liên tục tìm ra mentor của nhân viên đó và chỉ dừng khi người cuối cùng có cấp bậc cao nhất và không có ai hướng dẫn công việc nữa.
Nếu không có điều kiện chặn, CTE đệ quy có thể rơi vào một vòng lặp vô hạn. Để không rơi vào tình huống này, hãy đảm bảo logic của truy vấn có thể dừng lại, hoặc với SQL Server, bạn sẽ cần bổ sung mệnh đề OPTION(MAXRECUSION x) với x là con số cụ thể số lượng vòng lặp có thể thực hiện.
Giả sử, cùng là bài toán tạo ra 10 dòng từ 1 tới 10, có thể sử dụng CTE đệ quy bằng cách:
Đầu tiên, khai báo biến rowno với giá trị mặc định là số nguyên 1, là giá trị đầu tiên của chuỗi đệ quy. Tiếp theo, truy vấn CTE với điều kiện rowno + 1 và UNION ALL cho tới khi RowNo < 10. Truy vấn sẽ lấy giá trị đầu tiên của chuỗi đệ quy là 1, sau đó thực hiện cộng 1 với giá trị mặc định và UNION, tiếp tục cộng 1 và UNION, cho tới khi giá trị sau khi cộng 1 là số nguyên lớn nhất và nhỏ hơn 10.
WITH example AS (
SELECT 1::INT as rowno
UNION ALL
SELECT rowno + 1
FROM example
WHERE rowno < 10
)
SELECT * FROM example
Kết quả:
Đọc thêm: Hướng dẫn tự học SQL và các nguồn luyện tập dành cho người mới bắt đầu
Subquery
1. Subquery là gì?
Subquery là một truy vấn con nằm trong một truy vấn SQL khác.
Subquery được hỗ trợ bởi hầu hết các cơ sở dữ liệu SQL, bao gồm MySQL, PostgreSQL, Oracle, SQL Server và SQLite, mỗi cơ sở dữ liệu sẽ có hiệu suất thực hiện truy vấn khác nhau.
Subquery thường được sử dụng khi cần lọc điều kiện hoặc tính toán dựa trên các giá trị kết quả của một truy vấn khác, hoặc khi cần thực hiện phép JOIN với một truy vấn đã có điều kiện, nhằm giúp tăng hiệu suất truy vấn (nhờ cần JOIN ít bản ghi hơn).
Tóm tắt ưu điểm của việc dùng Subqueries – Slide khóa học SQL for Data Analysis
Subquery sẽ bắt đầu với dấu “(“, theo sau là câu lệnh truy xuất dữ liệu và kết thúc với dấu “)”. Đặc biệt, khi kết hợp với JOIN, subquery được coi như một tập dữ liệu và cần được định danh với ALIAS.
2. Có các loại sub-query nào?
Nested subquery (truy vấn lồng)
Nested subquery trong SQL, hay còn được biết là inner queries hay subselects, là các truy vấn được nhúng trong một truy vấn khác, thường ở dạng câu lệnh SQL. Truy vấn con trả về một bảng tạm, sau đó truy vấn SQL chính bên ngoài sẽ sử dụng bảng này để tinh chỉnh thêm các kết quả. Nested subquery thường được sử dụng khi bạn cần lọc hoặc thao tác dữ liệu từ nhiều bảng, hoặc khi kết quả của một truy vấn sẽ được tính toán dựa trên kết quả của một truy vấn khác.
Trong nested subquery, truy vấn sẽ thực hiện theo từng lớp từ trong ra ngoài và chỉ chạy duy nhất một lần, truy vấn ngoài sẽ sử dụng kết quả từ truy vấn trong để tiếp tục thực hiện các hàm, các phép tổng hợp/tính toán.
Nested subquery sẽ là điểm cộng khi xử lý các tác vụ lọc và thao tác dữ liệu phức tạp:
- Tính linh hoạt (Flexibility): Nested subquery cung cấp cách truy cập dữ liệu từ nhiều bảng ngoài cách dựa vào JOIN, giúp linh hoạt hơn trong việc xử lý các mối quan hệ phức tạp giữa các bảng
- Tính dễ đọc (Readability): Nested subquery chia câu lệnh SQL phức tạp thành các phần nhỏ để dễ đọc hiểu logic và thứ tự tư duy của người query, từ đó cũng dễ debug khi query gặp lỗi hơn.
- Tính lọc dữ liệu (Data Filtering): Nested subquery giúp lọc dữ liệu dựa trên kết quả của các truy vấn bên trong nên có thể lọc dữ liệu theo điều kiện nâng cao mà không cần thực hiện nhiều câu lệnh SQL riêng biệt
- Tính hiệu năng (Performance): Nested subquery cũng có thể cải thiện hiệu suất bằng cách giảm nhu cầu lặp lại nhiều lần của cùng một truy vấn hoặc yêu cầu ít lệnh gọi cơ sở dữ liệu hơn để truy xuất kết quả mong muốn
- Tính thao tác với dữ liệu (Data Manipulation): Nested subquery có thể được sử dụng để tính toán các giá trị cần có để tiếp tục thực hiện các phép so sánh ở truy vấn ngoài
Một truy vấn có thể sử dụng subquery có nhiều cấp độ truy vấn (nesting levels).
Scalar Subquery (truy vấn con vô hướng)
Subquery vô hướng là các truy vấn trả về duy nhất một giá trị (của một express/một cột và một hàng). Một scalar subquery có thể được tận dụng nhiều lần trong các mệnh đề như SELECT, WHERE hoặc HAVING, tương tự các subquery khác.
Ví dụ:
Giả sử chúng ta có bảng employees chứa dữ liệu của các nhân viên của một công ty với các trường như: employee_id, first_name, last_name, salary, và department_id. Để truy xuất danh sách các nhân viên có mức lương lớn hơn trung bình lương của bộ phận của nhân viên đó, có thể thực hiện phép truy vấn:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees GROUP BY department_id);
Subquery vô hướng sẽ tính mức lương trung bình của mỗi phòng ban, sau đó truy vấn bên ngoài (outer query) sẽ so sánh mức lương của mỗi nhân viên với giá trị trung bình đó để trả về các nhân viên thỏa mãn điều kiện.
Row Subquery (truy vấn con cấp hàng)
Subquery cấp hàng sẽ trả về một hàng với nhiều cột. Row subquery có thể được sử dụng trong mệnh đề WHERE và HAVING với các toán tử so sánh như =, <>, >, <, >=, và <= hoặc IN, EXISTS, ANY và ALL.
Ví dụ
Với bảng employees đã có, để truy vấn những nhân viên có họ và tên có cùng tên với nhân viên có mức lương cao nhất, có thể thực hiện phép truy vấn:
SELECT first_name, last_name, salary
FROM employees
WHERE (first_name, last_name) IN (
SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees)
);
Scalar subquery sẽ tính toán mức lương cao nhất của tất cả nhân viên, sau đó sử dụng kết quả để trả về họ và tên của (một hoặc vài) nhân viên có mức lương bằng với giá trị đó. Cuối cùng, truy vấn ngoài cùng sẽ trả về những nhân viên có họ và tên trùng với kết quả của truy vấn con thứ hai.
Table Subquery (truy vấn con cấp bảng)
Table subquery sẽ trả về toàn bộ một bảng dữ liệu và có thể được sử dụng trong mệnh đề FROM như một bảng tạm hoặc như sử dụng với điều kiện WHERE/HAVING với toán tử IN và EXISTS.
Ví dụ: để truy xuất danh sách các nhân viên đang làm việc cho những bộ phận có nhiều hơn 10 nhân viên, có thể thực hiện phép truy vấn:
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 10
);
Table subquery sẽ trả về những id của các bộ phận (giá trị trường department_id) có nhiều hơn 10 nhân viên. Truy vấn ngoài sau đó sẽ chọn ra những nhân viên đang làm việc tại những bộ phận đó.
Multiple-Row Subquery (truy vấn con nhiều dòng)
Multiple-row subquery là những truy vấn con trả về kết quả nhiều hơn một dòng. Loại truy vấn con này bao gồm (1) các truy vấn con trả về giá trị của một cột với nhiều dòng hoặc (2) giá trị của nhiều cột với nhiều bảng (tương đương với một bảng tạm).
Loại 1 thường được dùng trong các mệnh đề WHERE để lọc ra kết quả của một truy vấn chính và thường được dùng với các toán tử như IN, NOT IN, ANY, ALL, EXISTS hoặc NOT EXISTS để so sánh một giá trị cụ thể với một danh sách các giá trị – kết quả của truy vấn con.
Ví dụ, để tính trung bình mức phí agency của các agent không phải quản lý, bạn có thể thực hiện truy vấn:
SELECT AVG(agency_fee)
FROM sales_agents
WHERE id NOT IN (SELECT id
FROM managers);
Truy vấn trong cùng sẽ trả về danh sách id của các quản lý, sau đó kết quả đó được truy vấn ngoài sử dụng và lọc những agent không có trong danh sách đó để thực hiện phép tính trung bình mức phí agency. Kết quả truy vấn trả về một giá trị ($1885).
Correlated Subquery (truy vấn con tương quan)
Correlated Subquery là các truy vấn có inner query sử dụng kết quả của outer query nhờ có mối quan hệ phụ thuộc. Loại truy vấn này thường được sử dụng trong các mệnh đề SELECT, WHERE và FROM.
Correlated subquery (truy vấn con tương quan) là những truy vấn có thể tận dụng kết quả của outer query cho inner query, trong khi với non-correlated subquery (truy vấn con không tương quan) thì các inner query lại độc lập với outer query và có thể thực hiện tách biệt. Correlated subquery có hiệu suất kém hơn non-correlated subquery bởi inner query phải thực hiện lại truy vấn mỗi lần tương ứng với một dòng của outer query.
Giả sử, để tính số lượng tác phẩm nghệ thuật của tất cả các phòng tranh của một thành phố, có thể thực hiện truy vấn:
SELECT city, (
SELECT count(*)
FROM paintings p
WHERE g.id = p.gallery_id) total_paintings
FROM galleries g;
Truy vấn con sẽ trả về giá trị vô hướng là số lượng tác phẩm tương ứng của mỗi thành phố:
| city | total_paintings |
| London | 2 |
| New York | 2 |
| Munich | 1 |
Với truy vấn này, inner query sẽ phụ thuộc kết quả của outer query, thông qua việc outer query sẽ lấy ra danh sách id của các phòng tranh ở mỗi thành phố, sau đó tạo thêm một trường để đếm số lượng các tác phẩm nghệ thuật của phòng tranh tương ứng và thực hiện phép cộng tổng.
Cùng một mục đích, bạn vẫn có thể sử dụng JOIN thay vì truy vấn con:
SELECT
c.city,
COUNT(p.id) AS total_paintings
FROM galleries g
LEFT JOIN paintings p
ON g.id = p.gallery_id
| city | total_paintings |
| London | 2 |
| New York | 2 |
| Munich | 1 |
3. Cách sử dụng các subquery trong các mệnh đề khác nhau
Subquery trong mệnh đề SELECT
Sử dụng kết hợp subquery trong mệnh đề SELECT sẽ giúp tính toán các giá trị phụ thuộc vào hàng khác trong một bảng.
Giả sử, với bảng employees đã có, để truy xuất họ và tên, mức lương và mức chênh lệch giữa mức lương của nhân viên đó với mức trung bình của bộ phận, bạn có thể thực hiện phép truy vấn:
SELECT
first_name,
last_name,
salary,
salary – (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id) AS salary_diff
FROM employees e1;
Truy vấn con sẽ tính toán mức lương trung bình của mỗi bộ phận, sau đó truy vấn ngoài sẽ tận dụng kết quả đó để thực hiện phép trừ để tính sự chênh lệch so với mỗi nhân viên trong bộ phận.
Subquery trong mệnh đề FROM
Subquery khi được dùng trong mệnh đề FROM sẽ tạo ra một bảng tạm hoặc kết hợp nhiều bảng khác nhau dựa trên một điều kiện cụ thể.
Ví dụ, với bảng employees đã có, để tính được tổng mức lương của các nhân viên trong một bộ phận, có thể thực hiện truy vấn:
SELECT
department_id,
SUM(salary) as total_salary
FROM (
SELECT department_id, salary
FROM employees
) AS derived_table
GROUP BY department_id;
Truy vấn con trả về id của các bộ phận và mức lương của tất cả nhân viên trong bộ phận đó, sau đó truy vấn ngoài sẽ nhóm tổng kết quả theo từng bộ phận.
Subquery trong mệnh đề WHERE
Subquery khi được dùng trong mệnh đề WHERE thường được dùng với mục đích lọc dữ liệu theo một điều kiện dựa trên các dòng khác hoặc một bảng khác.
Ví dụ, với bảng employees đã có, để lấy được danh sách của các nhân viên có mức lương cao hơn mức trung bình của tất cả nhân viên, có thể thực hiện truy vấn:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Truy vấn con sẽ trả về mức lương trung bình của tất cả nhân viên, sau đó truy vấn ngoài sẽ tận dụng kết quả để so sánh với mức lương của mỗi nhân viên.
Subquery trong mệnh đề HAVING
Subquery khi được dùng trong mệnh đề WHERE sẽ thực hiện lọc các giá trị được tổng hợp dựa trên các điều kiện phụ thuộc dòng hoặc bảng khác.
Ví dụ, với bảng employees đã có, để lấy được các bộ phận có tổng mức lương lớn hơn mức trung bình của tất cả bộ phận, có thể thực hiện truy vấn:
SELECT
department_id,
SUM(salary) as total_salary
FROM employees
GROUP BY department_id
HAVING total_salary > (
SELECT AVG(total_salary)
FROM (
SELECT department_id, SUM(salary) as total_salary
FROM employees
GROUP BY department_id
) as department_totals);
Truy vấn con sẽ tính toán tổng mức lương của mỗi bộ phận, sau đó truy vấn ngoài sẽ tính trung bình lương của tất cả bộ phận, sau đó so sánh mức trung bình đó với số tổng của mỗi bộ phận đã tính trước đó.
Đọc thêm: GROUP BY và HAVING của SQL là gì và cách dùng như nào?
4. Tips tối ưu subquery:
- Giới hạn số cấp độ phân cấp (nesting level): Sử dụng xen kẽ JOIN và CTE sẽ giúp bạn giảm bớt cấp độ phân cấp của các truy vấn con. JOIN có thể thay thế nested subquery trong một số trường hợp cần kết hợp dữ liệu theo các trường khóa chính và khóa ngoại, và với hiệu suất tốt hơn. Trong khi đó, CTE nên được sử dụng thay thế khi cần tận dụng kết quả của một truy vấn nhiều lần.
- Tạo chỉ mục cho các bảng cần truy vấn: Lập chỉ mục (index) thích hợp cho các bảng có liên quan có thể cải thiện hiệu suất truy vấn, đặc biệt khi xử lý các tập dữ liệu lớn.
- Sử dụng toán tử hợp lý trong mệnh đề điều kiện: Thay thế IN hoặc NOT IN bằng EXISTS hoặc NOT EXISTS khi kiểm tra xem một giá trị có tồn tại trong bảng khác hay không. Hoặc bạn có thể sử dụng điều kiện logic nếu các giá trị điều kiện có một pattern chung, thay vì sử dụng IN để đối tượng lần lượt.
- Phân tích kế hoạch thực hiện truy vấn để tìm ra mệnh đề nào tốn nhiều chi phí (cost) nhất với EXPLAIN: Khi thực hiện một truy vấn, bạn cần đảm bảo tính hiệu suất của truy vấn đó tới tài nguyên của hệ thống, đặc biệt nếu hệ thống có liên tiếp số lượng truy vấn lớn trong khoảng thời gian ngắn. Để kiểm tra hiệu suất của truy vấn, ví dụ như biết thứ tự thực hiện và mất bao nhiêu thời gian để chạy được kết quả của truy vấn đó, bạn có thể sử dụng mệnh đề EXPLAIN để hiểu được cách cơ sở dữ liệu thực hiện truy vấn đó, bao gồm nhật ký trình tối ưu hóa, cách join các bảng và thứ tự, thực hiện,… Nhờ vậy, bạn có thể biết được đâu là câu lệnh khiến truy vấn chưa đạt được hiệu suất tối ưu, từ đó bổ sung điều kiện giới hạn số lượng bản ghi cần chuyển đổi hoặc bổ sung truy vấn con để giảm tính phức tạp,…
Khi nào nên dùng CTE và Subquery?
| CTE | Subquery |
| Thường sẽ dễ đọc hơn do CTE thường được sử dụng để đảm bảo truy vấn có cấu trúc mạch lạc theo flow logic. | Thường khó đọc hơn, đặc biệt khi truy vấn con có nhiều cấp độ phân cấp |
| CTE được thực hiện trước truy vấn chính | Subquery có thể thực hiện cùng lúc truy vấn chính |
| Có thể “tái sử dụng” | Tái thực hiện truy vấn con mỗi khi cần sử dụng kết quả của truy vấn |
| Có thể thực hiện truy vấn đệ quy | Không thể thực hiện truy vấn đệ quy |
| CTE phải có tên định danh độc nhất trong một truy vấn | Subquery không bắt buộc lúc nào cũng cần đặt tên định danh (ngoại trừ PostgreSQL) |
| CTE không thể dùng trong mệnh đề điều kiện WHERE với toán tử IN hoặc EXISTS | Subquery có thể dùng trong mệnh đề điều kiện WHERE với toán tử IN hoặc EXISTS |
Đọc thêm: Lộ trình Data Analysis chi tiết cho dân trái ngành có background Business
Nói tóm lại, CTE sẽ thích hợp cho các truy vấn cần tái sử dụng kết quả nhiều lần hoặc khi cần thực hiện truy vấn đệ quy, hạn chế việc lặp lại nhiều lần một truy vấn, từ đó có thể tối ưu hiệu suất truy vấn. Trong khi đó, Subqueries lại phù hợp với mục đích thực hiện các phép tổng hợp hoặc lọc dữ liệu theo các điều kiện đặc biệt, nhờ đó giúp tách nhỏ từng bước tư duy khi truy vấn và làm cho câu lệnh SQL linh hoạt hơn.
Tạm kết
Biết những câu lệnh truy vấn và xử lý dữ liệu cơ bản nhất của SQL chính là bước đi đầu tiên, nhưng làm thế nào để kết hợp và vận dụng linh hoạt cho những bài toán phức tạp thì thật không đơn giản. Bạn không chỉ cần tư duy logic, nắm được ý nghĩa của các trường & các bảng dữ liệu mà còn cần phải nắm được loại dữ liệu, vị trí lưu trữ cũng như liên tục trau dồi SQL thông qua các case study khác nhau.
Đây cũng là một trong những nội dung quan trọng được truyền tải trong khóa SQL for Data Analysis của TM Data School. Với 12 buổi học được chia làm 2 học phần, khóa học sẽ giúp bạn:
- Nắm vững tư duy thiết lập thế thống dữ liệu, hiểu cách dữ liệu được tổ chức và lưu trữ, từ đó biết cách trích xuất và xử lý dữ liệu hiệu quả với mỗi một bài toán khác nhau của doanh nghiệp
- Cung cấp các “câu lệnh” SQL từ đơn giản đến nâng cao để bạn có thể chủ động thực hiện các truy vấn nhằm tìm kiếm thông tin, biến đổi dữ liệu để phục vụ cho việc tạo báo cáo phân tích.
Bài viết được tổng hợp và biên dịch bởi TM Data School, xin vui lòng không sao chép dưới mọi hình thức!
