Medium10 minDatabase Fundamentals
UpdatedAug 2, 2026
Edit

Common Table Expressions (CTEs)

CONCEPTS:Common Table Expressions (CTE)

Question Variations

  • "What is a Common Table Expression (CTE), and how does it differ from a Subquery?"
  • "What are the benefits of using CTEs in terms of query readability and maintenance?"
  • "What is a 'Recursive CTE,' and what are some common use cases for it?"
  • "Can you use a CTE to perform a DELETE or UPDATE operation? How?"

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.

Question Variations

  • “What is a Common Table Expression (CTE), and how does it differ from a Subquery?”
  • “What are the benefits of using CTEs in terms of query readability and maintenance?”
  • “What is a ‘Recursive CTE,’ and what are some common use cases for it?”
  • “Can you use a CTE to perform a DELETE or UPDATE operation? How?”

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).