Kỹ thuật truy vấn SQL: So sánh Subqueries, CTE và Stored Procedures

Giới thiệu

SQL cung cấp nhiều phương pháp để thực hiện các thao tác dữ liệu phức tạp. Việc hiểu sự khác biệt giữa subqueries, Common Table Expressions (CTEs), và stored procedures là rất quan trọng để viết mã cơ sở dữ liệu hiệu quả và dễ bảo trì. Trong bài viết này, chúng ta sẽ khám phá các kỹ thuật truy vấn này, cùng với những ưu điểm, nhược điểm, và các tình huống sử dụng tốt nhất cho từng loại.

Subqueries

Định nghĩa

Subquery là một truy vấn lồng trong một câu lệnh SQL khác. Nó được thực thi trước và truyền kết quả cho truy vấn bên ngoài.

Đặc điểm

• Thực thi: Chạy một lần hoặc nhiều lần tùy thuộc vào ngữ cảnh.
• Phạm vi: Giới hạn trong câu lệnh mà nó được định nghĩa.
• Tái sử dụng: Không thể tái sử dụng trong các truy vấn khác nhau.
• Hiệu suất: Có thể kém hiệu quả hơn cho các thao tác phức tạp.

Ví dụ

-- Tìm kiếm nhân viên có mức lương cao hơn mức lương trung bình
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery tương quan
SELECT e1.name, e1.department
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

Tình huống sử dụng tốt nhất

• Điều kiện lọc đơn giản.
• Tính toán một lần.
• Các thao tác EXISTS/NOT EXISTS.

Common Table Expressions (CTEs)

Định nghĩa

CTEs là các tập kết quả tạm thời có tên, chỉ tồn tại trong quá trình thực thi truy vấn. Chúng cải thiện khả năng đọc và có thể được tham chiếu nhiều lần trong cùng một câu lệnh.

Đặc điểm

• Khả năng đọc: Làm cho các truy vấn phức tạp dễ đọc hơn.
• Tái sử dụng: Có thể được tham chiếu nhiều lần trong cùng một truy vấn.
• Đệ quy: Hỗ trợ các thao tác đệ quy.
• Hiệu suất: Tương tự như subqueries nhưng dễ bảo trì hơn.

Ví dụ

-- CTE cơ bản
WITH high_earners AS (
    SELECT name, salary, department
    FROM employees
    WHERE salary > 75000
)
SELECT department, COUNT(*) as high_earner_count
FROM high_earners
GROUP BY department;

-- CTE đệ quy cho dữ liệu phân cấp
WITH RECURSIVE employee_hierarchy AS (
    -- Trường hợp cơ bản
    SELECT employee_id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Trường hợp đệ quy
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Tình huống sử dụng tốt nhất

• Các truy vấn phức tạp cần tham chiếu nhiều lần đến cùng một tập kết quả.
• Các thao tác đệ quy (bảng tổ chức, hóa đơn nguyên liệu).
• Cải thiện khả năng đọc của truy vấn.
• Các thao tác với hàm cửa sổ.

Stored Procedures

Định nghĩa

Stored procedures là các khối mã SQL đã được biên dịch trước và lưu trữ trong cơ sở dữ liệu, có thể chấp nhận tham số và trả về kết quả.

Đặc điểm

• Tồn tại lâu dài: Được lưu trữ vĩnh viễn trong cơ sở dữ liệu.
• Hiệu suất: Được biên dịch trước và lưu vào bộ nhớ đệm để thực thi nhanh hơn.
• Tái sử dụng: Có thể được gọi từ nhiều ứng dụng khác nhau.
• Bảo mật: Cung cấp quyền truy cập có kiểm soát vào dữ liệu.
• Logic: Có thể chứa logic kinh doanh phức tạp với các cấu trúc điều khiển.

Ví dụ

-- Tạo một stored procedure
CREATE PROCEDURE GetEmployeesByDepartment(
    @DepartmentName VARCHAR(50),
    @MinSalary DECIMAL(10,2) = 0
)
AS
BEGIN
    SELECT 
        employee_id,
        name,
        salary,
        hire_date
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = @DepartmentName
    AND e.salary >= @MinSalary
    ORDER BY e.salary DESC;
END;

-- Gọi stored procedure
EXEC GetEmployeesByDepartment @DepartmentName = 'Engineering', @MinSalary = 60000;

Tình huống sử dụng tốt nhất

• Các thao tác thường xuyên được thực hiện.
• Logic kinh doanh phức tạp cần nhiều bước.
• Xác thực và chuyển đổi dữ liệu.
• Các thao tác xử lý theo lô.
• Thực thi các quy tắc kinh doanh tại mức độ cơ sở dữ liệu.

Tóm tắt sự khác biệt chính

Khi nào nên chọn phương pháp nào

Sử dụng Subqueries khi:

• Bạn cần một điều kiện lọc đơn giản, một lần.
• Logic đơn giản và không cần tái sử dụng.
• Làm việc với các tập dữ liệu nhỏ.

Sử dụng CTEs khi:

• Bạn cần tham chiếu cùng một tập kết quả nhiều lần.
• Làm việc với các cấu trúc dữ liệu đệ quy.
• Bạn muốn cải thiện khả năng đọc của truy vấn.
• Phân tách các truy vấn phức tạp thành các bước logic.

Sử dụng Stored Procedures khi:

• Thao tác sẽ được gọi thường xuyên.
• Bạn cần triển khai logic kinh doanh phức tạp.
• Nhiều ứng dụng cần cùng một chức năng.
• Bạn muốn kiểm soát tập trung đối với quyền truy cập dữ liệu.
• Tối ưu hóa hiệu suất là rất quan trọng.

Những điều cần nhớ:

Mỗi phương pháp phục vụ các mục đích khác nhau trong phát triển cơ sở dữ liệu. Subqueries rất tốt cho các thao tác đơn giản, CTEs xuất sắc trong việc làm cho các truy vấn phức tạp dễ đọc và xử lý các tình huống đệ quy, trong khi stored procedures cung cấp các giải pháp mạnh mẽ, có thể tái sử dụng cho logic kinh doanh phức tạp. Hãy chọn lựa dựa trên yêu cầu cụ thể của bạn về hiệu suất, khả năng bảo trì và độ phức tạp.