UpdatedAug 2, 2026

Common Table Expressions (CTEs)

Why This Is Asked

CTEs are essential for writing readable, maintainable SQL. They are also the primary way to perform recursive queries in SQL.

Key Concepts

Readability: Breaking down large queries.
Recursion: Parent-child relationships.
DML usage: Using CTEs for Insert/Update/Delete.

Answers by Technology

+ Add Variant

Expected Answer (PostgreSQL 16)

PostgreSQL supports standard, recursive, and writable CTEs.

Recursive: WITH RECURSIVE for hierarchy.
Writable: INSERT/UPDATE/DELETE inside a CTE.

Why It Matters

CTEs improve readability and allow for complex multi-step data transformations in a single statement. PostgreSQL 16’s optimization of CTE materialization makes them as fast as subqueries while remaining much more maintainable.

SQL Example

-- Writable CTE: Move deleted rows to an archive table
WITH deleted_rows AS (
    DELETE FROM tasks 
    WHERE status = 'done' 
    RETURNING *
)
INSERT INTO task_archive
SELECT * FROM deleted_rows;

Common Mistakes

Infinite Loops: Forgetting a termination condition in WITH RECURSIVE.
Materialization Overhead: In older versions, CTEs were always materialized. In PG 16, this is mostly automatic.

Follow-up Questions

Can you update data with a CTE? (Answer: Yes, in PostgreSQL).
How to prevent infinite loops? (Answer: Use UNION instead of UNION ALL or a depth counter).

Expected Answer (SQL Server 2022)

SQL Server CTEs are defined with WITH and are valid for a single statement.

Recursion: Standard for tree traversal.
Readability: Replaces complex subqueries.

Why It Matters

CTEs are essential for writing maintainable SQL. They allow you to define logic sequentially at the top of a query. SQL Server 2022’s Intelligent Query Processing ensures they are executed efficiently.

SQL Example

-- Recursive CTE for Hierarchy
WITH OrgChart AS (
    SELECT EmployeeID, ManagerID, Name, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name, oc.Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart;

Common Mistakes

Semicolon requirement: The statement before WITH must end with a semicolon.
MAXRECURSION limit: Default is 100; recursive queries on deep trees will fail unless this is increased.

Follow-up Questions

CTE vs Temp Table? (Answer: CTEs are for a single query; Temp tables are for a session).
Can a CTE be used in an UPDATE? (Answer: Yes, you can join a CTE in an UPDATE statement).