CTEs in SQL: Runnable Tutorial with Non-Recursive and Recursive Examples

A long nested SQL query may return the right answer and still be painful to trace. A reporting hierarchy feels harder because ordinary SQL has no visible loop. Run the nine-row employee table through non-recursive and recursive CTEs, inspect intermediate results, and trace a tree one level at a time. All queries run in PostgreSQL; other engines have small recursive-syntax differences.

CTEs in SQL: the mental model, syntax, and one reusable table

A common table expression, or CTE, is a named query result available only to the statement immediately following WITH. Its basic non-recursive shape is:

WITH cte_name AS (
    <query>
)
SELECT ...
FROM cte_name;

The name helps divide a statement into understandable steps. It does not create a stored table, a persistent view, or an automatic cache.

Create the nine-row employee table in PostgreSQL:

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    manager_id INTEGER REFERENCES employees(emp_id),
    dept VARCHAR(20) NOT NULL,
    salary INTEGER NOT NULL
);

INSERT INTO employees (emp_id, name, manager_id, dept, salary) VALUES
    (1, 'Mira',   NULL, 'Engineering', 120000),
    (2, 'Arun',      1, 'Engineering',  90000),
    (3, 'Beena',     1, 'Engineering',  85000),
    (4, 'Chetan',    2, 'Engineering',  65000),
    (5, 'Diya',      2, 'Engineering',  60000),
    (6, 'Eshan',     3, 'Engineering',  70000),
    (7, 'Farah',  NULL, 'Sales',       110000),
    (8, 'Gopal',     7, 'Sales',        75000),
    (9, 'Hina',      7, 'Sales',        68000);

SQL Queries and Joins in DBMS owns fixed employee-manager self joins. The recursive CTE below walks arbitrary hierarchy depth instead. Place this topic within the wider CS Fundamentals for Exams & Placements sequence.

A non-recursive CTE: calculate department averages once

The first CTE groups the nine employees by department:

WITH dept_average AS (
    SELECT dept,
           COUNT(*) AS employee_count,
           AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept
)
SELECT dept,
       employee_count,
       ROUND(avg_salary, 2) AS avg_salary
FROM dept_average
ORDER BY dept;

Engineering has six salaries totalling 490000, so its average is 490000 / 6 = 81666.67 after rounding. Sales has three salaries totalling 253000, giving 253000 / 3 = 84333.33.

Evaluation has three steps. The inner query calculates two grouped rows. Those rows become available under the temporary name dept_average. The outer query rounds the averages and sorts the departments. Once this statement ends, a separate query cannot select from dept_average.

Notice that the inner CTE keeps the full average value. Rounding happens only in the displayed result, so later calculations can still compare against the unrounded value. This separation between calculation and presentation is useful whenever decimals affect a filter.

Chained CTEs: find employees above their department average

Multiple CTEs are separated by commas. A later CTE can use one defined earlier in the same WITH list:

WITH dept_average AS (
    SELECT dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept
),
above_average AS (
    SELECT e.emp_id, e.name, e.dept, e.salary, d.avg_salary
    FROM employees AS e
    JOIN dept_average AS d ON d.dept = e.dept
    WHERE e.salary > d.avg_salary
)
SELECT emp_id, name, dept, salary,
       ROUND(avg_salary, 2) AS dept_average
FROM above_average
ORDER BY dept, salary DESC;

Against Engineering's 81666.67, Mira 120000, Arun 90000, and Beena 85000 survive. Eshan 70000, Chetan 65000, and Diya 60000 do not. Against Sales' 84333.33, only Farah 110000 survives; Gopal 75000 and Hina 68000 remain below it.

SQL Subqueries and Correlated Subqueries owns per-row department-average comparisons. This CTE version expresses each department average in one named step, then joins that result to employees. During development, make either CTE the final SELECT target to inspect it; both run within one SQL statement.

Recursive CTEs: walk the employee hierarchy from Mira

A recursive CTE has three working parts. The anchor query supplies the starting rows. UNION ALL connects it to a recursive member, which refers to the CTE and produces the next level. PostgreSQL and MySQL use WITH RECURSIVE. SQL Server follows the same anchor and recursive-member model but omits the RECURSIVE keyword.

WITH RECURSIVE org_chart (emp_id, name, manager_id, depth) AS (
    SELECT emp_id, name, manager_id, 0
    FROM employees
    WHERE emp_id = 1

    UNION ALL

    SELECT e.emp_id, e.name, e.manager_id, o.depth + 1
    FROM employees AS e
    JOIN org_chart AS o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, manager_id, depth
FROM org_chart
ORDER BY depth, emp_id;

The anchor produces Mira at depth 0. Wave one finds Arun and Beena at depth 1. Wave two finds Chetan and Diya under Arun, plus Eshan under Beena, at depth 2. The next wave finds no employee managed by those three people, so recursion stops. Farah, Gopal, and Hina never enter the result because the anchor selected only Mira's tree. The ordered IDs are 1, then 2, 3, then 4, 5, 6.

Recursive CTE termination: predict rows before running the query

A numerical CTE makes the stopping rule easy to see:

WITH RECURSIVE steps(n) AS (
    SELECT 2
    UNION ALL
    SELECT n + 3
    FROM steps
    WHERE n < 11
)
SELECT n FROM steps ORDER BY n;

The anchor produces 2. Successive waves produce 5, 8, and 11. The row 11 is generated from 8; on the next wave, 11 < 11 is false, so 14 is never generated. The final output is exactly 2, 5, 8, 11. The condition tests the current row, not the proposed next value, which makes this a useful output-tracing check.

Leaves stop the organisation query naturally because no employee names them as manager. A missing numerical boundary or a cycle in hierarchy data can instead continue until the database raises an error or reaches an engine-specific recursion limit.

CTE vs subquery, view, and temporary table

Choose by scope and reuse, not by a claim that one spelling is always faster.

A CTE is not automatically faster than a subquery, and its name does not guarantee one calculation. Depending on the database engine and version, the optimiser may inline, materialise, or otherwise transform a non-recursive CTE. Compare actual plans with EXPLAIN on the database you use.

The practical rule is simple: choose a CTE first when it makes the statement clearer and easier to verify. Change the structure only when a measured plan or a genuine multi-statement reuse requirement justifies it.

CTE mistakes, exam questions, and interview checks

Four mistakes cause many incorrect answers:

1. A CTE is scoped to one following statement. A later SELECT * FROM dept_average fails.

2. A later CTE may refer to an earlier CTE, but a forward reference is not portable.

3. The anchor and recursive members must return the same number of compatible columns.

4. UNION ALL preserves every generated row. Cyclic hierarchy data needs validation or an engine-appropriate visited-path guard.

Now test the boundary logic. If the numerical condition becomes WHERE n <= 11, the output is 2, 5, 8, 11, 14: the current row 11 now passes and produces 14. If the hierarchy's recursive member adds WHERE o.depth < 1, Mira at depth 0 can produce Arun and Beena at depth 1, but those rows cannot produce another level. The result contains exactly three rows.

Assessments commonly ask you to identify the anchor and recursive member, count waves, predict output, repair scope or column mismatches, or rewrite a nested query as named steps. Try the trace-style problems in SQL Query MCQs: 12 Solved (SELECT, Joins, Subqueries).

CTEs in SQL: the short version and next step

Keep five rules ready:

1. WITH names a statement-local result.

2. Comma-separated CTEs form a pipeline.

3. Recursion needs an anchor, recursive member, and stopping condition.

4. The anchor and recursive column shapes must agree.

5. More readable SQL is not proof of faster SQL.

Run the nine-row setup, predict the four above-average employees, and trace Mira's six-row hierarchy on paper before changing either query. For a wider DBMS and SQL learning sequence, continue with GATE Guidance by Sanchit Sir.