Subqueries vs. CTEs vs. Stored Procedures in SQL — Explained Simply 🚀

SQL offers different tools to query and manage data effectively. Three common concepts that often confuse beginners are subqueries, CTEs (Common Table Expressions), and stored procedures. While they may look similar at first, they serve different purposes and have unique strengths.

In this article, we’ll break them down one by one with examples, then summarize their key differences.

1. Subquery (Nested Query)

A subquery is a query written inside another query. It’s commonly used to provide intermediate results for filtering, aggregation, or transformation.

Key Features:

1. Exists only while the main query runs.
2. Can be written inside WHERE,FROM, or SELECT clauses.
3. Great for filtering based on calculated values. Example

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Here, the subquery(SELECT AVG(salary) …) calculates the average salary, and the outer query selects employees who earn above it.

2. CTE (Common Table Expression)

A CTE is a temporary, named result set created with the WITH keyword. Think of it as giving a subquery a name so you can reuse it and make your query more readable.

Key Features:

1. Exists only during a single query execution.
2. Improves readability and avoids deeply nested queries.
3. Can be referenced multiple times in the same query.
4. Supports recursion (useful for hierarchical data like org charts). Example:

WITH HighEarners AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 5000
)
SELECT * FROM HighEarners;

Here, HighEarners acts like a temporary table that can be reused in the query.

3. Stored Procedure

A stored procedure is a saved, compiled block of SQL code stored permanently in the database. Unlike subqueries and CTEs, it isn’t tied to a single query but can be executed anytime.

Key Features:

1. Stored permanently in the database.
2. Can accept parameters (input/output).
3. Can include complex logic: loops, conditions, error handling.
4. Useful for reusability, automation, and encapsulating business logic. Example:

CREATE PROCEDURE GetHighEarners (@minSalary INT)
AS
BEGIN
    SELECT name, salary
    FROM employees
    WHERE salary > @minSalary;
END;

Run it with:

EXEC GetHighEarners @minSalary = 6000;

Quick Comparison

Feature	Subquery	CTE	Stored Procedure
Scope	Inside a single query	Within one query	Permanent in DB
Reusability	Not reusable	Reusable within the query	Reusable anytime
Storage	Not stored	Not stored	Stored in DB
Complexity	Simple logic	Moderate (supports recursion)	Full programming logic
Use Case	Filtering & aggregation	Readability & modularity	Business logic & automation

---

Conclusion

• Use subqueries for quick, inline calculations or filters.
• Use CTEs when you want better readability and the ability to reference results multiple times in a single query.
• Use stored procedures when you need reusable, parameterized, and complex database logic.
• By understanding when to use each, you’ll write cleaner SQL, improve performance, and build maintainable database solutions.