On this page

Before You Start
What Is a Common Table Expression?
How Do You Write a WITH Clause?
Using Multiple CTEs in One Query
How Does WITH RECURSIVE Work?
Walking a Hierarchy with a Recursive CTE
Generating a Date Series to Fill Gaps
Are CTEs Materialized? What Each Engine Does
How Do PostgreSQL, MySQL, SQLite, and SQL Server Differ?
Common Pitfalls and Debugging Tips
Best Practices and Trade-offs
Conclusion
Frequently Asked Questions
Self-Check
Next Steps

SQL CTEs: WITH and WITH RECURSIVE

Q: What is the difference between a CTE and a subquery?

A CTE is a subquery that has been given a name at the top of the statement. The main query then reads it like a table, it can be referenced more than once, and it can be recursive. A plain subquery sits inline where it is used and has no name to reuse.

Q: Is a CTE faster than a subquery?

Neither form guarantees better performance on your data. An optimizer may inline a CTE or keep an intermediate result, depending on the engine and query. Referring to the CTE repeatedly can change that choice. Compare execution plans and measured results on your own data before claiming a CTE is faster.

Q: Can one CTE refer to another CTE?

A CTE can read from any CTE defined earlier in the same WITH clause, which lets a query build up in named steps. Defining them in order is the portable habit, since MySQL and SQL Server reject a reference to a CTE defined later.

Q: What is the difference between a CTE and a temporary table?

A CTE lasts for a single statement and needs no CREATE or DROP. A temporary table usually lasts until the session ends, can be indexed, and can be read by several statements. Reach for a temporary table when many later queries need the same intermediate result.

Q: How do you stop a recursive CTE from looping forever?

Give the recursive member a condition that eventually returns no rows, such as a depth limit. For data that can contain cycles, track the visited ids and skip any already seen, or use UNION when each row holds only identifying columns, so repeats are discarded. MySQL and SQL Server also enforce recursion limits, 1000 and 100 levels by default.

Q: Does SQL Server support WITH RECURSIVE?

SQL Server supports recursive CTEs, but its syntax has no RECURSIVE keyword. Write a plain WITH clause, and SQL Server treats the CTE as recursive when it refers to its own name. The anchor and recursive members must be joined with UNION ALL.

Q: Can you use a CTE in an UPDATE or DELETE statement?

Yes, in all four engines covered here. PostgreSQL, MySQL, SQLite, and SQL Server all accept a WITH clause in front of UPDATE and DELETE. PostgreSQL goes further and allows INSERT, UPDATE, or DELETE statements inside the WITH clause itself.

Mark Gates

Published October 8, 2026 Updated October 8, 2026

A common table expression, or CTE, gives a query a name so the rest of a statement can refer to it. An accountant's worksheet works the same way: labeled working lines come first, and the final figure refers back to them. A recursive CTE builds each new line from the previous one.

Use a CTE when a query has steps a reader must follow in order, or when it must walk a hierarchy of unknown depth. Whether it also makes the query faster is a separate question that only the execution plan answers. The examples below compare salaries, walk a management hierarchy and fill missing dates.

Before You Start

Run the sample-table script below in a fresh scratch database first.
Know how joins connect rows and how UNION and UNION ALL combine results.
The syntax outline is not executable, and the labeled T-SQL example needs SQL Server.

What Is a Common Table Expression?

A common table expression is a named query that exists only for the statement it belongs to. It is defined in a WITH clause before the main query, and the main query refers to it by name in the same places it could name a table.

Three terms come up throughout this guide:

CTE name: the label on the worksheet line, such as dept_avg. It is visible only inside the one statement.
Anchor member: in a recursive CTE, the query that produces the starting rows without referring to the CTE, like the first line you write by hand.
Recursive member: the query that refers to the CTE's own name and produces the next rows from the rows the previous pass produced.

The Sample Tables

Apart from one counting example that needs no table, every example uses the two small tables below. The employees table stores a management hierarchy through manager_id, with salaries in thousands. The orders table has deliberate gaps in its dates for the series section.

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

INSERT INTO employees VALUES
  (1, 'Ava',   NULL, 'Exec',  250),
  (2, 'Ben',   1,    'Eng',   180),
  (3, 'Chloe', 1,    'Sales', 170),
  (4, 'Dan',   2,    'Eng',   140),
  (5, 'Erin',  2,    'Eng',   120),
  (6, 'Finn',  4,    'Eng',    95),
  (7, 'Gia',   3,    'Sales', 110),
  (8, 'Hugo',  3,    'Sales',  90);

CREATE TABLE orders (
  id        INTEGER PRIMARY KEY,
  order_day DATE NOT NULL,
  amount    INTEGER NOT NULL
);

INSERT INTO orders VALUES
  (1, '2026-03-01', 120),
  (2, '2026-03-02',  80),
  (3, '2026-03-02',  40),
  (4, '2026-03-04', 200),
  (5, '2026-03-06',  60);

The column types are plain enough for SQLite, PostgreSQL, MySQL, and SQL Server to accept the script unchanged. Ava has no manager, Ben and Chloe report to her, and Finn sits three levels down under Dan and Ben. The reporting lines form the following hierarchy:

Ava
├── Ben
│   ├── Dan
│   │   └── Finn
│   └── Erin
└── Chloe
    ├── Gia
    └── Hugo

A CTE Compared with a Subquery

Suppose the question is "who earns more than their department's average?" The first answer most people write puts the averages in a subquery inside the FROM clause:

SELECT e.name, e.dept, e.salary,
       ROUND(d.avg_sal, 1) AS avg_sal
FROM employees e
JOIN (
  SELECT dept,
         AVG(salary * 1.0) AS avg_sal
  FROM employees
  GROUP BY dept
) d ON d.dept = e.dept
WHERE e.salary > d.avg_sal
ORDER BY e.id;

The same query written with a CTE moves the averages to the top and gives them a name:

WITH dept_avg AS (
  SELECT dept,
         AVG(salary * 1.0) AS avg_sal
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.dept, e.salary,
       ROUND(d.avg_sal, 1) AS avg_sal
FROM employees e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_sal
ORDER BY e.id;

Both queries return the same three rows:

name	dept	salary	avg_sal
Ben	Eng	180	133.8
Chloe	Sales	170	123.3
Dan	Eng	140	133.8

The execution plan still depends on your database engine and data, so equivalent output alone does not prove equivalent performance. What changes is the reading order: the CTE version reads top to bottom, with the working line first and the question second.

The salary * 1.0 is there for SQL Server, where AVG over an integer column returns an integer and would report 133 instead of 133.75. The other three engines return a decimal average without it.

How Do You Write a WITH Clause?

The general shape is the keyword WITH, one or more named queries separated by commas, and then a single main statement that uses them:

WITH cte_name [(column, ...)] AS (
  SELECT ...
)[, another_cte AS (
  SELECT ...
)]
SELECT ... FROM cte_name ...;

The name follows the same rules as a table alias. It can even match a real table's name, in which case the CTE hides the table for that statement, which is legal but confusing.
The optional column list renames the columns, as in counter(n). It is useful when the inner query produces an unnamed expression such as SELECT 1.
The main statement is usually a SELECT, although all four engines also accept a WITH clause in front of UPDATE and DELETE.

A CTE lives for exactly one statement. Once the statement finishes, the name is gone, so the next query has to define it again or use a view or temporary table instead.

Using Multiple CTEs in One Query

Several CTEs go in one WITH clause, separated by commas, and each one can read from the CTEs defined before it. This is where the worksheet comparison earns its keep, because each working line can build on the lines above it.

This query counts, for each department, how many people earn more than that department's average. The first CTE calculates the averages, and the second CTE uses them:

WITH dept_stats AS (
  SELECT dept, COUNT(*) AS headcount,
         AVG(salary * 1.0) AS avg_sal
  FROM employees
  GROUP BY dept
),
above_avg AS (
  SELECT e.name, e.dept
  FROM employees e
  JOIN dept_stats d ON d.dept = e.dept
  WHERE e.salary > d.avg_sal
)
SELECT d.dept, d.headcount,
       COUNT(a.name) AS above_avg
FROM dept_stats d
LEFT JOIN above_avg a
  ON a.dept = d.dept
GROUP BY d.dept, d.headcount
ORDER BY d.dept;

dept	headcount	above_avg
Eng	4	2
Exec	1	0
Sales	3	1

Notice that dept_stats is used twice: once inside above_avg and once in the main query. A subquery in the FROM clause, which MySQL's manual calls a derived table, would have to be written out twice to do the same thing.

The LEFT JOIN keeps Exec in the result with a count of 0. Ava's salary equals her one-person department's average, so she does not count as above it.

Define each CTE before the ones that read it. MySQL and SQL Server document that a CTE can refer only to CTEs defined earlier in the same clause. PostgreSQL relaxes that rule when the clause starts with WITH RECURSIVE, and SQLite accepts a forward reference, although a query that relies on it will not port.

How Does WITH RECURSIVE Work?

A recursive CTE has two parts joined by UNION ALL (or UNION). The anchor member runs once and produces the starting rows. The recursive member then runs again and again, and each pass reads only the rows produced by the pass before it.

The engine stops when a pass produces no new rows. PostgreSQL's manual notes that the evaluation is iterative internally, even though the syntax is called recursive. The smallest useful example counts from 1 to 5:

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1
  FROM counter
  WHERE n < 5
)
SELECT n FROM counter;

n
1
2
3
4
5

Trace it the way the engine does, one worksheet line at a time:

The anchor SELECT 1 writes the first line, a single row with n = 1.
The recursive member reads that row and produces n = 2, which becomes the input to the next pass.
Passes continue until the input row is n = 5. The WHERE n < 5 filter then removes it, the pass produces nothing, and the recursion ends.

The WHERE clause in the recursive member is the stop condition. Leave it out and the query has no natural end, which is the first pitfall covered later on this page.

Walking a Hierarchy with a Recursive CTE

Hierarchies are the classic reason to learn recursive CTEs. The employees table only records each person's direct manager, so "everyone under Ben" or "everyone above Finn" needs as many joins as there are levels, and the number of levels is not known in advance.

Down the Tree: Everyone Below a Manager

Start with Ben as the anchor, then repeatedly find people whose manager is someone already found. A depth column records how many levels below Ben each person sits:

WITH RECURSIVE team AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE name = 'Ben'
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees e
  JOIN team t ON e.manager_id = t.id
)
SELECT name, depth
FROM team
ORDER BY depth, name;

name	depth
Ben	0
Dan	1
Erin	1
Finn	2

The first pass after the anchor finds Dan and Erin, whose manager is Ben. The second pass finds Finn, whose manager is Dan. The third pass finds nobody who reports to Finn, so the query stops without needing an explicit limit.

In this query the data itself supplies the stop condition, since the tree runs out of branches. That holds only while the data is a true tree, which the pitfalls section tests.

Building a Path for Every Row

A depth number says how far down someone sits, and a path says how they got there. This query starts from the person with no manager and carries a growing text path down each branch:

WITH RECURSIVE chart AS (
  SELECT id, name, 0 AS depth,
         CAST(name AS VARCHAR(200))
           AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, c.depth + 1,
         CAST(CONCAT(c.path, ' > ',
                     e.name)
           AS VARCHAR(200))
  FROM employees e
  JOIN chart c ON e.manager_id = c.id
)
SELECT path, depth
FROM chart
ORDER BY path;

path	depth
Ava	0
Ava > Ben	1
Ava > Ben > Dan	2
Ava > Ben > Dan > Finn	3
Ava > Ben > Erin	2
Ava > Chloe	1
Ava > Chloe > Gia	2
Ava > Chloe > Hugo	2

Sorting by the path lists each manager directly above their own reports, in the same order as an org chart drawn top to bottom. Two details in the query exist for portability:

CONCAT() joins the text in PostgreSQL, MySQL, SQL Server, and SQLite 3.44.0 or later. On older SQLite, use the || operator, which MySQL treats as logical OR by default.
The two CASTs give the path the same type in both members. SQLite would run without them, although MySQL and SQL Server would not, as the pitfalls section explains.
MySQL's CAST does not accept VARCHAR, so write CHAR(200) in both casts there. MySQL returns that as an unpadded variable-length string, whereas SQL Server would pad a CHAR(200) with spaces, so keep VARCHAR on SQL Server.

Up the Tree: A Chain of Managers

Reversing the join condition walks upward instead. Start at Finn, and on each pass find the person whose id matches the current row's manager_id:

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id,
         0 AS steps
  FROM employees
  WHERE name = 'Finn'
  UNION ALL
  SELECT e.id, e.name, e.manager_id,
         c.steps + 1
  FROM employees e
  JOIN chain c ON e.id = c.manager_id
)
SELECT name, steps
FROM chain
ORDER BY steps;

name	steps
Finn	0
Dan	1
Ben	2
Ava	3

The walk stops at Ava because her manager_id is NULL, and a join on NULL matches nothing. The ORDER BY steps matters as well, since no engine guarantees the order of a result, recursive or otherwise, without one.

Generating a Date Series to Fill Gaps

The orders table has no rows for 3 March or 5 March. A plain GROUP BY order_day skips those days entirely, which leaves holes in a daily report or chart. A recursive CTE can generate every date in the range, and a LEFT JOIN then attaches the orders:

WITH RECURSIVE days(cal_day) AS (
  SELECT DATE('2026-03-01')
  UNION ALL
  SELECT DATE(cal_day, '+1 day')
  FROM days
  WHERE cal_day < '2026-03-06'
)
SELECT d.cal_day,
       COALESCE(SUM(o.amount), 0)
         AS total
FROM days d
LEFT JOIN orders o
  ON o.order_day = d.cal_day
GROUP BY d.cal_day
ORDER BY d.cal_day;

cal_day	total
2026-03-01	120
2026-03-02	120
2026-03-03	0
2026-03-04	200
2026-03-05	0
2026-03-06	60

The two missing days now appear with a total of 0, and 2 March correctly adds its two orders together. The recursive structure is portable, although date arithmetic is one of the least portable corners of SQL, so the anchor and the step change from engine to engine:

Engine	Anchor	Recursive Step
SQLite	`DATE('2026-03-01')`	`DATE(cal_day, '+1 day')`
PostgreSQL	`DATE '2026-03-01'`	`cal_day + 1`
MySQL	`DATE '2026-03-01'`	`cal_day + INTERVAL 1 DAY`
SQL Server	`CAST('2026-03-01' AS DATE)`	`DATEADD(day, 1, cal_day)`

SQL Server also needs the RECURSIVE keyword removed, and a range longer than 100 days needs OPTION (MAXRECURSION ...) on the outer query. PostgreSQL users can skip the recursion altogether with its built-in generate_series() function, which returns a series of numbers or timestamps that can be cast to dates.

Are CTEs Materialized? What Each Engine Does

Materializing a CTE means running it once and keeping the rows in a temporary result, which the engine reads wherever the name appears. The alternative is inlining, where the engine pastes the CTE's query into the main query and optimizes the whole thing together, the way it would treat a subquery.

Each of the two choices wins in different queries. Materializing avoids repeating expensive work when a CTE is referenced several times, while inlining lets the optimizer push a filter from the main query down into the CTE, so it reads only the rows it needs.

The four engines make that decision in different ways:

PostgreSQL inlines a non-recursive, side-effect-free CTE (a plain SELECT with no volatile functions) that the main query references once, and computes it once when it is referenced more than once. AS MATERIALIZED or AS NOT MATERIALIZED overrides that choice. Releases before PostgreSQL 12 always materialized, which is why older advice calls a CTE an "optimization fence".
MySQL handles CTEs the same way it handles derived tables: it merges them into the outer query when it can and materializes them otherwise. A materialized CTE is built once per query, a recursive CTE is always materialized, and the MERGE and NO_MERGE optimizer hints can steer each reference.
SQLite lets its query planner choose. Since 3.35.0, it materializes a CTE that is used more than once by default, and it accepts the PostgreSQL-style MATERIALIZED and NOT MATERIALIZED hints, which its documentation describes as non-binding.
SQL Server documents that CTE results are not materialized. Each reference to the CTE re-runs its query, and Microsoft's advice for a result needed in several places is a temporary table.

The practical upshot is that a CTE is a readability tool first. Check the plan with EXPLAIN (or the execution plan in SQL Server) before assuming a CTE made a query faster or slower, and write the materialization hint only when the plan shows a problem.

How Do PostgreSQL, MySQL, SQLite, and SQL Server Differ?

Plain CTEs behave much the same in all four engines on current releases. The differences sit mostly in recursion: how it is declared, and what stops a runaway query:

Engine	CTEs From	RECURSIVE Keyword	Recursion Limit
PostgreSQL	8.4	Required for recursion	No depth setting
MySQL	8.0	Required for recursion	1000 levels by default
SQLite	3.8.3	Optional	No depth setting
SQL Server	2005	Not part of the syntax	100 levels by default

The details behind those cells, engine by engine:

PostgreSQL allows only one recursive self-reference per query, on the right-hand side of the UNION. It adds SEARCH and CYCLE clauses that compute a depth-first or breadth-first ordering column and detect cycles for you, and it allows data-modifying statements such as DELETE ... RETURNING inside a WITH clause.
MySQL sets its limit with the cte_max_recursion_depth system variable, and a query that recurses past it is stopped with an error. The recursive member cannot use aggregate functions, window functions, GROUP BY, ORDER BY, or DISTINCT, although current releases allow LIMIT there.
SQLite accepts a recursive CTE with or without RECURSIVE, for compatibility with SQL Server and Oracle. Its recursive member cannot use aggregate or window functions, and a LIMIT on it stops the recursion once enough rows exist.
SQL Server treats a CTE as recursive whenever it refers to its own name. The anchor and recursive members must be joined with UNION ALL, the recursive member cannot use GROUP BY, DISTINCT, TOP, or outer joins, and OPTION (MAXRECURSION n) accepts 0 to 32767, where 0 removes the limit.

One more difference affects where a CTE can go. MySQL, PostgreSQL, and SQLite accept a WITH clause at the start of a subquery, while SQL Server does not allow a nested WITH inside a CTE's subquery, so there every CTE has to sit in the single top-level clause.

For a wider comparison of the three open-source engines, see MySQL vs PostgreSQL vs SQLite.

Common Pitfalls and Debugging Tips

Most CTE bugs come from recursion, and they trace back to two misunderstandings: what makes the recursion stop, and which member decides the shape of the rows. The four problems below cover both, plus one SQL Server syntax trap.

A Recursive CTE That Never Stops

The team query from earlier stops because the tree runs out of branches. Bad data can turn a tree into a loop. This update makes Ava report to Finn, so the chain Ben, Dan, Finn, Ava leads straight back to Ben:

UPDATE employees
SET manager_id = 6
WHERE id = 1;

The original team query now runs until something external stops it. A depth guard in the recursive member makes it safe to run, and the output shows the loop plainly:

WITH RECURSIVE team AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE name = 'Ben'
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees e
  JOIN team t ON e.manager_id = t.id
  WHERE t.depth < 4
)
SELECT name, depth
FROM team
ORDER BY depth, name;

name	depth
Ben	0
Dan	1
Erin	1
Finn	2
Ava	3
Ben	4
Chloe	4

Ben appearing again at depth 4 is the signature of a cycle. Without the guard, SQLite and PostgreSQL keep going until the query is cancelled, times out, or runs out of resources. MySQL stops at 1000 levels and SQL Server at 100 by default, each with an error.

There are three ways to fix it, depending on what the query needs:

Use UNION instead of UNION ALL when each row holds only identifying columns, such as an id and a name. UNION discards a row that matches an earlier row in every column, so the second visit to Ben produces nothing new and the recursion ends. This does not work in SQL Server, which requires UNION ALL.
Keep a depth guard such as WHERE t.depth < 20 set well above the real maximum depth. It stops a loop, although the rows it returns will still contain repeats.
Track the path and skip any row whose id is already on it, or use PostgreSQL's CYCLE clause, which does the same bookkeeping for you.

This is the UNION version, run against the same looping data. Every person appears once, and the query ends on its own:

WITH RECURSIVE team AS (
  SELECT id, name
  FROM employees
  WHERE name = 'Ben'
  UNION
  SELECT e.id, e.name
  FROM employees e
  JOIN team t ON e.manager_id = t.id
)
SELECT name FROM team ORDER BY name;

It returns all eight names, Ava through Hugo, because the loop now connects Ben to the whole company. UNION discards a row only when every column matches an earlier row, so this fix works here because each row holds just an id and a name.

Add a depth or path column and each repeat visit becomes a different row, which means UNION no longer stops the loop. For those queries, use the depth guard or check the path for ids already visited.

Forgetting the RECURSIVE Keyword

Remove RECURSIVE from the team query and SQLite still runs it and returns the same four rows, because SQLite does not enforce the keyword. That makes SQLite a poor judge of whether the query is portable.

MySQL's manual gives ERROR 1146 (42S02): Table 'cte_name' doesn't exist as the likely result, where cte_name is the CTE's name. PostgreSQL also treats the self-reference as an unknown table and reports that the relation does not exist.

The error names a table that the query appears to define two lines earlier, which is the clue. Add RECURSIVE once, directly after WITH, even when only one of several CTEs is recursive. SQL Server is the exception, since writing RECURSIVE there is a syntax error.

The Anchor Decides the Column Types

In the path query, the anchor row holds a short name such as "Ava", and the recursive member appends to it. The engines disagree about what that does to the column's type:

MySQL infers each column's type from the anchor alone. Without the CAST, the path column would take the width of name, and longer paths would be truncated or rejected with ERROR 1406 (22001): Data too long for column in strict mode.
SQL Server requires the anchor and recursive columns to have exactly the same data type, so it raises a type-mismatch error rather than truncating.
PostgreSQL also rejects a recursive column whose type differs from the anchor's, and its error hint suggests casting the anchor.
SQLite uses dynamic typing and accepts the query without any cast, which is why the missing cast is easy to overlook while developing there.

The fix follows the same pattern everywhere: cast the anchor column to a type wide enough for the longest value the recursion can build, and cast the recursive member to the same type. The path query earlier on this page does both, with CHAR(200) in place of VARCHAR(200) on MySQL.

The Missing Semicolon in SQL Server

In SQL Server, WITH is also used for table hints, so a CTE that follows another statement in the same batch needs that earlier statement to end with a semicolon. Without it, the batch fails with a syntax error near WITH:

-- T-SQL: end the previous statement
DECLARE @boss VARCHAR(20) = 'Ben';

WITH team AS (
  SELECT id, name
  FROM employees
  WHERE name = @boss
)
SELECT name FROM team;

Some codebases write ;WITH as a defensive habit. Ending every statement with a semicolon is the cleaner fix, and Microsoft already lists statements without a terminating semicolon as a deprecated feature.

Best Practices and Trade-offs

A CTE sits beside three other ways of naming an intermediate result. This table shows where each one fits:

Approach	Lasts For	Best For
Subquery	One use inside a statement	A short filter or a single lookup
CTE	One statement	Step-by-step queries and recursion
Temporary table	Usually the session	A result reused by several statements
View	Until dropped	A query many people and programs share

A few habits keep CTE queries correct and readable:

Name each CTE for what it holds, such as dept_stats or above_avg, so the main query reads like a sentence.
Give every recursive CTE a stop condition you can point to, and add a depth or cycle guard whenever the data could contain a loop.
Cast anchor columns that the recursion widens, even when SQLite accepts the query without the cast.
Treat a CTE as a way to organize a query, and use EXPLAIN before claiming it changed performance, especially on SQL Server, where naming a CTE does not guarantee that its result is cached for repeated references.

The SQL indexes and query optimization guide explains how to read that plan.

Choosing among the four comes down to how long the result must live. For a short result used once, a plain subquery is still the simpler choice. When a query has two or more steps that depend on each other, I would write CTEs, because each step gets a name and the query reads in the order the work happens.

When several statements need the same result, a temporary table fits better, because a CTE disappears when its statement ends. When many people and programs share the query, a view keeps one definition in one place.

The trade-off with a CTE is that readability is all it guarantees. On SQL Server, for example, a CTE referenced three times can run its query three times.

Conclusion

A CTE is the labeled working line on the accountant's worksheet: it names an intermediate result so the final query can refer to it plainly. Several CTEs can be chained, each building on the ones above it, and the whole structure disappears when the statement ends.

A recursive CTE is a worksheet line built from the line above it, which is what lets SQL walk a hierarchy of unknown depth or produce a run of dates. Learn the anchor, the recursive member, and the stop condition first. When a recursive query misbehaves, check the stop condition before anything else, then check the anchor's column types.

Frequently Asked Questions

What is the difference between a CTE and a subquery?