DEV Community

Joseph
Joseph

Posted on

What is the difference between subqueries, common table expressions (CTEs), and stored procedures?

Subqueries, Common Table Expressions (CTEs), and stored procedures are SQL constructs used to manage and manipulate data in relational databases, but they serve different purposes and have distinct characteristics. Below, I’ll explain each, highlight their differences, and provide examples using SQL syntax.

1. Subquery

Definition: A subquery is a query nested within another query (often called the outer query). It’s used to return data that the outer query processes. Subqueries are typically enclosed in parentheses and can appear in clauses like SELECT, WHERE, or FROM.

Use Case:
When you need a temporary result set for filtering, calculations, or comparisons within a single query.

Characteristics:

  • Executes first, and its result is used by the outer query.

  • Can be correlated (depends on the outer query) or non-correlated (independent).

  • Single-use, not reusable across queries.

  • May impact performance for complex queries due to repeated execution.

Example:
Suppose you have a Employees table and want to find employees with a salary above the average salary.

SELECT EmployeeID, FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode

The subquery (SELECT AVG(Salary) FROM Employees) calculates the average salary, and the outer query filters employees whose salary exceeds it.

2. Common Table Expression (CTE)

Definition: A CTE is a temporary result set defined within a single query using the WITH clause. It’s named and can be referenced multiple times in the main query.

Use Case:
When you need a readable, reusable intermediate result within a query, especially for complex or recursive queries.

Characteristics:

  • Improves query readability and maintainability compared to subqueries.
  • Can be referenced multiple times in the same query.
  • Supports recursion for hierarchical data (e.g., organizational charts).
  • Temporary and exists only for the duration of the query.

Example:
Using the same Employees table, calculate the average salary and use it to filter employees.

WITH AvgSalary AS (
    SELECT AVG(Salary) AS AvgSal
    FROM Employees
)
SELECT EmployeeID, FirstName, Salary
FROM Employees, AvgSalary
WHERE Salary > AvgSalary.AvgSal;
Enter fullscreen mode Exit fullscreen mode

The CTE AvgSalary computes the average salary, and the main query references it. This is more readable than a subquery.

3. Stored Procedure

Definition: A stored procedure is a precompiled set of SQL statements stored in the database with a name, which can be executed on demand. It can accept parameters and include logic like loops or conditionals.

Use Case:
For reusable, complex operations that need to be executed repeatedly, such as data processing, reporting, or business logic.

Characteristics:

  • Stored in the database and reusable across multiple sessions or applications.
  • Can include procedural logic (e.g., IF, WHILE) and error handling.
  • Can accept input/output parameters for dynamic behavior.
  • Improves performance (precompiled) and security (controlled access).

Example:
Create a stored procedure to update an employee’s salary based on their EmployeeID.

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END;

-- Execute the stored procedure
EXEC UpdateEmployeeSalary @EmployeeID = 101, @NewSalary = 75000.00;
Enter fullscreen mode Exit fullscreen mode

The procedure UpdateEmployeeSalary takes two parameters and updates the Employees table. It can be reused anytime by calling EXEC.

Top comments (0)