CTE

A CTE (common table expression) is a named, temporary result set defined with the WITH clause at the start of an SQL statement and referenced by that statement as if it were a table. In SQL, a CTE exists only for the duration of one query: it is not stored in the schema, and no other statement can see it.

CTEs serve two distinct purposes. Non-recursive CTEs break a complex query into readable, named steps, much like variables in a program. Recursive CTEs let a query refer to its own output, which makes SQL capable of walking hierarchies and graphs such as organizational charts, bills of materials or category trees. CTEs are part of the SQL standard since SQL:1999 and are supported by all major relational database systems.

How a common table expression is structured

A CTE consists of the WITH keyword, a name, an optional column list and a subquery in parentheses, followed by the main statement that uses it. Several CTEs can be chained with commas, and each later CTE may reference earlier ones, which turns a nested query into a top-down pipeline.

A typical form is WITH monthly AS (SELECT ...), ranked AS (SELECT ... FROM monthly) SELECT * FROM ranked WHERE .... The table below lists the parts of this syntax and their role.

A CTE may be referenced several times in the same statement, for example joined to itself, which a derived table in the FROM clause cannot do without repeating its whole text. The CTE scope ends with the statement, so it never pollutes the database namespace.

Recursive CTEs and hierarchical queries

A recursive CTE is declared with WITH RECURSIVE in PostgreSQL, MySQL, MariaDB and SQLite, while SQL Server and Oracle accept plain WITH. Its body has two parts joined by UNION ALL: an anchor member that returns the starting rows, and a recursive member that joins back to the CTE itself.

The engine evaluates it iteratively. It runs the anchor, feeds the new rows into the recursive member, and repeats until an iteration returns no rows. The table below shows this process for a simple employee hierarchy.

Recursion is only safe when it terminates. Cyclic data, such as a graph in which A points to B and B back to A, can produce an infinite loop. Engines add safeguards: SQL Server stops at 100 recursion levels by default through the MAXRECURSION option, MySQL limits depth with cte_max_recursion_depth (default 1000), and PostgreSQL 14 and later offer a standard CYCLE clause. Tracking a visited path or a depth counter is good practice.

CTE compared with subqueries, views and temporary tables

A CTE is one of several ways to name or reuse an intermediate result in SQL. The choice depends on lifetime, reuse and performance, as the comparison below shows.

Functionally, a non-recursive CTE is equivalent to a derived table; its advantage is clarity and reuse within the query. When the same intermediate result is needed by several statements, or when it is large and benefits from its own index, a temporary table is usually the better choice.

Performance and optimizer behavior

Whether a CTE is fast depends on whether the optimizer inlines it into the main query or materializes it as a separate intermediate result. Inlining allows filters from the outer query to be pushed down into the CTE; materialization computes the CTE once and reuses it.

Two consequences follow. In SQL Server, a CTE referenced three times may be evaluated three times, so an expensive CTE used repeatedly can be slower than a temporary table. In older PostgreSQL versions, a CTE could block index use for outer filters. Checking the execution plan with EXPLAIN is the reliable way to confirm behavior.

Practical guidelines and pitfalls

CTEs work best as a tool for readable, step-by-step query design, combined with window functions, GROUP BY and HAVING. A few rules keep them correct and efficient.

• Give each CTE a descriptive name that states what the rows represent, such as active_customers rather than t1.
• Filter early inside the CTE when the optimizer may materialize it, so the intermediate result stays small.
• Always bound recursion with a depth column or cycle detection when the data may contain loops.
• Use UNION ALL in recursive CTEs unless duplicates must be removed, because UNION adds sorting or hashing at every iteration.
• Remember the semicolon rule in SQL Server: the statement before WITH must be terminated, or the parser misreads it.

A last pitfall is stacking too many CTEs. A chain of ten steps is readable only when each step does one clear job; otherwise, splitting the logic into views or separate statements is easier to test and maintain.

Stripped to the essentials

A CTE is a way to give a name to a step inside a database question. Instead of writing one huge, nested query, the author writes small named pieces. The first piece might collect last month’s sales. The next piece ranks them. The final line simply asks for the top results.

It works like the steps of a recipe. Each step has a clear name and builds on the one before. The result is the same, but people can read and check it far more easily. A special recursive form can also follow chains, such as who reports to whom in a company.

Where it is applied

A manufacturing company stores its bill of materials in one table: each row says which component is part of which assembly and in what quantity. A planner needs the total number of screws required to build 500 units of a machine whose parts are nested up to six levels deep.

A data analyst writes a recursive CTE. The anchor selects the direct components of the machine, and the recursive member repeatedly joins to the parts table while multiplying quantities along the path. A depth column stops the recursion at ten levels as a safety limit. The final SELECT groups the results by part number and multiplies by 500, so one query replaces a manual spreadsheet exercise that previously took a full day and often missed deeply nested parts.