Subqueries vs. CTEs vs. Stored Procedures in SQL — Explained Simply 🚀
Published: 2025-09-08
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: Exists only while the main query runs. Can be written inside WHERE,FROM, or SELECT clauses. 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: Exists only during a single query execution. Improves readability and avoids deeply nested queries. Can be referenced multiple times in the same query. 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: Stored permanently in the database. Can accept parameters (input/output). Can include complex logic: loops, conditions, error handling. 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.
Source: https://dev.to/mercy_musyoka_/subqueries-vs-ctes-vs-stored-procedures-in-sql-explained-simply-1phe