DEV Community

Anaya Manwa
Anaya Manwa

Posted on

Differences between a subquery, a CTE, and a stored procedure.

Subquery vs CTE vs Stored Procedure: Technical Breakdown

When you’re dealing with SQL, you’ve got three primary tools for organizing your code: subqueries, CTEs (Common Table Expressions), and stored procedures. Each has a distinct role and technical characteristics you need to know.

1. Subquery

A subquery is basically a query embedded inside another SQL statement. You wrap it in parentheses and slot it into SELECT, FROM, or WHERE clauses. It’s mostly used for situations where you need a temporary result—think of it as an inline helper.

Technical specifics:

  • Scope: Only exists within the main query.
  • Types: Correlated (runs per row in the outer query) or uncorrelated (runs independently).
  • Use case: Quick, one-off data retrieval—don’t lean on these for heavy lifting, performance can tank if overused.
  • Limitation: Not reusable outside the statement.

Example:

SELECT name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 1000
);
Enter fullscreen mode Exit fullscreen mode

Here, the subquery pulls qualifying customer IDs. The main query fetches names based on those IDs.

2. Common Table Expression (CTE)

CTEs let you define a named temporary result set using the WITH clause. This is ideal for breaking up complex queries into logical parts, and it supports recursion—something subqueries just don’t do.

Technical specifics:

  • Scope: Only during the execution of the main query.
  • Reusability: Can reference the CTE multiple times within the same query.
  • Supports: Recursion (for hierarchical/iterative problems).
  • Main benefit: Improved readability and maintainability.

Example:

WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_totals
WHERE total_spent > 1000;
Enter fullscreen mode Exit fullscreen mode

The customer_totals CTE aggregates spending, which the main query then filters.

3. Stored Procedure

Stored procedures are pre-compiled routines stored in the database. They can take parameters, run multiple SQL statements, and handle complex logic like loops and conditionals. These are essential for automation, encapsulation, and reusability.

Technical specifics:

  • Location: Permanently stored in the database schema.
  • Accepts: Input/output parameters.
  • Functionality: Can include multiple queries, business logic, and control flow.
  • Performance: Precompiled—reduces repeated parsing overhead.
  • Use case: Routine batch jobs, automation, and complex operations.

Example (PostgreSQL):

CREATE OR REPLACE PROCEDURE get_high_value_customers(min_amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT name, total_amount
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    WHERE total_amount > min_amount;
END;
$$;
Enter fullscreen mode Exit fullscreen mode

You invoke this procedure with parameters—no need to rewrite logic every time.

Technical Comparison Table

Aspect Subquery CTE Stored Procedure
Scope Within single query Within single query Persistently in DB, reusable
Purpose Inline data retrieval Organize/structure complex queries Automation, encapsulation
Reusability None Query-level only Yes, across sessions
Handles Recursion No Yes Yes (with control flow)
Execution With main query With main query Explicitly called

Technical Summary

  • Subqueries: Good for quick, inline data pulls—avoid them for large-scale logic or you’ll hit performance issues.
  • CTEs: Use them to simplify and modularize complex SQL, especially when recursion or multiple query references are required.
  • Stored Procedures: Ideal for encapsulating business logic, automating tasks, and improving performance due to their persistent, precompiled nature.

Pick the right approach based on your scenario—scope, complexity, and the need for reusability are key factors.

Top comments (0)