Recursive CTE Explained Simply with Example

🧩 Part 1: What is a CTE? 

“CTE stands for Common Table Expression.
It’s like creating a temporary table that exists only while your query runs.
You define it using the WITH keyword.”

Example:

WITH my_cte AS (
    SELECT * FROM employees
)
SELECT * FROM my_cte;

“This is a normal CTE — not recursive yet.
You can think of it as giving a name to a subquery so it’s easier to read and reuse.”

🔁 Part 2: What is a Recursive CTE? 

“A Recursive CTE is special because it refers to itself inside its definition.
That means the query runs repeatedly — just like recursion in programming.”

“It has two main parts:”

1. Anchor member → the starting query
2. Recursive member → the query that references the CTE itself

“These two parts are combined using UNION ALL.”

💡 Part 3: Syntax Template

WITH cte_name AS (
    -- Anchor member
    SELECT ... -- base result

    UNION ALL

    -- Recursive member
    SELECT ... FROM cte_name ... -- refers to itself
)
SELECT * FROM cte_name;

“SQL keeps running the recursive part until no new rows are returned.”

🌳 Part 4: Example — Generate Numbers from 1 to 10

“Let’s understand this with a simple example: generating numbers 1 to 10.”

WITH RECURSIVE numbers AS (
    -- Anchor part
    SELECT 1 AS n

    UNION ALL

    -- Recursive part
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

Explanation:

“The first query creates one row with value 1.
Then the recursive part keeps adding 1 until it reaches 10.
SQL keeps joining the results together — so finally you get a column of numbers from 1 to 10!”

👨‍👩‍👧 Part 5: Real-Life Example — Employee Hierarchy

“Now, let’s look at a real-world example — suppose we have an employee table:”

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50),
    manager_id INT
);

“Now we want to find all employees under Alice, directly or indirectly.
Recursive CTE makes this super easy.”

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor part: start with Alice
    SELECT emp_id, emp_name, manager_id
    FROM employees
    WHERE emp_name = 'Alice'

    UNION ALL

    -- Recursive part: find employees reporting to previous level
    SELECT e.emp_id, e.emp_name, e.manager_id
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM employee_hierarchy;

Output:

“You can see that starting from Alice, we got the entire hierarchy below her — Bob, Charlie, David, and Eva — all in one query!”

⚙️ Part 6: Key Points to Remember

✅ Recursive CTE has two parts — anchor and recursive.
✅ They are combined using UNION ALL.
✅ It keeps running until no new records are produced.
✅ Useful for hierarchical data, tree structures, bill of materials, or sequence generation.