SQL Query techniques and their differences ie Subqueries, CTEs and stored procedures.

Overview

SQL offers multiple approaches for complex data operations. Understanding the differences between subqueries, Common Table Expressions (CTEs), and stored procedures is crucial for writing efficient and maintainable database code.

Subqueries

Definition

A subquery is a query nested inside another SQL statement. It executes first and passes its result to the outer query.

Characteristics

• Execution: Runs once or multiple times depending on context
• Scope: Limited to the statement where it's defined
• Reusability: Cannot be reused across different queries
• Performance: Can be less efficient for complex operations

Example

-- Find employees earning more than average salary
SELECT name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery
SELECT e1.name, e1.department
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary) 
    FROM employees e2 
    WHERE e2.department = e1.department
);

Best Use Cases

• Simple filtering conditions
• One-time calculations
• EXISTS/NOT EXISTS operations

---

Common Table Expressions (CTEs)

Definition

CTEs are temporary named result sets that exist only during query execution. They improve readability and can be referenced multiple times within a single statement.

Characteristics

• Readability: Makes complex queries more readable
• Reusability: Can be referenced multiple times in the same query
• Recursion: Supports recursive operations
• Performance: Similar to subqueries but more maintainable

Example

-- Basic CTE
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;

-- Recursive CTE for hierarchical data
WITH RECURSIVE employee_hierarchy AS (
    -- Base case
    SELECT employee_id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    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;

Best Use Cases

• Complex queries requiring multiple references to the same result set
• Recursive operations (organizational charts, bill of materials)
• Improving query readability
• Window function operations

---

Stored Procedures

Definition

Stored procedures are precompiled SQL code blocks stored in the database that can accept parameters and return results.

Characteristics

• Persistence: Stored permanently in the database
• Performance: Precompiled and cached for faster execution
• Reusability: Can be called from multiple applications
• Security: Provide controlled access to data
• Logic: Can contain complex business logic with control structures

Example

-- Creating a 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;

-- Calling the stored procedure
EXEC GetEmployeesByDepartment @DepartmentName = 'Engineering', @MinSalary = 60000;

Best Use Cases

• Frequently executed operations
• Complex business logic requiring multiple steps
• Data validation and transformation
• Batch processing operations
• Enforcing business rules at the database level

---

Key Differences Summary

Aspect	Subqueries	CTEs	Stored Procedures
Scope	Single statement	Single statement	Database-wide
Reusability	No	Within same query	Across applications
Performance	Variable	Similar to subqueries	Optimized (precompiled)
Complexity	Limited	Moderate	High
Maintenance	Inline only	Inline only	Centralized
Parameters	No	No	Yes
Business Logic	No	Limited	Yes

---

When to Choose What

Use Subqueries When:

• You need a simple, one-time filtering condition
• The logic is straightforward and won't be reused
• Working with small datasets

Use CTEs When:

• You need to reference the same result set multiple times
• Working with recursive data structures
• You want to improve query readability
• Breaking down complex queries into logical steps

Use Stored Procedures When:

• The operation will be called frequently
• You need to implement complex business logic
• Multiple applications need the same functionality
• You want centralized control over data access
• Performance optimization is critical

---

Things to remember:

Each approach serves different purposes in database development. Subqueries are great for simple operations, CTEs excel at making complex queries readable and handling recursive scenarios, while stored procedures provide robust, reusable solutions for complex business logic. Choose based on your specific requirements for performance, maintainability, and complexity.