Truy vấn con (Subquery) trong SQL: Hướng dẫn toàn diện

Truy vấn con (subquery) là một công cụ mạnh mẽ trong quản trị cơ sở dữ liệu, cho phép truy xuất dữ liệu phức tạp hơn và hiệu quả hơn. Hướng dẫn này sẽ đưa bạn đi qua các kiến thức nền tảng về subquery trong SQL, cung cấp góc nhìn về các ứng dụng thực tế và kỹ thuật nâng cao. Dù bạn là người mới hay đã có nhiều kinh nghiệm, thành thạo subquery sẽ nâng cao đáng kể kỹ năng SQL của bạn.

Nếu bạn mới làm quen với SQL, hãy bắt đầu với khóa học SQL Trung cấp để xây dựng nền tảng vững chắc. Ngoài ra, tôi thấy SQL Basics Cheat Sheet mà bạn có thể tải về là một tài liệu tham khảo hữu ích vì nó có hầu hết các hàm SQL phổ biến. Cuối cùng, tôi muốn nói rằng subquery thường xuất hiện trong câu hỏi phỏng vấn SQL, nên nếu bạn đang chuẩn bị phỏng vấn, đây là nơi phù hợp để ôn tập.

SQL Subquery là gì?

Subquery giúp các truy vấn SQL mang tính mô-đun hơn bằng cách xử lý các tác vụ vốn cần nhiều truy vấn riêng lẻ.

Định nghĩa và mục đích

SQL subquery là một truy vấn được lồng trong một truy vấn SQL khác, dùng để thực hiện các thao tác cần nhiều bước hoặc logic phức tạp. Vai trò của subquery trong SQL bao gồm:

• Lọc bản ghi dựa trên dữ liệu từ các bảng liên quan.
• Tổng hợp dữ liệu và thực hiện tính toán một cách linh hoạt.
• Đối chiếu dữ liệu giữa các bảng để rút ra thông tin cụ thể.
• Chọn điều kiện hàng mà không cần join tường minh hoặc logic bên ngoài.

Nghe có vẻ nhiều, nhưng bạn sẽ thấy hợp lý khi chúng ta cùng khám phá trong bài hướng dẫn này.

Các loại subquery

Có thể bạn sẽ bất ngờ khi biết rằng có nhiều loại subquery khác nhau. Các loại này được nhóm dựa trên và phù hợp với các nhu cầu truy xuất dữ liệu khác nhau. Bạn có thể chọn trong số các subquery sau tùy theo thao tác muốn thực hiện:

Scalar subquery

Scalar subquery trả về một giá trị duy nhất, như một hàng và một cột. Thường dùng ở nơi cần một giá trị duy nhất, chẳng hạn trong phép tính, so sánh hoặc gán trong các mệnh đề SELECT hoặc WHERE.

Trong ví dụ dưới đây, scalar subquery (SELECT AVG(salary) FROM employees) trả về một giá trị duy nhất là mức lương trung bình và so sánh với lương của từng nhân viên.

-- Example of Scalar Subquery 
-- Compares each salary to the average salary
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS average_salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Column subquery

Column subquery trả về một cột nhưng nhiều hàng. Thường dùng cùng các toán tử như IN hoặc ANY, nơi truy vấn ngoài so sánh các giá trị từ nhiều hàng.

Ví dụ, subquery dưới đây trả về danh sách ID phòng ban ở New York, sau đó truy vấn chính dùng danh sách đó để lọc nhân viên trong các phòng ban tương ứng.

-- Example of Column Subquery 
-- Filters based on departments in New York
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Row subquery

Row subquery trả về một hàng duy nhất chứa nhiều cột. Thường dùng với các toán tử so sánh có thể so sánh một hàng dữ liệu, như = hoặc IN, khi cần nhiều giá trị.

Subquery sau truy xuất phòng ban và chức danh của một quản lý, và truy vấn ngoài tìm các nhân viên có giá trị khớp.

-- Example of Row Subquery 
-- Matches department and job title with a specific manager
SELECT employee_name
FROM employees
WHERE (department_id, job_title) = (SELECT department_id, job_title FROM managers WHERE manager_id = 1);

Table subquery (bảng dẫn xuất)

Table subquery, hay bảng dẫn xuất, trả về một bảng đầy đủ gồm nhiều hàng và cột. Thường dùng trong mệnh đề FROM như một bảng tạm trong truy vấn.

Ví dụ, subquery dưới đây tạo một bảng dẫn xuất về lương trung bình theo phòng ban, sau đó truy vấn ngoài dùng để tìm các phòng ban có lương trung bình trên một ngưỡng nhất định.

-- Example of Table Subquery 
-- Uses derived table for average department salary comparison
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM 
    (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE dept_avg.avg_salary > 50000;

Tìm hiểu về SQL Subquery

Qua các ví dụ trên, chúng ta thấy subquery được đặt trong dấu ngoặc đơn. Bây giờ hãy khám phá cơ chế hoạt động của subquery, gồm cú pháp và thứ tự thực thi.

Cú pháp và cấu trúc

Cú pháp của subquery thay đổi tùy theo vị trí dùng trong câu lệnh SQL chính, như trong các mệnh đề SELECT, FROM hoặc WHERE. Subquery thường được đặt trong dấu ngoặc đơn ( ), thể hiện một truy vấn tách biệt. 

Ví dụ sau minh họa một subquery trong mệnh đề WHERE, cho phép chúng ta lọc dữ liệu ở truy vấn chính dựa trên kết quả của truy vấn lồng. 

-- Selects the main column to retrieve from the main table to query
SELECT column_name
FROM table_name
-- Applies a condition to filter rows based on the subquery result
WHERE column_name operator 
      -- Subquery retrieves data for comparison in the WHERE clause
      (SELECT column_name FROM table_name WHERE condition);  

Thứ tự thực thi

Thứ tự thực thi của subquery phụ thuộc vào việc chúng là tương quan (correlated) hay không tương quan (non-correlated).

Subquery không tương quan

Subquery không tương quan độc lập với truy vấn ngoài và được thực thi trước. Kết quả của subquery sau đó được truyền cho truy vấn ngoài. Loại này thường dùng cho tính toán và lọc ở cấp giá trị vô hướng hoặc cột.

Truy vấn dưới đây tuân theo thứ tự thực thi:

• Subquery (SELECT AVG(salary) FROM employees) chạy trước và tính lương trung bình.

• Truy vấn ngoài sau đó lấy nhân viên có lương lớn hơn mức trung bình này.

-- Retrieves names of employees with above-average salary
SELECT employee_name  
FROM employees
-- Subquery: calculates average salary across all employees
WHERE salary > (SELECT AVG(salary) FROM employees);  

Tôi khuyên bạn nên học khóa Giới thiệu về SQL Server của DataCamp để tìm hiểu thêm về nhóm và tổng hợp dữ liệu, cũng như cách join bảng.

Subquery tương quan

Subquery tương quan phụ thuộc vào truy vấn ngoài cho một phần dữ liệu, vì vậy chúng được đánh giá lại cho từng hàng mà truy vấn ngoài xử lý. 

Truy vấn sau thực thi theo thứ tự này:

• Với mỗi hàng trong employees (bí danh e1), subquery (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) tính lương trung bình cho chính phòng ban đó.

• Truy vấn ngoài sau đó so sánh lương của từng nhân viên với mức trung bình của phòng ban và chỉ lấy những người có thu nhập cao hơn.

-- Retrieves names of employees with above-average salary in their department
SELECT e1.employee_name  
FROM employees e1
 -- Subquery: calculates average salary for each department
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id); 

Ứng dụng của SQL Subquery

Subquery trong SQL rất quan trọng cho truy xuất và biến đổi dữ liệu, cho phép bạn viết các truy vấn phức tạp và xử lý các tác vụ phân tích nâng cao. Dưới đây là các ứng dụng thực tế của subquery trong quản trị cơ sở dữ liệu.

Lọc dữ liệu

Subquery hữu ích khi lọc dữ liệu dựa trên các điều kiện động, đặc biệt khi việc lọc yêu cầu so sánh giá trị giữa nhiều bảng hoặc thực hiện tính toán.

Subquery sau truy xuất category_id của "Product A", và truy vấn chính tìm tất cả sản phẩm trong danh mục đó.

-- Retrieves names of products in the same category as 'Product A'
SELECT product_name  
FROM products
 -- Subquery: finds category ID of 'Product A'
WHERE category_id = (SELECT category_id FROM products WHERE product_name = 'Product A'); 

Tổng hợp dữ liệu

Subquery cũng được dùng để tổng hợp dữ liệu, đặc biệt khi tạo thống kê tóm tắt hoặc thông tin phục vụ báo cáo và phân tích. Subquery (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) tính doanh số trung bình theo phòng ban. Truy vấn ngoài sau đó lọc các phòng ban có doanh số trung bình trên 50.000. 

-- Retrieves department IDs and their average sales
-- Filters for departments with average sales over 50,000
SELECT department_id, avg_sales  
FROM (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) AS dept_sales  -- Subquery: calculates average sales per department
WHERE avg_sales > 50000;  

Lưu ý về hiệu năng và Thực hành tốt

Mặc dù subquery rất mạnh khi viết các truy vấn phức tạp, chúng có thể ảnh hưởng hiệu năng, đặc biệt với tập dữ liệu lớn. Cần lưu ý các cạm bẫy thường gặp và thực hành tốt để cải thiện hiệu năng.

Tối ưu hiệu năng subquery

Tối ưu subquery giúp cải thiện thời gian thực thi và khả năng phản hồi của cơ sở dữ liệu. Dưới đây là các cách tối ưu subquery.

• Đánh chỉ mục các cột liên quan: Để tăng tốc truy xuất dữ liệu, hãy đảm bảo các cột dùng trong mệnh đề WHERE và JOIN cùng các phép so sánh đều được đánh chỉ mục.

• Hạn chế dùng subquery tương quan: Khi có thể, hãy dùng JOIN hoặc CTE thay cho subquery tương quan, vì chúng thường xử lý dữ liệu nhanh hơn nhờ thao tác theo tập thay vì theo từng hàng.

• Giới hạn số cột trong subquery: Chỉ chọn các cột cần thiết trong subquery để giảm lượng dữ liệu truy xuất, tiết kiệm bộ nhớ và giúp hệ quản trị tối ưu thực thi.

• Dùng EXISTS thay vì IN: Nếu subquery trả về tập dữ liệu lớn, dùng EXISTS thay cho IN có thể cải thiện hiệu năng. Toán tử EXISTS sẽ dừng xử lý khi tìm thấy hàng khớp, còn IN tiếp tục đánh giá toàn bộ kết quả subquery.

Tránh các cạm bẫy thường gặp

Viết subquery không đúng có thể gây lỗi khi thực thi. Hãy xem cách tránh các cạm bẫy này.

• Tránh subquery tương quan không cần thiết: Subquery tương quan tốn tài nguyên, nên tránh dùng khi có thể đạt kết quả bằng subquery không tương quan hoặc join.

• Lưu ý giá trị NULL trong subquery: NULL có thể dẫn đến kết quả không như mong đợi, nhất là trong subquery dùng các toán tử so sánh như IN hoặc =. Để tránh lỗi, cân nhắc dùng COALESCE để xử lý null hoặc đảm bảo các cột dùng để so sánh không cho phép null.

• Tránh dùng SELECT * trong subquery: Dùng SELECT * gây kém hiệu quả vì truy xuất mọi cột, kể cả khi không cần. Điều này tăng sử dụng bộ nhớ và có thể làm chậm thực thi, nhất là với tập dữ liệu lớn.

• Dùng bí danh có ý nghĩa: Đặt tên rõ ràng cho bảng và subquery để cải thiện khả năng đọc.

Hãy thử lộ trình nghề nghiệp SQL Server Developer của chúng tôi, giúp bạn trang bị kỹ năng viết, khắc phục sự cố và tối ưu truy vấn với SQL Server. 

Các kỹ thuật subquery SQL nâng cao

Mặc dù subquery mang lại cách viết truy vấn phức tạp hiệu quả, vẫn có các phương pháp nâng cao để xử lý dữ liệu phân cấp. Hãy xem các kỹ thuật và chiến lược nâng cao để áp dụng subquery trong SQL.

Subquery đệ quy

Subquery đệ quy (còn gọi là biểu thức bảng chung đệ quy – recursive CTE) cho phép bạn truy xuất dữ liệu phân cấp, như cơ cấu tổ chức, danh mục sản phẩm hoặc quan hệ dạng đồ thị, nơi mỗi phần tử liên kết với một phần tử khác.

Giả sử bạn có bảng employees với employee_id, manager_id và employee_name. Bạn muốn truy xuất hệ thống phân cấp của nhân viên dưới một quản lý cụ thể.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Start with the specified manager
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
 -- Assuming the top-level manager has NULL as manager_id
    WHERE manager_id IS NULL 
    
    UNION ALL
    
    -- Recursive Query: Find employees who report to those in the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Trong truy vấn trên:

• Điều tôi gọi là Anchor Query chọn quản lý cấp cao nhất (nơi manager_id là NULL).

• Recursive Query join employees với chính CTE (EmployeeHierarchy), tìm nhân viên báo cáo cho mỗi nhân viên vừa truy xuất ở cấp trước.

• Đệ quy tiếp tục cho đến khi không còn nhân viên nào báo cáo cho các nhân viên đã tìm thấy.

Kết hợp subquery với các tính năng SQL khác

Bạn có thể tích hợp subquery với các tính năng SQL khác như hàm cửa sổ (window functions), câu lệnh CASE và các hàm nhóm. Những kết hợp này cho phép thao tác dữ liệu nâng cao và báo cáo toàn diện hơn.

Kết hợp subquery với hàm cửa sổ

Subquery có thể dùng để tinh chỉnh tập dữ liệu mà hàm cửa sổ vận hành, hữu ích cho xếp hạng, tổng lũy kế và trung bình trượt. Giả sử bạn muốn xếp hạng sản phẩm theo doanh số trong từng vùng. Bạn có thể dùng subquery để chọn dữ liệu liên quan rồi áp dụng hàm cửa sổ để xếp hạng.

 -- Ranks products by sales within each region
SELECT region, product_id, sales, 
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank 
-- Subquery: calculates total sales per product in each region
FROM (SELECT region, product_id, SUM(sales) AS sales  
      FROM sales_data
      GROUP BY region, product_id) AS regional_sales;

Dùng subquery với câu lệnh CASE

Kết hợp subquery với CASE giúp bạn áp dụng các điều kiện phức tạp dựa trên tính toán động. Truy vấn sau phân loại sản phẩm thành “Cao”, “Trung bình” hoặc “Thấp” dựa trên doanh số so với doanh số trung bình của danh mục.

 -- Categorize above-average sales, average sales, and below-average sales
SELECT product_id, category_id, sales,
       CASE 
           WHEN sales > (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'High' 
           WHEN sales = (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'Medium' 
           ELSE 'Low'
       END AS performance
FROM products AS p;

Subquery với hàm tổng hợp cho tổng hợp có điều kiện

Bạn cũng có thể tính tổng hợp có điều kiện bằng cách dùng subquery trong các hàm tổng hợp. Giả sử bạn muốn tính tổng doanh thu tạo ra chỉ bởi khách hàng đang hoạt động. Trong ví dụ dưới, subquery truy xuất tất cả khách hàng đang hoạt động. Truy vấn chính sau đó lọc đơn hàng chỉ gồm các đơn do nhóm khách hàng này đặt, và tính tổng doanh thu từ nhóm đó.

 -- Calculates total revenue from active customers
SELECT SUM(order_total) AS active_customer_revenue 
FROM orders
  -- Subquery: retrieves IDs of active customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);

Khi nào dùng SQL Subquery

Subquery trong SQL có nhiều ứng dụng thực tế cho phân tích dữ liệu, đó là lý do chúng được dùng thường xuyên. Dưới đây là một số ứng dụng theo ngành hoặc liên quan đến toán học của subquery. Để đưa ra những ý tưởng này, tôi nghĩ về các công việc phân tích hoặc kỹ sư dữ liệu cần nhiều bước, theo tôi, đó là nơi subquery thực sự phát huy tác dụng.

Ví dụ theo ngành

Subquery có thể mang lại giải pháp hữu ích trong các ngành tài chính, chăm sóc sức khỏe và bán lẻ. Dưới đây là một vài ý tưởng:

• Đánh giá rủi ro phê duyệt khoản vay (Tài chính): Tôi hình dung các ngân hàng phải cân đối các chỉ số như tỷ lệ nợ trên thu nhập và điểm tín dụng. Bằng cách lồng các chỉ số này trong subquery, nhà phân tích có thể hiểu rõ hơn các thước đo tài chính phức tạp. Ví dụ, một subquery có thể tính khoản vay trung bình cho khách hàng trong các nhóm thu nhập cụ thể.

• Nhận diện mẫu chẩn đoán của bệnh nhân (Y tế): Trong y tế, quản lý dữ liệu bệnh nhân là một công việc lớn. Subquery có thể giúp phân rã sự phức tạp này. Tôi hình dung dùng subquery để theo dõi tần suất chẩn đoán theo nhóm tuổi hoặc yếu tố rủi ro.

• Tối ưu bày trí sản phẩm dựa trên lịch sử mua hàng (Bán lẻ): Nhà bán lẻ thành công nhờ hiểu mô hình mua sắm. Subquery có thể hiển thị dữ liệu mua lồng nhau để xác định các sản phẩm thường được mua cùng nhau. Điều này giúp bày trí sản phẩm bổ trợ một cách chiến lược và tăng doanh số.

Liên hệ toán học

Subquery cũng dùng để nhận diện mẫu và xu hướng dữ liệu theo các liên hệ toán học và logic. Dưới đây là một số kịch bản áp dụng subquery trong toán học.

• Trung bình trượt cho phân tích chuỗi thời gian: Khi phân tích xu hướng theo thời gian, subquery giúp đơn giản hóa việc tính trung bình trượt. Tôi thấy chúng hữu ích để xác định các cửa sổ thời gian cụ thể trong truy vấn lồng, giúp làm mượt dữ liệu và phát hiện xu hướng.

• Phát hiện ngoại lai bằng độ lệch chuẩn: Phát hiện ngoại lai rất quan trọng cho nhiều việc, bao gồm phát hiện gian lận. Subquery giúp tính các thước đo như độ lệch chuẩn một cách trực tiếp trong truy vấn lồng.

• Vận dụng khái niệm lý thuyết tập hợp: Tôi thấy thú vị khi subquery phản chiếu các phép toán trong lý thuyết tập hợp như UNION và INTERSECT. Khả năng này rất phù hợp cho các tác vụ như phân tích giữ chân khách hàng, nơi việc hiểu phần giao và khác biệt giữa các nhóm khách hàng có thể thúc đẩy chiến lược tiếp thị thông minh hơn.

Kết luận

Thành thạo subquery trong SQL sẽ nâng cao đáng kể khả năng quản lý và phân tích dữ liệu của bạn một cách hiệu quả. Bằng cách hiểu cấu trúc, ứng dụng và thực hành tốt, bạn có thể tối ưu các truy vấn SQL để đạt hiệu năng tốt hơn. Ngoài ra, tôi muốn nói rằng thành thạo subquery khiến việc viết SQL dễ dàng hơn, nên rất đáng để học.

Nếu bạn muốn trở thành một nhà phân tích dữ liệu thành thạo, hãy xem lộ trình nghề nghiệp Associate Data Analyst in SQL để học các kỹ năng cần thiết. Khóa học Reporting in SQL cũng phù hợp nếu bạn muốn học cách xây dựng dashboard chuyên nghiệp bằng SQL. Cuối cùng, tôi khuyến nghị lấy Chứng chỉ SQL Associate để chứng minh năng lực dùng SQL cho phân tích dữ liệu và tạo lợi thế so với các chuyên gia dữ liệu khác.