CodeBlanks Logo Icon CodeBlanks
Login Register

What is a recursive CTE?

Learn the idea of a self-calling CTE.

What is a recursive CTE?

A recursive CTE is a CTE that references itself. Used for hierarchies and trees.

WITH RECURSIVE countdown(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;

This query counts from 1 to 5.

WITH RECURSIVE defines a self-referencing temporary result set (CTE) named countdown; the query after it uses it like a table. All columns are selected from countdown.

What is a recursive CTE? — details

A recursive CTE has an anchor and a recursive part. They are joined with UNION ALL.

It is used for tasks where you don't know the number of steps in advance, such as category trees, management chains or generating a sequence from 1 to N.

Practice What is a recursive CTE?

Write a RECURSIVE CTE that counts from 1 to 5.

Starter code

query.sql
-- recursive CTE
WITH RECURSIVE

Sample solution

query.sql
WITH RECURSIVE countdown(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;

Expected output

n
1
2
3
4
5

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 What does a recursive CTE combine?

  • INTERSECT
  • JOIN
  • UNION ALL Correct answer
  • EXCEPT

Explanation: A recursive CTE combines with UNION ALL.

2 Add RECURSIVE

Question 
WITH ____ countdown(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;
Answer 
WITH RECURSIVE countdown(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5
)
SELECT * FROM countdown;

Explanation: RECURSIVE is the right answer.

3 What is a recursive CTE: match the terms

Term Meaning
recursive recursive
anchor base
UNION ALL combine
hierarchy tree

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization