This lesson on CTE vs Subquery vs Temp Table — When to Use Each is hands-on and example-driven. You will learn how to choose between Common Table Expressions (CTEs), subqueries, and temporary tables based on readability, reuse, and performance. You will be able to refactor complex nested subqueries into modular CTE chains, reference CTEs multiple times, and build recursive CTEs to generate sequential data like missing dates.
What You'll Be Able To Do
- Refactor deeply nested subqueries into readable Common Table Expressions using the WITH clause.
- Reference a single CTE multiple times in the main query without duplicating logic.
- Define and join multiple comma-separated CTEs within a single query structure.
- Evaluate performance and permission trade-offs to choose between CTEs and temporary tables.
- Construct a recursive CTE with an anchor and recursive member to generate sequential records.
Detailed Concept Walkthrough
1. Refactoring Subqueries Into CTEs
Subqueries embed intermediate queries directly inside outer statements, whereas CTEs define named temporary result sets upfront using the WITH keyword.
- Syntax Rule: A CTE begins with the WITH keyword, followed by the CTE name, the AS keyword, and the query enclosed in parentheses.
- Execution Flow: The database engine processes the CTE block before executing the main outer query that selects from it.
- Best Practice: Write initial query logic as simple subqueries when drafting, then refactor them into CTEs to enhance readability and maintainability.
-- Basic CTE refactoring an inline subquery
WITH mp AS (
SELECT category, MAX(price) AS max_price
FROM menu_items
GROUP BY category
)
SELECT COUNT(*)
FROM mp
WHERE max_price < 15;
Key Takeaway: CTEs move intermediate transformation logic to the top of the file, transforming nested code into readable sequential steps.
2. Multiple References and Multiple CTEs
A single WITH block can define multiple distinct CTEs separated by commas, and any defined CTE can be queried multiple times in downstream code.
- Syntax Rule: Use the WITH keyword only once at the beginning, separating subsequent CTE declarations with a comma.
- Mechanism: Downstream queries and subsequent CTEs can reference previously defined CTEs just like base tables.
- Under the Hood: Referencing a CTE multiple times avoids duplicating complex subquery code in both the SELECT/JOIN and WHERE clauses.
-- Defining multiple CTEs and joining them
WITH mp AS (
SELECT category, MAX(price) AS max_price
FROM menu_items
GROUP BY category
),
ci AS (
SELECT *
FROM menu_items
WHERE item_name LIKE '%chicken%'
)
SELECT ci.item_name, ci.price, mp.max_price
FROM ci
JOIN mp ON ci.category = mp.category;
Key Takeaway: Chain multiple CTEs using commas to build clean pipelines and eliminate duplicated subquery blocks.
3. CTEs Versus Temporary Tables
CTEs exist solely for the duration of a single query execution, whereas temporary tables physically store intermediate results across an entire session.
- Under the Hood: CTEs are ephemeral and re-evaluated per query, whereas temporary tables write data to temporary storage for repeated access across queries.
- Best Practice: Use CTEs for single-query data organization; switch to temporary tables when processing heavy datasets reused across multiple separate statements.
- Permissions Nuance: Temporary tables require CREATE permissions in the database; when permissions are restricted, rely on CTEs or request database views.
-- Creating a temporary table to store heavy intermediate results
CREATE TEMPORARY TABLE temp_max_prices AS
SELECT category, MAX(price) AS max_price
FROM menu_items
GROUP BY category;
-- Query the temporary table multiple times across the session
SELECT * FROM temp_max_prices WHERE max_price < 15;
Key Takeaway: Default to CTEs for clarity, but materialize into temporary tables when reusing expensive computations across separate queries.
4. Recursive CTEs for Iterative Data
A recursive CTE references itself iteratively to generate sequences or traverse hierarchical data until a termination condition is met.
- Mechanism: Combines an initial base anchor query with an iterative recursive query using the UNION ALL operator.
- Execution Flow: Each recursion step operates only on the rows generated by the immediately preceding iteration until the WHERE filter stops it.
- Best Practice: Use recursive CTEs to generate continuous date series, then LEFT JOIN sparse tables to fill missing intervals using LAG or COALESCE.
-- Generate date sequence to fill missing stock price dates
WITH RECURSIVE date_series AS (
SELECT CAST('2023-06-01' AS DATE) AS dt -- Anchor member
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY) -- Recursive member
FROM date_series
WHERE dt < '2023-06-06'
)
SELECT d.dt,
COALESCE(s.price, LAG(s.price) OVER (ORDER BY d.dt)) AS price
FROM date_series d
LEFT JOIN stock_prices s ON d.dt = s.date;
Key Takeaway: Recursive CTEs iteratively generate sequence rows, making them ideal for date scaffolding and hierarchical graph traversals.
Topics Covered in CTE vs Subquery vs Temp Table — When to Use Each
- Subquery to CTE Refactoring (0:00 - 1:15) — The instructor solves a menu pricing problem using a subquery and refactors it into a basic CTE.
- When to Use Subqueries vs CTEs (1:15 - 2:30) — A comparison highlights MySQL version support, code readability, and workflow strategies.
- Multiple CTE References and Tables (2:30 - 3:55) — Demonstrations show referencing one CTE twice in a query and joining multiple comma-separated CTEs.
- CTEs vs Temporary Tables (3:55 - 5:10) — The instructor evaluates execution frequency, dataset size, and user permissions between CTEs and temp tables.
- Recursive CTEs and Missing Dates (5:10 - 6:45) — A recursive CTE generates a sequential date series that is merged with stock prices to backfill nulls.
- Summary and Practical Rules (6:45 - 7:35) — A final recap summarizes decision rules for choosing between subqueries, CTEs, temp tables, and recursive CTEs.
SQL Cheat Sheet
-
WITH cte_name AS (...)— Defines a single Common Table Expression upfrontWITH mp AS (SELECT category, MAX(price) AS max_p FROM menu_items GROUP BY category) SELECT * FROM mp; -
WITH cte1 AS (...), cte2 AS (...)— Defines multiple chained CTEs separated by commasWITH t1 AS (SELECT * FROM menu_items), t2 AS (SELECT * FROM stock_prices) SELECT * FROM t1, t2; -
WITH RECURSIVE cte AS (anchor UNION ALL recursive)— Iteratively generates rows until termination condition metWITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 5) SELECT * FROM seq; -
CREATE TEMPORARY TABLE table_name AS (...)— Persists query results for the current sessionCREATE TEMPORARY TABLE temp_menu AS SELECT * FROM menu_items WHERE price < 15; -
COALESCE(val1, val2)— Returns first non-null expression from argument listSELECT COALESCE(price, 0) FROM stock_prices; -
LAG(col) OVER (ORDER BY col_order)— Returns value from the preceding rowSELECT LAG(price) OVER (ORDER BY dt) FROM stock_prices;
Comparison Table
| Aspect | Subquery | CTE | Temporary Table |
|---|---|---|---|
| Lifetime Scope | Single query statement | Single query statement | Entire database session |
| Readability | Low; deeply nested | High; top-down layout | Moderate; multi-statement |
| Multiple Reuse | No; requires duplicating code | Yes; within single query | Yes; across multiple queries |
| Write Permissions | Read-only access required | Read-only access required | Requires CREATE TEMP rights |
| Best Use Case | Quick one-off nested filters | Complex multi-step pipeline logic | Heavy computations reused often |
Common Pitfalls
- Mistake: Repeating the WITH keyword before every CTE. Avoid: Use WITH once at the beginning and separate subsequent CTE definitions with commas.
- Mistake: Attempting to query a CTE from a subsequent standalone query. Avoid: Use a temporary table or database view to persist intermediate data across queries.
- Mistake: Omitting a termination filter inside a recursive CTE member. Avoid: Include an explicit WHERE condition in the recursive step to prevent infinite loops.
- Mistake: Duplicating identical subqueries in the SELECT and WHERE clauses. Avoid: Define a single CTE upfront and reference it multiple times downstream.
FAQs
- When is a subquery preferred over a CTE? Subqueries work well for simple, one-line nested filtering where a CTE adds boilerplate, or when writing code in legacy databases lacking CTE support.
- How can I persist CTE results if I lack permissions to create temporary tables? You must use CTEs within individual queries or coordinate with data engineers to materialize the logic into permanent views.
- Can a later CTE in the same WITH block reference an earlier CTE? Yes, any CTE declared in a comma-separated list can query the results of CTEs defined before it.
- How does the recursive query know when to terminate? The WHERE clause in the recursive query acts as the exit condition, ending recursion when zero rows are returned.