Giới thiệu

Trong lập trình SQL, subquery, Common Table Expressions (CTE) và stored procedures là những công cụ rất quan trọng giúp quản lý và xử lý dữ liệu hiệu quả. Tuy nhiên, mỗi công cụ có mục đích và cách sử dụng khác nhau. Bài viết này sẽ giúp bạn hiểu rõ hơn về sự khác biệt giữa chúng thông qua các ví dụ thực tế, cũng như hướng dẫn cách áp dụng chúng trong các tình huống cụ thể.

Mục lục

1. Định nghĩa
   • Subquery
   • CTE (Common Table Expressions)
   • Stored Procedure
2. Sự khác biệt
3. Thực hành tốt nhất
4. Cạm bẫy phổ biến
5. Mẹo hiệu suất
6. Khắc phục sự cố
7. Câu hỏi thường gặp

Định nghĩa

Subquery

• Subquery là một truy vấn lồng trong một truy vấn khác. Nó thường được sử dụng trong các câu lệnh SELECT, FROM hoặc WHERE để lấy kết quả trung gian. Subquery được đặt trong dấu ngoặc đơn () và có thể sử dụng như sau:

Trong câu lệnh SELECT

Subquery trong câu lệnh SELECT được sử dụng để thêm một cột bổ sung vào tập kết quả bằng cách tính toán giá trị dựa trên một truy vấn khác.

SELECT first_name, last_name, 
    (SELECT COUNT(*) FROM mental_health_visits WHERE mental_health_visits.patient_id = mental_health_patients.patient_id) AS total_visits 
FROM mental_health_patients;

Trong câu lệnh FROM

Subquery trong câu lệnh FROM được sử dụng để tạo một bảng tạm thời (gọi là derived table) mà truy vấn bên ngoài có thể sử dụng.

SELECT diagnosis, total_visits 
FROM (SELECT diagnosis, COUNT(visit_id) AS total_visits 
      FROM mental_health_patients 
      JOIN mental_health_visits ON mental_health_patients.patient_id = mental_health_visits.patient_id 
      GROUP BY diagnosis) AS diagnosis_visits;

Trong câu lệnh WHERE

Subquery trong câu lệnh WHERE được sử dụng để lọc các bản ghi dựa trên kết quả của một truy vấn khác.

SELECT first_name, last_name 
FROM mental_health_patients 
WHERE patient_id IN 
    (SELECT patient_id 
     FROM mental_health_visits 
     JOIN mental_health_doctors ON mental_health_visits.doctor_id = mental_health_doctors.doctor_id 
     WHERE mental_health_doctors.specialization = 'Psychologist');

Subquery tương quan

Một subquery tương quan là một subquery tham chiếu đến các cột từ truy vấn bên ngoài. Subquery này sẽ được thực hiện cho mỗi hàng được xử lý bởi truy vấn bên ngoài.

SELECT first_name, last_name 
FROM mental_health_patients 
WHERE (SELECT COUNT(*) 
      FROM mental_health_visits 
      WHERE mental_health_visits.patient_id = mental_health_patients.patient_id 
      AND mental_health_visits.visit_type = 'Emergency') > 1;

CTE (Common Table Expressions)

• CTE là một tập kết quả tạm thời trong SQL mà có thể được tham chiếu trong một truy vấn SELECT, INSERT, UPDATE hoặc DELETE.
• CTE giúp cải thiện khả năng đọc và bảo trì của các truy vấn bằng cách cho phép bạn chia nhỏ các truy vấn phức tạp thành các thành phần mô-đun.

WITH cte_name AS (SELECT column1, column2 FROM table_name WHERE conditions) 
SELECT * FROM cte_name;

Stored Procedure

• Stored Procedure là một tập hợp các câu lệnh SQL được biên dịch trước và lưu trữ trong cơ sở dữ liệu, có thể được thực thi như một đơn vị duy nhất. Nó hoạt động như một hàm tái sử dụng cho cơ sở dữ liệu.
• Được lưu trữ trong cơ sở dữ liệu — một khi được tạo, nó có thể được sử dụng nhiều lần.
• Tái sử dụng — được gọi khi cần mà không cần viết lại mã SQL.
• Có thể chấp nhận tham số — để xử lý các giá trị đầu vào khác nhau.
• Hỗ trợ kiểm soát luồng — như IF, WHILE, LOOP, v.v.
• Cải thiện hiệu suất — giảm thiểu việc gửi nhiều truy vấn qua mạng.

CREATE PROCEDURE GetAllCustomers AS 
BEGIN 
    SELECT * FROM students; 
END;

Để thực thi:

EXEC GetAllCustomers;

Sự khác biệt giữa Subquery, CTE và Stored Procedure

1. Subquery là một truy vấn được đặt bên trong một truy vấn SQL khác, thường được sử dụng trong các câu lệnh SELECT, FROM hoặc WHERE. Nó giúp lấy các kết quả trung gian để sử dụng cho truy vấn chính. Subqueries tạm thời, không thể tái sử dụng và không hỗ trợ logic như điều kiện hoặc vòng lặp. Chúng thường được sử dụng để lọc hoặc tính toán các giá trị trong một câu lệnh SQL duy nhất.

2. CTE cũng là một tập kết quả tạm thời, nhưng nó được định nghĩa bằng cách sử dụng câu lệnh WITH ở đầu truy vấn. Không giống như subqueries, CTE có thể giúp các truy vấn phức tạp dễ đọc và dễ quản lý hơn. Chúng cho phép bạn chia nhỏ một truy vấn lớn thành các phần nhỏ hơn, hợp lý. Mặc dù CTE không thể được tái sử dụng bên ngoài truy vấn mà nó được định nghĩa trong đó, chúng rất hữu ích khi xử lý đệ quy hoặc tham chiếu lặp lại trong cùng một truy vấn.

3. Stored Procedure là một khối mã SQL đã được biên dịch và lưu trữ trong cơ sở dữ liệu. Nó có thể được tái sử dụng nhiều lần, chấp nhận tham số và hỗ trợ logic nâng cao như các câu lệnh điều kiện và vòng lặp. Stored procedures là công cụ lý tưởng cho các tác vụ cần được lặp lại thường xuyên hoặc cho việc đóng gói các quy tắc kinh doanh một cách an toàn và tập trung.

Tóm lại: subqueries tốt nhất cho việc lọc nhanh và nội tuyến; CTE tuyệt vời cho việc đơn giản hóa và tổ chức các truy vấn phức tạp; và stored procedures là công cụ mạnh mẽ cho khả năng tái sử dụng, tự động hóa và logic quy trình trong cơ sở dữ liệu.

Thực hành tốt nhất

• Sử dụng Subquery một cách hợp lý: Nên dùng subquery khi bạn cần lấy dữ liệu đơn giản và không quá phức tạp. Tránh lồng nhiều subquery trong nhau vì có thể làm giảm hiệu suất.
• CTE cho truy vấn phức tạp: Khi bạn có một truy vấn phức tạp, hãy sử dụng CTE để dễ dàng theo dõi và bảo trì mã.
• Stored Procedure cho tái sử dụng: Khi bạn cần thực hiện một tác vụ thường xuyên, hãy xem xét việc sử dụng stored procedure để giảm thiểu mã lặp lại.

Cạm bẫy phổ biến

• Subquery hiệu suất kém: Sử dụng quá nhiều subquery có thể làm giảm hiệu suất truy vấn. Hãy xem xét việc tối ưu hóa hoặc sử dụng JOIN thay thế.
• CTE không thể tái sử dụng: Cần lưu ý rằng CTE không thể được sử dụng bên ngoài truy vấn mà nó được định nghĩa trong đó.
• Quản lý Stored Procedure: Nếu không được quản lý tốt, stored procedure có thể trở nên phức tạp và khó bảo trì.

Mẹo hiệu suất

• Tối ưu hóa subquery: Cố gắng viết các subquery đơn giản và dễ hiểu để tránh làm tăng độ phức tạp của truy vấn chính.
• Sử dụng CTE để giảm độ phức tạp: Thay vì viết các truy vấn phức tạp trong một câu lệnh duy nhất, hãy chia chúng thành các phần nhỏ hơn sử dụng CTE.
• Kiểm tra hiệu suất của stored procedure: Theo dõi và tối ưu hóa stored procedure để đảm bảo rằng chúng hoạt động hiệu quả nhất có thể.

Khắc phục sự cố

• Lỗi trong subquery: Nếu subquery không trả về kết quả như mong đợi, hãy kiểm tra điều kiện và đảm bảo rằng dữ liệu đầu vào là chính xác.
• CTE không hoạt động: Nếu CTE không hoạt động như mong muốn, hãy xem lại cú pháp và các điều kiện được sử dụng.
• Stored procedure không thực thi được: Kiểm tra xem các tham số đã được cung cấp đúng cách chưa và có lỗi cú pháp trong mã hay không.

Câu hỏi thường gặp

• Khi nào nên sử dụng subquery thay vì CTE?
  Subquery thường nhanh hơn cho các tác vụ đơn giản, trong khi CTE tốt hơn cho các truy vấn phức tạp.
• Có thể sử dụng CTE trong stored procedure không?
  Có, bạn có thể sử dụng CTE trong stored procedure để cải thiện khả năng đọc và bảo trì mã.
• Tôi có thể gọi stored procedure từ một subquery không?
  Không, bạn không thể gọi stored procedure từ một subquery, nhưng bạn có thể gọi nó từ một truy vấn chính.

Kết luận

Subquery, CTE và stored procedures đều là những công cụ mạnh mẽ trong SQL, mỗi công cụ phục vụ một mục đích cụ thể và mang lại lợi ích riêng. Bằng cách hiểu rõ sự khác biệt và cách sử dụng từng công cụ, bạn có thể tối ưu hóa truy vấn của mình và nâng cao hiệu quả làm việc với cơ sở dữ liệu. Hãy thử áp dụng những kiến thức này vào thực tiễn và khám phá cách chúng có thể cải thiện quy trình làm việc của bạn trong SQL.

Bạn có ý kiến hay câu hỏi nào không? Hãy để lại bình luận bên dưới để thảo luận thêm!