SQL CTE vs Subquery vs Temporary Tables: Differences, Examples, and When to Use Each

When transforming relational data for business intelligence reports, data pipelines, or technical interviews, managing intermediate data structures is inevitable. A simple raw SELECT query quickly becomes inadequate when you need to aggregate numbers first, filter against those aggregations second, and join the results back to parent dimensions third.

SQL developers have three primary tools for structuring intermediate data: Subqueries, Common Table Expressions (CTEs), and Temporary Tables.

While they can frequently achieve the same functional outcome, their lifecycle scope, memory/storage footprint, engine optimizer behavior, and developer readability differ drastically. Selecting the wrong approach can transform a clean query into unreadable nesting or introduce memory pressure and performance bottlenecks.

This guide provides an evidence-based, side-by-side technical comparison across PostgreSQL, MySQL, and Microsoft SQL Server, complete with a reproducible dataset, execution plan analysis, and a practical decision framework.

---

Direct Answer: CTE vs Subquery vs Temp Table Core Differences

The fundamental difference between CTEs, subqueries, and temporary tables centers on scope, persistence, and reusability:

1. Subqueries: Anonymous queries nested directly inside another statement (SELECT, FROM, or WHERE). They have the narrowest scope (the single clause containing them), execute "inside-out," and cannot be referenced elsewhere.
2. CTEs (`WITH` clause): Named, temporary result sets defined at the beginning of a statement. They exist strictly for the lifespan of that single SQL statement, read cleanly from "top-to-bottom," and support recursive queries.
3. Temporary Tables: Physical or memory-backed tables created inside tempdb or session memory. They persist across multiple separate queries throughout an entire database session, support dedicated indexes, and allow explicit cost statistics, but require disk/memory overhead and session cleanup.

---

Architectural Comparison Matrix

---

The Reproducible E-Commerce Dataset

To evaluate how each mechanism solves identical analytical questions, we use a classic corporate reporting dataset containing departments, employees, and sales deals:

-- ANSI SQL Compliant Setup Script
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept_id INT NOT NULL REFERENCES departments(dept_id),
    manager_id INT REFERENCES employees(emp_id),
    salary INT NOT NULL
);

CREATE TABLE deals (
    deal_id INT PRIMARY KEY,
    emp_id INT NOT NULL REFERENCES employees(emp_id),
    deal_amount INT NOT NULL,
    close_quarter VARCHAR(10) NOT NULL
);

-- Seed Data
INSERT INTO departments (dept_id, dept_name) VALUES
(1, 'Enterprise Sales'),
(2, 'Cloud Solutions');

INSERT INTO employees (emp_id, name, dept_id, manager_id, salary) VALUES
(101, 'Sarah Connor', 1, NULL, 140000),  -- Sales VP (Top of hierarchy)
(102, 'John Doe',     1, 101,  95000),   -- Account Exec (Reports to 101)
(103, 'Jane Miller',  1, 101, 110000),   -- Senior AE (Reports to 101)
(104, 'Alex Rivera',  2, NULL, 130000),  -- Cloud Director
(105, 'David Kim',    2, 104,  85000);   -- Solutions Architect

INSERT INTO deals (deal_id, emp_id, deal_amount, close_quarter) VALUES
(1, 102, 50000, '2026-Q1'),
(2, 102, 70000, '2026-Q1'),
(3, 103, 120000, '2026-Q1'),
(4, 103, 90000, '2026-Q1'),
(5, 105, 40000, '2026-Q1'),
(6, 105, 45000, '2026-Q1');

Our objective: Calculate total Q1 closed sales per employee, then find employees whose sales exceeded the average sales achieved within their respective department.

---

1. Solving with a Derived Table Subquery

In traditional SQL, we nest derived tables inside the FROM and JOIN clauses:

SELECT 
    d.dept_name,
    e.name AS employee_name,
    emp_totals.total_sales,
    ROUND(dept_averages.avg_dept_sales, 2) AS dept_average_sales
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN (
    -- Derived Table 1: Sales per employee
    SELECT emp_id, SUM(deal_amount) AS total_sales
    FROM deals
    WHERE close_quarter = '2026-Q1'
    GROUP BY emp_id
) emp_totals ON e.emp_id = emp_totals.emp_id
JOIN (
    -- Derived Table 2: Average sales per department
    SELECT 
        e_inner.dept_id,
        AVG(sub_totals.total_sales) AS avg_dept_sales
    FROM employees e_inner
    JOIN (
        SELECT emp_id, SUM(deal_amount) AS total_sales
        FROM deals
        WHERE close_quarter = '2026-Q1'
        GROUP BY emp_id
    ) sub_totals ON e_inner.emp_id = sub_totals.emp_id
    GROUP BY e_inner.dept_id
) dept_averages ON e.dept_id = dept_averages.dept_id
WHERE emp_totals.total_sales > dept_averages.avg_dept_sales
ORDER BY emp_totals.total_sales DESC;

The Readability Bottleneck

Notice the structural drawbacks:

• The subquery calculating SUM(deal_amount) had to be written twice because SQL subqueries cannot reference a sibling subquery in the same FROM clause.
• Reading this query requires jumping "inside-out": you must read the innermost derived tables first before understanding what the outer SELECT is joining.

---

2. Solving with a Common Table Expression (CTE)

Now let us restructure the identical business logic into a top-to-bottom pipeline using the WITH clause:

WITH employee_sales AS (
    -- Step 1: Compute closed sales per employee
    SELECT 
        emp_id, 
        SUM(deal_amount) AS total_sales
    FROM deals
    WHERE close_quarter = '2026-Q1'
    GROUP BY emp_id
),
department_averages AS (
    -- Step 2: Compute average sales per department reusing employee_sales
    SELECT 
        e.dept_id,
        AVG(es.total_sales) AS avg_dept_sales
    FROM employees e
    JOIN employee_sales es ON e.emp_id = es.emp_id
    GROUP BY e.dept_id
)
-- Step 3: Final presentation filtering employees beating department average
SELECT 
    d.dept_name,
    e.name AS employee_name,
    es.total_sales,
    ROUND(da.avg_dept_sales, 2) AS dept_average_sales
FROM employee_sales es
JOIN employees e ON es.emp_id = e.emp_id
JOIN departments d ON e.dept_id = d.dept_id
JOIN department_averages da ON e.dept_id = da.dept_id
WHERE es.total_sales > da.avg_dept_sales
ORDER BY es.total_sales DESC;

Result Output

Educational Analysis

• In Enterprise Sales, Jane Miller closed \$210,000 and John Doe closed \$120,000.
• The departmental average was \$165,000. Jane Miller is correctly isolated.
• Key Advantage: department_averages referenced employee_sales directly. There is zero duplicated SQL code, and any team member can read the logic sequentially from top to bottom.

---

3. Solving with Temporary Tables Across Multiple Statements

What if your task is not just a single reporting output, but an end-of-quarter analytics batch where you must:

1. Identify high-performing outliers.
2. Insert audit records into a bonus calculation table.
3. Generate an executive department summary report?

If you use a CTE, the entire calculation must re-run for every individual statement because CTEs dissolve the instant the semicolon is reached.

Here, a Temporary Table is the architecturally correct choice:

-- PostgreSQL Syntax (See engine section below for SQL Server & MySQL)
CREATE TEMP TABLE temp_q1_rep_sales AS
SELECT 
    e.emp_id,
    e.name AS employee_name,
    e.dept_id,
    d.dept_name,
    SUM(dl.deal_amount) AS total_sales
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
JOIN deals dl ON e.emp_id = dl.emp_id
WHERE dl.close_quarter = '2026-Q1'
GROUP BY e.emp_id, e.name, e.dept_id, d.dept_name;

-- Create an index to accelerate subsequent joins
CREATE INDEX idx_temp_rep_dept ON temp_q1_rep_sales(dept_id);

-- Statement 1: Executive Outlier Report
SELECT 
    t.dept_name,
    t.employee_name,
    t.total_sales
FROM temp_q1_rep_sales t
WHERE t.total_sales > (
    SELECT AVG(total_sales) 
    FROM temp_q1_rep_sales 
    WHERE dept_id = t.dept_id
);

-- Statement 2: Department Summary (reusing the exact same temp table)
SELECT 
    dept_name,
    COUNT(emp_id) AS total_active_reps,
    SUM(total_sales) AS aggregate_revenue,
    ROUND(AVG(total_sales), 2) AS average_rep_revenue
FROM temp_q1_rep_sales
GROUP BY dept_name;

-- Clean up session resources
DROP TABLE IF EXISTS temp_q1_rep_sales;

---

Advanced CTE Superpower: Recursive Queries

One capability unique to CTEs (unachievable with simple subqueries) is recursion. Recursive CTEs allow SQL to traverse hierarchical data, such as organizational charts, parent-child product categories, or graph networks.

Tracing Management Hierarchy Levels

Using our employees table where Sarah Connor (101) manages Jane Miller (103):

-- PostgreSQL & MySQL 8.0+ Recursive Syntax
WITH RECURSIVE org_hierarchy AS (
    -- Anchor Member: Top-level executives (manager_id IS NULL)
    SELECT 
        emp_id,
        name,
        manager_id,
        1 AS hierarchy_level,
        CAST(name AS VARCHAR(255)) AS reporting_chain
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Join employees to their managers
    SELECT 
        e.emp_id,
        e.name,
        e.manager_id,
        h.hierarchy_level + 1,
        CAST(h.reporting_chain || ' -> ' || e.name AS VARCHAR(255))
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.emp_id
)
SELECT hierarchy_level, name, reporting_chain
FROM org_hierarchy
ORDER BY hierarchy_level, emp_id;

Hierarchy Result Table

---

Engine-Specific Implementation Notes

Different database engines optimize and store these structures under distinct rules. Never assume identical performance across database engines.

1. PostgreSQL (Version 12+ Optimization Paradigm Shift)

• Pre-Postgres 12: CTEs acted as strict optimization fences. PostgreSQL always computed and materialized the CTE into memory or temporary workfiles before executing the outer query. This prevented the optimizer from pushing outer WHERE predicates into the CTE.
• PostgreSQL 12 and Newer: CTEs are inlined by default if they are referenced only once and have no side-effects (e.g., non-modifying statements). The engine merges them into derived tables for unified cost-based optimization.
• Manual Control: PostgreSQL allows developers to override optimizer heuristics explicitly:

`sql -- Force materialization (acts as optimization fence) WITH cte_data AS MATERIALIZED (SELECT ...)

-- Prevent materialization (force inlining) WITH cte_data AS NOT MATERIALIZED (SELECT ...) `

2. MySQL (Version 8.0+)

• Derived tables and CTEs are evaluated by the Derived Table Merge optimizer switch (derived_merge=on). When enabled, MySQL attempts to dissolve subqueries and CTEs into the outer query block.
• If a CTE contains window functions, GROUP BY, or DISTINCT, MySQL materializes it into an internal temporary table in memory (using the TempTable storage engine) or on disk.

3. Microsoft SQL Server

• SQL Server's query processor treats non-recursive CTEs as syntax macros (views scoped to a statement). It inlines the CTE definition into the query execution tree.
• Multiple CTE References Caution: If a query references the same CTE three times, SQL Server may evaluate the underlying CTE query three separate times, or introduce a Table Spool operator. If the CTE query is expensive, storing the result in a local temporary table (#temp) is often vastly superior.
• Temp Table Syntax:

`sql -- Local temp table (single hash): dropped when procedure/session ends CREATE TABLE #local_deals (emp_id INT, total INT);

-- Global temp table (double hash): accessible across all active sessions CREATE TABLE ##shared_deals (emp_id INT, total INT); `

---

Why Performance Cannot Be Inferred from Syntax Alone

A common misconception among intermediate developers is asking: *"Which is faster: a CTE or a subquery?"*

The technical reality: Modern query optimizers compile CTEs and subqueries into the exact same physical execution plan.

Consider this query plan comparison in PostgreSQL 16:

Query 1 (Subquery):
EXPLAIN ANALYZE
SELECT * FROM (SELECT emp_id, SUM(deal_amount) FROM deals GROUP BY emp_id) sub WHERE emp_id = 102;

Query 2 (CTE):
EXPLAIN ANALYZE
WITH cte AS (SELECT emp_id, SUM(deal_amount) FROM deals GROUP BY emp_id)
SELECT * FROM cte WHERE emp_id = 102;

In both instances, the PostgreSQL optimizer pushes the predicate emp_id = 102 down into the scan before grouping:

-> HashAggregate  (cost=1.12..1.13 rows=1 width=12)
   -> Bitmap Heap Scan on deals  (cost=1.02..1.11 rows=2 width=8)
      Recheck Cond: (emp_id = 102)

Both queries generate identical costs, identical scans, and identical execution times.

When Temporary Tables Actually Outperform CTEs

Temporary tables outperform CTEs under specific, predictable conditions:

1. Multi-Statement Reusability: If an expensive aggregation of 5 million rows is needed across 3 subsequent queries in a stored procedure, running a CTE 3 times incurs $3\times$ the CPU and I/O. A temporary table computes it once.
2. Indexing on Intermediate Results: You cannot create an index on a CTE. If your intermediate result needs to be joined to multiple tables on different foreign keys, creating targeted B-tree indexes on a temporary table can transform nested loop cartesian disasters into fast index seeks.
3. Cardinality Statistics: For deep, multi-table joins, the optimizer's mathematical estimate of intermediate row counts often drifts. Running ANALYZE temp_table; provides exact row counts and histograms to the planner, eliminating suboptimal join order choices.

---

Practical Decision Guide: When to Use Each

Use this streamlined flowchart when structuring your queries:

[Need to store intermediate data?]
   │
   ├─► Is the data needed across multiple distinct SQL queries?
   │      │
   │      ├─► YES: Use a TEMPORARY TABLE (Index if >10k rows)
   │      └─► NO: Continue...
   │
   ├─► Does the transformation require hierarchical/recursive traversal?
   │      │
   │      ├─► YES: Use a RECURSIVE CTE (WITH RECURSIVE)
   │      └─► NO: Continue...
   │
   ├─► Is it a simple, one-off scalar comparison (e.g. WHERE val > (...))?
   │      │
   │      ├─► YES: Use an INLINE SUBQUERY
   │      └─► NO: Continue...
   │
   └─► Default for all multi-step analytical queries:
          └─► Use a COMMON TABLE EXPRESSION (CTE) for readability and modularity.

---

4 Common Mistakes and Troubleshooting Tips

1. Statement Scope Expiration

Symptom: ERROR: relation "my_cte" does not exist. Cause: Attempting to query a CTE after the terminating semicolon:

WITH my_cte AS (SELECT 1 AS val)
SELECT * FROM my_cte;

-- ERROR: my_cte was destroyed at the previous semicolon
SELECT * FROM my_cte;

2. Missing Mandatory Derived Table Aliases

Symptom: ERROR: subquery in FROM must have an alias (PostgreSQL) or Every derived table must have its own alias (MySQL). Cause: Omitting the alias on a subquery:

-- INCORRECT: Missing alias
SELECT * FROM (SELECT emp_id, salary FROM employees);

-- CORRECT: Labeled with alias
SELECT * FROM (SELECT emp_id, salary FROM employees) AS active_emp;

3. Temp Table Name Collisions in Connection Pools

Symptom: ERROR: Table '#temp_sales' already exists. Cause: When using application connection poolers (such as PgBouncer or HikariCP), sessions are reused across HTTP requests. If a script fails to drop a temp table, subsequent queries on the same connection fail. Fix: Always prefix creation with conditional drop logic:

DROP TABLE IF EXISTS #temp_sales;
CREATE TABLE #temp_sales (...);

4. Accidental Materialization Overhead in Postgres < 12

Symptom: Query runs orders of magnitude slower when rewritten from a subquery to a CTE on legacy Postgres instances. Cause: The older optimizer materialized the CTE completely, preventing index scans. Upgrade to PostgreSQL 12+ or stick with subqueries for filtered lookups on legacy engines.

---

Technical Interview Questions & Answers

Question 1: Can a Common Table Expression be modified with INSERT, UPDATE, or DELETE statements?

Answer: Yes, in PostgreSQL. PostgreSQL supports data-modifying CTEs (WITH deleted_rows AS (DELETE FROM ... RETURNING *)). This allows developers to move rows atomically between tables in a single statement. In SQL Server and MySQL, CTEs can be the target of an UPDATE or DELETE, but CTE definitions themselves cannot execute modifying operations within the WITH block.

Question 2: Why can't we add an index to a Common Table Expression?

Answer: CTEs are not persistent schema objects; they are logical syntax definitions evaluated dynamically within query compilation. The database engine does not create a durable system catalog entry or table allocation for a CTE, meaning there is no physical structure on disk upon which an index tree can be maintained. If an intermediate result requires an index, it must be written to a temporary or physical staging table.

Question 3: How does the QUALIFY clause relate to CTEs and Subqueries?

Answer: In databases that support it (Snowflake, BigQuery, Databricks), the QUALIFY clause filters the results of window functions directly within a single SELECT block, completely eliminating the need to wrap the query in an outer CTE or derived table subquery just to write WHERE rank <= 3.

---

Hands-On Practice Exercises

Exercise 1: Multi-Step Sales Ratio (CTE Challenge)

Using the deals table, write a single query using CTEs that displays each deal's deal_id, emp_id, deal_amount, and the percentage that this individual deal represents out of all deals closed in 2026-Q1.

-- Solution:
WITH q1_summary AS (
    SELECT SUM(deal_amount) AS grand_total
    FROM deals
    WHERE close_quarter = '2026-Q1'
)
SELECT 
    d.deal_id,
    d.emp_id,
    d.deal_amount,
    ROUND((d.deal_amount::NUMERIC / s.grand_total) * 100, 2) AS percentage_of_quarter
FROM deals d
CROSS JOIN q1_summary s
WHERE d.close_quarter = '2026-Q1'
ORDER BY d.deal_amount DESC;

Exercise 2: Top Sales Rep Per Department (Subquery vs Window Function)

Find the top-earning deal for each department.

-- Solution using CTE with DENSE_RANK:
WITH ranked_deals AS (
    SELECT 
        e.dept_id,
        e.name AS rep_name,
        d.deal_amount,
        DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY d.deal_amount DESC) AS rnk
    FROM deals d
    JOIN employees e ON d.emp_id = e.emp_id
)
SELECT dept_id, rep_name, deal_amount
FROM ranked_deals
WHERE rnk = 1;

---

Next Steps: Practice in Pathubs Interactive Workspaces

Solidify your database and analytical engineering skills with Pathubs' dedicated interactive labs:

1. [SQL CTE Interactive Lab](/resource/sql-cte): Practice transforming nested subqueries into modular WITH pipelines using our live in-browser SQL runner.
2. [SQL Subqueries Deep Dive](/resource/sql-subqueries): Master scalar, IN, EXISTS, and correlated query evaluation mechanics.
3. [SQL Running Totals & Window Functions](/resource/sql-running-totals): Explore cumulative metrics, frame boundaries, and partitioned analytics.
4. [SQL RANK vs DENSE_RANK vs ROW_NUMBER Guide](/blog/sql-rank-vs-dense-rank-vs-row-number): Read our companion guide on deterministic tie-breaking and Top-N query patterns.
5. [Data Analytics Career Roadmap](/roadmap/data-analytics): Follow our complete, structured curriculum from relational SQL foundations to dimensional modeling and executive BI reporting.