DEV Community

Dilan Bosire
Dilan Bosire

Posted on

Understanding the Difference Between Subquery, CTE, and Stored Procedure

In SQL and database programming, developers have several tools for organizing and optimizing queries. Among these are subqueries, Common Table Expressions (CTEs), and stored procedures. While they can sometimes be used to achieve similar goals, each serves a different purpose and has unique strengths. Let’s break down the differences.

1. Subquery

A subquery is a query nested inside another query. It is often used to filter, aggregate, or transform data before the main query executes. Subqueries can appear in SELECT, FROM, WHERE, or HAVING clauses.

Example:

If you have a table called employees with name and salary columns.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Enter fullscreen mode Exit fullscreen mode

Here, the subquery calculates the average salary, and the outer query filters employees based on that value.

Key Characteristics:

  • Runs each time it is called in the outer query.
  • Can return a single value, multiple rows, or a table.
  • Great for quick inline logic, but overuse can harm performance.

2. Common Table Expression (CTE)

A Common Table Expression (CTE) is a temporary result set created with the WITH clause. It can be referenced within a query, enhances readability, and can be reused, helping to simplify complex queries.

Example:

If you have a table called orders and customers with a common column called customer_id, and you want to rank customers based on their total quantities

with customer_rankings as (
    select orders.customer_id,sum(orders.quantity)as total_quantity,
        row_number() over (order by sum(orders.quantity)desc) as rankings
    from orders
    group by orders.customer_id)
select customers.first_name,customers.second_name,customer_rankings.total_quantity ,customer_rankings.rankings
from customers join customer_rankings on customers.customer_id= customer_rankings.customer_id
order by customer_rankings.total_quantity desc;
Enter fullscreen mode Exit fullscreen mode

Key Characteristics:

  • Improves SQL readability and modularity.
  • Supports recursion for hierarchical data, such as organizational charts.
  • Exists only during query execution.
  • Cannot be reused across sessions without rewriting.

3. Stored Procedure

A stored procedure is a set of SQL statements, possibly with procedural logic, saved in the database for repeated use. Unlike subqueries or CTEs, it operates independently and can be called multiple times.

Example:

If you have a table called employees with employee_id, name, and department columns, and we want a stored procedure that shows all employees in the HR department.

CREATE PROCEDURE GetHREmployees
AS
BEGIN
    SELECT employee_id, name, department
    FROM employees
    WHERE department = 'HR';
END;
Enter fullscreen mode Exit fullscreen mode

Key Characteristics:

  • Stored and run on the database server.
  • Supports parameters, control-of-flow logic, error handling, and transactions.
  • Improves performance by minimizing network traffic—only the procedure call is sent.
  • Enhances security by controlling access with permissions.

Conclusion

  • Use subqueries for inline filtering or calculations in another query.
  • Use Common Table Expressions (CTEs) for better readability, recursive queries, and simplifying complex logic.
  • Use stored procedures for reusable, parameterized, and secure database logic.

These tools give SQL developers the flexibility to manage tasks from simple lookups to complex data processing.

Top comments (0)