Understanding the Differences Between Subqueries, CTEs, and Stored Procedures

This content originally appeared on DEV Community and was authored by Patrick Kinoti

Introduction

Subqueries, CTEs, and stored procedures are three powerful tools that shape how we write and optimize SQL. At first glance they may seem alike, but each serves a unique purpose, and understanding their distinctions can help you choose the right approach for your needs, i.e Subqueries help nest logic, CTEs improve clarity and organization, while stored procedures package logic for reuse and efficiency.

Subqueries

A subquery is a query nested inside another SQL query. It is enclosed in parentheses and provides a result that the main query can use—either as a value, a set of rows, or as part of a condition.

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

• Outer query: selects first_name and last_name from employees table (e).
• Condition: keeps only employees whose salary is greater than the result of the subquery.
• Subquery: calculates the average salary of employees in the same department (department_id = e.department_id).
• Correlation: the subquery depends on the outer query (e.department_id).

CTE (Common Table Expression)

A CTE is a temporary, named result set—like an inline view—that exists only during query execution, defined using the WITH clause. They enhance readability by breaking up complex SQL, eliminate repetitive subqueries, and support recursive data retrieval. Used for Structuring complex queries, handling hierarchical or recurring logic, and avoiding duplication

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;

CTE (ranking_customers)

• Groups orders by customer_id. -_ Calculates SUM(quantity) = total items ordered per customer._
• Assigns rank using ROW_NUMBER() (highest quantity = rank 1).

Main query

• Joins customers table with the CTE on customer_id.
• Selects customer’s first & last name, total quantity, and rank.
• Orders the result by customer_rank.

Stored Procedure

A stored procedure is a precompiled set of SQL statements stored on the database server, executed by calling its name. It can include logic, parameters, and multiple queries, offering reusability and performance benefits for repeated tasks, but it’s more complex to maintain.

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;

• Procedure name: GetCropYieldByCounty
• Input: crop_name (the crop to check)
• Action: Selects each county and calculates SUM(yield_tons) for that crop
• Groups results by county and sorts them in descending order of yield
• Example: CALL GetCropYieldByCounty(‘Maize’); → shows maize yields per county

Comparison of Subqueries, CTEs, and Stored Procedures

This content originally appeared on DEV Community and was authored by Patrick Kinoti