CodeBlanks Logo Icon CodeBlanks
Login Register

Anchor and recursive parts

Learn the two parts joined by UNION ALL.

Anchor and recursive

The anchor is the starting point, the recursive part calls itself.

-- anchor: SELECT 1
-- recursive: SELECT n + 1 FROM countdown WHERE n < 5

Anchor is base, recursive is step.

Anchor and recursive — details

The anchor gives a fixed result. The recursive part builds on the previous step. Results merge with UNION ALL.

Each round only processes the rows produced by the previous round; recursion stops on its own in the first round that produces no new rows.

Practice Anchor and recursive

Write a recursive CTE: anchor SELECT 1, recursive SELECT n+1 ... WHERE n < 5.

Starter code

query.sql
-- anchor and recursive
WITH RECURSIVE countdown(n) AS (
  SELECT 1

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 Add UNION ALL

Question 
  SELECT 1
  ____
  SELECT n + 1 FROM countdown WHERE n < 5
Answer 
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM countdown WHERE n < 5

Explanation: UNION ALL is the right answer.

2 What does the anchor do?

  • The result
  • Sorting
  • A filter
  • The starting point Correct answer

Explanation: The anchor is the starting point.

3 Anchor and recursive parts: match the terms

Term Meaning
anchor base
recursive recursive
UNION ALL combine
step loop

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