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.