CTE referencing another CTE in SQL - Deep Dive

Overview - CTE referencing another CTE

What is it?

A Common Table Expression (CTE) is a temporary named result set in SQL that you can reference within a query. When one CTE refers to another CTE, it means you build multiple temporary tables step-by-step, each using the previous one. This helps organize complex queries by breaking them into smaller, readable parts. It is like creating mini-tables inside your query to use later.

Why it matters

Without CTEs referencing each other, writing complex queries would be harder and messier, often requiring nested subqueries that are difficult to read and maintain. This concept allows you to build queries in clear stages, making debugging and understanding easier. It also improves collaboration because others can follow your logic more easily.

Where it fits

Before learning this, you should understand basic SQL SELECT statements and simple CTEs. After mastering CTE referencing, you can learn recursive CTEs, window functions, and query optimization techniques.

Mental Model

Core Idea

CTEs referencing other CTEs let you build layered temporary tables inside a query, each building on the previous one to simplify complex data retrieval.

Think of it like...

It's like cooking a layered cake where each layer is prepared separately and stacked on top of the previous one to create the final dessert.

WITH cte1 AS (SELECT ...),
     cte2 AS (SELECT ... FROM cte1),
SELECT * FROM cte2;

┌─────────┐     ┌─────────┐     ┌─────────┐
│  cte1   │ --> │  cte2   │ --> │ Final   │
│ (base)  │     │ (built  │     │ result  │
└─────────┘     │ on cte1)│     └─────────┘
                └─────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Basic CTEs

Concept: Learn what a CTE is and how to write a simple one.

A CTE starts with WITH keyword followed by a name and a query in parentheses. It acts like a temporary table for the main query. Example: WITH cte AS ( SELECT id, name FROM users ) SELECT * FROM cte;

Result

Returns all users with their id and name from the temporary table named cte.

Understanding that CTEs create temporary named result sets helps you organize queries better than using nested subqueries.

2

Foundation

Referencing a Single CTE in Main Query

Concept: Use the CTE name in the main SELECT to get results.

After defining a CTE, you can use its name as if it were a table in your main query. Example: WITH cte AS ( SELECT id, age FROM users WHERE age > 18 ) SELECT id FROM cte WHERE age < 30;

Result

Returns ids of users older than 18 but younger than 30.

Knowing that CTEs can be filtered or joined in the main query makes them flexible building blocks.

3

Intermediate

Creating Multiple CTEs Sequentially

Concept: Define more than one CTE separated by commas to build stepwise results.

You can write multiple CTEs in one WITH clause, separated by commas. Example: WITH cte1 AS ( SELECT id, salary FROM employees ), cte2 AS ( SELECT id FROM cte1 WHERE salary > 50000 ) SELECT * FROM cte2;

Result

Returns ids of employees earning more than 50000.

Building multiple CTEs lets you break complex logic into smaller, manageable parts.

4

Intermediate

Referencing One CTE Inside Another

🤔Before reading on: Do you think a CTE can use another CTE defined before it in the same WITH clause? Commit to yes or no.

Concept: A CTE can refer to a previously defined CTE in the same WITH clause, enabling layered queries.

In the WITH clause, each CTE can use the ones defined before it. Example: WITH cte1 AS ( SELECT id, department FROM employees ), cte2 AS ( SELECT department, COUNT(*) AS count FROM cte1 GROUP BY department ) SELECT * FROM cte2;

Result

Returns each department with the number of employees in it.

Understanding that CTEs can build on each other allows stepwise refinement of data, improving clarity and reusability.

5

Intermediate

Using Multiple CTEs in Final Query

Concept: The final SELECT can use any of the defined CTEs or join them together.

You can join or select from multiple CTEs in the main query. Example: WITH cte1 AS ( SELECT id, salary FROM employees ), cte2 AS ( SELECT id FROM cte1 WHERE salary > 60000 ) SELECT cte1.id, cte1.salary FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

Result

Returns ids and salaries of employees earning more than 60000.

Knowing you can combine CTEs in the final query gives you powerful ways to organize complex data retrieval.

6

Advanced

Performance Considerations with Nested CTEs

🤔Before reading on: Do you think referencing one CTE inside another always improves query speed? Commit to yes or no.

Concept: CTEs referencing other CTEs can affect query performance depending on how the database engine optimizes them.

Some databases treat CTEs as inline views, others materialize them temporarily. This affects speed and memory. Example: Using many layered CTEs might slow down queries if materialized multiple times. You can check query plans to see how CTEs are handled.

Result

Understanding that CTE layering can impact performance helps you write efficient queries.

Knowing how your database executes CTEs prevents unexpected slowdowns in complex queries.

7

Expert

Recursive CTEs Using CTE References

🤔Before reading on: Can a CTE reference itself directly or indirectly to perform recursion? Commit to yes or no.

Concept: A recursive CTE references itself to perform repeated operations like traversing hierarchies or sequences.

Recursive CTEs have two parts: an anchor query and a recursive query that references the CTE itself. Example: WITH RECURSIVE cte AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte; This returns numbers 1 through 5 by repeatedly referencing itself.

Result

Returns rows with numbers 1, 2, 3, 4, 5 generated recursively.

Understanding recursive CTEs reveals the power of CTE references to solve complex problems like tree traversal.

Under the Hood

When a query with multiple CTEs runs, the database processes each CTE in order. Each CTE acts like a temporary named result set that can be used by subsequent CTEs or the main query. Depending on the database engine, CTEs may be optimized as inline views (merged into the main query) or materialized as temporary tables. This affects how often the data is computed and stored during query execution.

Why designed this way?

CTEs were designed to improve query readability and maintainability by allowing complex queries to be broken into named parts. Referencing one CTE inside another supports modular query building. Alternatives like nested subqueries were harder to read and debug. The design balances clarity with performance, though some engines treat CTEs differently, leading to trade-offs.

WITH clause processing order:

┌─────────────┐
│  WITH start │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  CTE1 query │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  CTE2 query │ (can use CTE1)
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Final Query │ (uses CTE1, CTE2)
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does referencing a CTE inside another CTE always improve query performance? Commit to yes or no.

Common Belief: Referencing one CTE inside another always makes queries faster because it breaks down logic.

Reality: Sometimes, layering CTEs can slow down queries if the database materializes each CTE separately, causing repeated work.

Why it matters: Assuming CTE references always improve speed can lead to inefficient queries and slow applications.

Quick: Can you reference a CTE that is defined after the current one in the WITH clause? Commit to yes or no.

Common Belief: You can reference any CTE in the WITH clause regardless of order.

Reality: CTEs can only reference CTEs defined before them in the WITH clause, not after.

Why it matters: Trying to reference a later CTE causes syntax errors and confusion.

Quick: Does a CTE always create a temporary table stored on disk? Commit to yes or no.

Common Belief: CTEs always create temporary tables on disk for reuse.

Reality: Many databases treat CTEs as inline views without materializing them, so no temporary table is created unless forced.

Why it matters: Misunderstanding this affects how you optimize queries and predict resource usage.

Quick: Can recursive CTEs only reference themselves directly? Commit to yes or no.

Common Belief: Recursive CTEs must reference themselves directly and cannot use other CTEs recursively.

Reality: Recursive CTEs can reference other CTEs indirectly as part of their recursion, allowing complex recursive logic.

Why it matters: Knowing this expands the power of recursive queries beyond simple self-reference.

Expert Zone

1

Some database engines optimize CTEs by inlining them, while others materialize them, affecting performance and memory usage.

2

Referencing multiple CTEs in complex queries can lead to repeated scans if not carefully written or indexed.

3

Recursive CTEs can be combined with window functions for advanced hierarchical data analysis.

When NOT to use

Avoid using multiple layered CTEs in performance-critical queries if your database materializes them inefficiently. Instead, consider temporary tables or derived tables with proper indexing. For very large datasets, window functions or indexed views might be better alternatives.

Production Patterns

In production, CTE referencing is used to break down complex business logic into readable steps, such as filtering, aggregation, and ranking. Recursive CTEs are common for organizational charts or bill-of-materials queries. Query plans are reviewed to ensure CTEs do not cause performance bottlenecks.

Connections

Functional Programming

Both use building blocks that reference previous results to create complex outputs.

Understanding how CTEs reference each other is like composing functions that build on each other's output, improving modularity and clarity.

Mathematical Induction

Recursive CTEs mirror the stepwise logic of induction, building solutions from a base case upward.

Seeing recursive CTEs as induction helps grasp how repeated self-reference solves problems like sequences or hierarchies.

Project Management Work Breakdown Structure (WBS)

Breaking a query into CTEs is like decomposing a project into smaller tasks that build on each other.

Knowing this helps organize complex queries systematically, just like managing complex projects.

Common Pitfalls

#1 Referencing a CTE before it is defined causes errors.

Wrong approach:

WITH cte2 AS (SELECT * FROM cte1), cte1 AS (SELECT 1 AS val) SELECT * FROM cte2;

Correct approach:

WITH cte1 AS (SELECT 1 AS val), cte2 AS (SELECT * FROM cte1) SELECT * FROM cte2;

Root cause:

CTEs must be defined in order because each can only reference previously defined CTEs.

#2 Assuming CTEs always improve performance without checking execution plans.

Wrong approach:

WITH cte1 AS (SELECT * FROM large_table), cte2 AS (SELECT * FROM cte1 WHERE condition) SELECT * FROM cte2;

Correct approach:

Rewrite query or use indexes and check EXPLAIN plans to ensure CTEs are optimized.

Root cause:

Misunderstanding how the database executes CTEs leads to inefficient queries.

#3 Trying to reference a CTE multiple times expecting it to be computed once.

Wrong approach:

WITH cte AS (SELECT * FROM big_table) SELECT * FROM cte JOIN cte ON cte.id = cte.id;

Correct approach:

Use temporary tables or subqueries if repeated use of large CTE causes performance issues.

Root cause:

CTEs may be re-evaluated each time they are referenced, causing repeated work.

Key Takeaways

CTEs let you create temporary named result sets inside SQL queries for better organization.

One CTE can reference another defined before it, enabling stepwise query building.

This layering makes complex queries easier to read, maintain, and debug.

However, CTEs may affect performance depending on how the database engine handles them.

Recursive CTEs extend this concept to solve problems needing repeated self-reference.

Practice

1. What is the main purpose of referencing one CTE inside another CTE in SQL?

easy

A. To create permanent tables in the database

B. To speed up the database server automatically

C. To avoid using SELECT statements

D. To break down complex queries into smaller, easier steps

Solution

1. Step 1: Understand what CTEs do

   CTEs (Common Table Expressions) let you write parts of a query separately for clarity.

2. Step 2: See why referencing one CTE inside another helps

   Referencing one CTE inside another breaks complex logic into smaller, readable steps without repeating code.

3. Final Answer:

   To break down complex queries into smaller, easier steps -> Option D

4. Quick Check:

   CTEs help simplify queries = C [OK]

Hint: Think of CTEs as building blocks for queries [OK]

Common Mistakes:

• Confusing CTEs with permanent tables
• Believing CTEs speed up queries automatically
• Thinking CTEs replace SELECT statements

2. Which of the following is the correct syntax to reference one CTE inside another in SQL?

easy

A. WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM cte1 WHERE id > 10) SELECT * FROM cte2;

B. WITH cte1 AS (SELECT * FROM table1) SELECT * FROM cte1; WITH cte2 AS (SELECT * FROM cte1 WHERE id > 10);

C. CREATE CTE cte1 AS SELECT * FROM table1; CREATE CTE cte2 AS SELECT * FROM cte1 WHERE id > 10;

D. SELECT * FROM cte1; WITH cte2 AS (SELECT * FROM cte1 WHERE id > 10);

Solution

1. Step 1: Review correct CTE syntax

   Multiple CTEs are defined together after WITH, separated by commas, and can reference each other.

2. Step 2: Check each option

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM cte1 WHERE id > 10) SELECT * FROM cte2; correctly defines cte1, then cte2 referencing cte1, then selects from cte2. Others misuse WITH or CREATE keywords.

3. Final Answer:

   WITH cte1 AS (SELECT * FROM table1), cte2 AS (SELECT * FROM cte1 WHERE id > 10) SELECT * FROM cte2; -> Option A

4. Quick Check:

   Multiple CTEs separated by commas = A [OK]

Hint: Use one WITH clause for all CTEs, separated by commas [OK]

Common Mistakes:

• Using multiple WITH clauses separately
• Trying to CREATE CTEs like tables
• Placing SELECT before WITH

3. Given the following SQL query, what will be the output?

WITH first_cte AS (
  SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3
), second_cte AS (
  SELECT num * 10 AS val FROM first_cte WHERE num > 1
)
SELECT * FROM second_cte ORDER BY val;

medium

A. [20, 30]

B. Syntax error

C. [1, 2, 3]

D. [10, 20, 30]

Solution

1. Step 1: Analyze first_cte output

   first_cte returns rows with num = 1, 2, 3.

2. Step 2: Analyze second_cte filtering and calculation

   second_cte selects num * 10 as val where num > 1, so num=2 and 3, val=20 and 30.

3. Final Answer:

   [20, 30] -> Option A

4. Quick Check:

   Filter num > 1, multiply by 10 = [20, 30] [OK]

Hint: Trace each CTE step by step to find final output [OK]

Common Mistakes:

• Including num=1 in second_cte output
• Confusing num and val columns
• Assuming syntax error due to multiple CTEs

4. Identify the error in this SQL query using CTEs:

WITH cte1 AS (
  SELECT id, name FROM users
),
cte2 AS (
  SELECT id, name FROM cte3 WHERE id > 5
)
SELECT * FROM cte2;

medium

A. SELECT statement missing after CTEs

B. Missing comma between CTE definitions

C. cte2 references cte3 which is not defined

D. CTEs cannot reference other CTEs

Solution

1. Step 1: Check CTE definitions

   cte1 is defined, but cte2 references cte3 which is not defined anywhere.

2. Step 2: Confirm syntax correctness

   Comma between cte1 and cte2 is present, and SELECT after CTEs is correct, so no syntax error there.

3. Final Answer:

   cte2 references cte3 which is not defined -> Option C

4. Quick Check:

   Undefined CTE reference = A [OK]

Hint: Check all referenced CTE names are defined above [OK]

Common Mistakes:

• Assuming missing comma error
• Thinking CTEs can't reference others
• Overlooking undefined CTE names

5. You have two CTEs: sales_cte with columns (product_id, total_sales) and products_cte with columns (product_id, product_name). How do you write a query using CTE referencing another CTE to get product names with sales over 1000?

hard

A. WITH sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id) SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM sales_cte WHERE total_sales > 1000);

B. WITH sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id), products_cte AS (SELECT product_id, product_name FROM products), final_cte AS (SELECT p.product_name FROM products_cte p JOIN sales_cte s ON p.product_id = s.product_id WHERE s.total_sales > 1000) SELECT * FROM final_cte;

C. WITH products_cte AS (SELECT product_id, product_name FROM products), sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id) SELECT product_name FROM products_cte WHERE total_sales > 1000;

D. WITH final_cte AS (SELECT product_name FROM products JOIN sales ON products.product_id = sales.product_id WHERE sales.amount > 1000) SELECT * FROM final_cte;

Solution

1. Step 1: Define sales_cte and products_cte correctly

   sales_cte sums sales per product_id; products_cte selects product info.

2. Step 2: Create final_cte referencing both CTEs

   Join products_cte and sales_cte on product_id, filter total_sales > 1000, select product_name.

3. Step 3: Select from final_cte

   Final SELECT returns product names with sales over 1000.

4. Final Answer:

   WITH sales_cte AS (SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id), products_cte AS (SELECT product_id, product_name FROM products), final_cte AS (SELECT p.product_name FROM products_cte p JOIN sales_cte s ON p.product_id = s.product_id WHERE s.total_sales > 1000) SELECT * FROM final_cte; -> Option B

5. Quick Check:

   Layered CTEs with join and filter = B [OK]

Hint: Build CTEs stepwise, join in last CTE for filtering [OK]

Common Mistakes:

• Referencing columns not in CTEs
• Filtering before joining CTEs
• Not joining CTEs properly