Giới thiệu

Subqueries, CTE (Common Table Expression) và Stored Procedures là ba công cụ mạnh mẽ trong SQL, giúp tối ưu hóa và viết truy vấn hiệu quả. Mặc dù có vẻ giống nhau ở cái nhìn đầu tiên, nhưng mỗi công cụ lại phục vụ một mục đích riêng biệt. Việc hiểu rõ sự khác biệt giữa chúng sẽ giúp bạn lựa chọn phương pháp phù hợp với nhu cầu của mình. Để dễ hình dung, Subqueries giúp lồng ghép logic, CTE cải thiện sự rõ ràng và tổ chức, trong khi Stored Procedures đóng gói logic để tái sử dụng và cải thiện hiệu suất.

Subqueries

Subquery là một truy vấn lồng trong một truy vấn SQL khác. Nó được đặt trong dấu ngoặc đơn và cung cấp kết quả mà truy vấn chính có thể sử dụng—dưới dạng giá trị, tập hợp các hàng, hoặc như một phần của điều kiện.

SELECT first_name, last_name
FROM employees e
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = e.department_id)

Phân tích Subquery

• Truy vấn ngoài: Chọn first_name và last_name từ bảng employees (e).
• Điều kiện: Giữ lại chỉ những nhân viên có mức lương cao hơn kết quả của subquery.
• Subquery: Tính toán mức lương trung bình của nhân viên trong cùng một phòng ban (department_id = e.department_id).
• Liên kết: Subquery phụ thuộc vào truy vấn ngoài (e.department_id).

CTE (Common Table Expression)

CTE là một tập hợp kết quả tạm thời có tên—giống như một cái nhìn nội tuyến—chỉ tồn tại trong quá trình thực thi truy vấn, được định nghĩa bằng cách sử dụng từ khóa WITH. CTE làm tăng tính đọc hiểu bằng cách chia nhỏ các truy vấn SQL phức tạp, loại bỏ các subqueries lặp lại và hỗ trợ truy xuất dữ liệu đệ quy. Chúng thường được sử dụng để cấu trúc các truy vấn phức tạp, xử lý logic phân cấp hoặc lặp lại, và tránh trùng lặp.

WITH ranking_customers AS (
    SELECT orders.customer_id, SUM(orders.quantity) AS total_quantity,
    ROW_NUMBER() OVER (ORDER BY SUM(orders.quantity) DESC) AS customer_rank
    FROM orders 
    GROUP BY orders.customer_id)
SELECT customers.first_name, customers.last_name, ranking_customers.total_quantity, ranking_customers.customer_rank
FROM customers 
JOIN ranking_customers ON customers.customer_id = ranking_customers.customer_id   
ORDER BY ranking_customers.customer_rank;

Phân tích CTE

CTE (ranking_customers)

• Nhóm các đơn hàng theo customer_id.
• Tính tổng số lượng (SUM(quantity)) = tổng số mặt hàng đã đặt hàng của mỗi khách hàng.
• Gán xếp hạng bằng ROW_NUMBER() (số lượng cao nhất = hạng 1).

Truy vấn chính

• Kết nối bảng customers với CTE dựa trên customer_id.
• Chọn tên và họ của khách hàng, tổng số lượng và hạng.
• Sắp xếp kết quả theo customer_rank.

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, được lưu trữ trên máy chủ cơ sở dữ liệu và được thực thi bằng cách gọi tên của nó. Nó có thể bao gồm logic, tham số, và nhiều truy vấn, mang lại lợi ích về khả năng tái sử dụng và hiệu suất cho các tác vụ lặp lại, nhưng phức tạp hơn để bảo trì.

CREATE PROCEDURE GetCropYieldByCounty(IN crop_name VARCHAR(50))
BEGIN
    SELECT county, SUM(yield_tons) AS total_yield
    FROM crops
    WHERE crop = crop_name
    GROUP BY county
    ORDER BY total_yield DESC;
END;

Phân tích Stored Procedure

• Tên thủ tục: GetCropYieldByCounty
• Tham số đầu vào: crop_name (cây trồng cần kiểm tra)
• Hành động: Chọn từng quận và tính tổng SUM(yield_tons) cho cây trồng đó.
• Nhóm kết quả theo quận và sắp xếp theo tổng sản lượng giảm dần.
• Ví dụ: CALL GetCropYieldByCounty('Ngô'); → Hiển thị sản lượng ngô theo từng quận.

So sánh giữa Subqueries, CTE và Stored Procedures

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

• Chọn công cụ phù hợp: Xác định rõ ràng mục đích sử dụng trước khi chọn giữa Subqueries, CTE và Stored Procedures.
• Tối ưu hóa hiệu suất: Sử dụng Stored Procedures cho các tác vụ lặp lại để giảm tải cho cơ sở dữ liệu.
• Tránh lồng nhau quá nhiều: Khi sử dụng Subqueries, hãy cẩn thận không lồng nhau quá nhiều để tránh làm giảm hiệu suất truy vấn.

Cạm bẫy thường gặp

• Quá phức tạp: CTE có thể trở nên quá phức tạp nếu không được sử dụng đúng cách, dẫn đến khó khăn trong việc bảo trì.
• Hiệu suất: Subqueries có thể làm chậm hiệu suất nếu không được tối ưu hóa.

Mẹo tối ưu hóa hiệu suất

• Sử dụng chỉ mục: Đảm bảo rằng các trường được sử dụng trong điều kiện JOIN hoặc WHERE được chỉ mục để tăng tốc độ truy vấn.
• Giảm thiểu dữ liệu: Chỉ lấy những cột cần thiết để giảm tải cho cơ sở dữ liệu.

Giải quyết sự cố

• Lỗi cú pháp: Kiểm tra kỹ lưỡng các câu lệnh SQL để tránh lỗi cú pháp.
• Kết quả không như mong muốn: Sử dụng EXPLAIN để phân tích cách mà SQL thực thi các truy vấn và điều chỉnh cho phù hợp.

Kết luận

Hiểu rõ sự khác biệt giữa Subqueries, CTE và Stored Procedures là rất quan trọng đối với bất kỳ lập trình viên SQL nào. Mỗi công cụ đều có ưu điểm và nhược điểm riêng, và việc chọn công cụ nào phụ thuộc vào ngữ cảnh và yêu cầu cụ thể của dự án. Hãy thử nghiệm với từng công cụ để tìm ra cách tối ưu nhất cho các tác vụ của bạn. Nếu bạn cần thêm thông tin hoặc có thắc mắc, hãy để lại câu hỏi của bạn dưới đây!