Subquery vs CTE vs Temp Table

Subqueries

Use subqueries when:

• Uncomplicated filtering - Simple WHERE conditions without complex joins
• Single aggregation - Basic aggregations that don't need to be reused
• Single extra query needed - When you only need to reference the result once
• Some databases optimize subqueries better than CTEs - Performance varies by database engine

-- Subquery for simple filtering
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = true);

-- Subquery for simple aggregation
SELECT product_name, price,
  (SELECT AVG(price) FROM products) as avg_price
FROM products;

CTEs (Common Table Expressions)

Use CTEs when:

• Needed more than once - Reuse the same result multiple times in the query
• Complex join tree - Multiple joins with intermediate steps
• Recursive queries - CTE is the clear solution for recursive operations (hierarchical data, traversal)
• Materialization control - CTEs can enable/disable materialization per segment, which subqueries can't do

-- CTE for reuse
WITH sales_summary AS (
  SELECT region, product, SUM(amount) as total_sales
  FROM sales
  GROUP BY region, product
)
SELECT * FROM sales_summary WHERE total_sales > 1000
UNION ALL
SELECT * FROM sales_summary WHERE region = 'North';

-- Recursive CTE for hierarchical data
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 as level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

Comparison: Subquery vs CTE vs Temp Table vs CTAS vs View

Category	Subquery	CTE	Temp Table	CTAS	View
Storage	Memory	Memory	Disk	Disk	No Storage*
Lifetime	Temporary	Temporary	Temporary	Permanent	Permanent
When Deleted	End of Query	End of Query	End of Session	DDL-DROP	DDL-DROP
Scope	Single use	Multi use	Multi use	Multi use	Multi use
Re-usability	1 Place - 1 Query	Multiple Places - 1 Query	Multiple queries - same Session	Multiple queries	Multiple queries
Remains Up to Date	Yes	Yes	No	No	Yes

*Technically speaking, the query plan is stored on the disk, but the data itself is dynamically obtained upon execution of the query.

When to Use Each

Use Subquery When:

• Simple filtering or aggregation
• Only needed once in the query
• Database optimizes subqueries well

Use CTE When:

• Need to reference the same data multiple times
• Complex join logic that benefits from named intermediate steps
• Recursive operations (hierarchical data)
• Want control over materialization

Use Temporary Table When:

• Need to persist data across multiple queries in a session
• Complex transformations that benefit from being materializes
• Breaking down very complex queries for debugging
• Multiple users/sessions need separate copies of data

Use CTAS (Create Table As Select) When:

• Need permanent storage of the result
• Want to create a new table from query results
• Need to index the result for faster queries

Use View When:

• Need to expose a query as a virtual table
• Want to encapsulate complex logic
• Data should always reflect current table state

💡 Key Takeaway: Start with a subquery for simplicity. Upgrade to a CTE if you need reuse or complexity. Use a temp table for session persistence or when you have multiple queries referencing the same intermediate result.