Common table expression

As explained in 100 SQL Server Mistakes and How to Avoid Them

A common table expression is a named temporary result set defined within a statement that can be referenced by the statement and, in recursive form, by its own definition.

A common table expression (CTE) is a named temporary result set referenced within a query. A recursive CTE can reference itself, making hierarchical queries practical without cursors. section 3.3

Build a recursive hierarchy query from two parts:

• Start with an anchor query, such as Sanjay Gupta’s employee record. section 3.3
• Use UNION ALL to join employees to the CTE’s previous results through EmployeeID and ManagerID. section 3.3
• Query the completed CTE and join back to Employees when manager details are needed. section 3.3

Reporting all direct and indirect employees

EmployeeCTE begins with EmployeeID 2, repeatedly finds employees whose ManagerID matches a prior result, and returns the resulting hierarchy. Guard recursive queries: an error can create an infinite loop; recursion defaults to 100 levels and can be overridden with MAXRECURSION. section 3.3

Figure 3.2 MagicChoc management org chart

Use the organizational chart to visualize the manager-child relationships traversed by the recursive query. section 3.3

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains anchor and recursive queries, joins, recursion limits, and MAXRECURSION, but not execution internals.

Examples

Uses employee-manager records to find reports, ancestors, hierarchy paths, and organizational relationships.

What it assumes

Assumes employee rows contain EmployeeID and ManagerID relationships and introduces recursion through query examples.

What you can do after

Build it from scratch with recursive T-SQL queries for hierarchical employee data.

How it connects

Connects recursion, hierarchies, UNION ALL, cursors, and HIERARCHYID.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

• 

  liveBookmanning.com
  DuckDB in ActionA common table expression is a named temporary query result defined within a SQL statement.
• 

  liveBookmanning.com
  Just Use Postgres!A common table expression is a named temporary query result defined within a statement and available to a subsequent primary query in that statement.
• 

  liveBookmanning.com
  AI for Everyday ITA common table expression is a named temporary query result defined within a SQL statement and referenced by that statement.
• 

  liveBookmanning.com
  PostgreSQL Mistakes and How to Avoid ThemA common table expression is a named temporary query result defined with SQL WITH syntax and available within a larger statement.
• 

  liveBookmanning.com
  The Well-Grounded Python Developer: How the pros use Python and FlaskA common table expression is a named temporary result set defined within a SQL statement and usable by that statement's subsequent query clauses.

Compare how 6 books explain common table expression →

Related concepts

• SQLuses · SQL is a standardized declarative language for defining, querying, and manipulating data and structures in relational database systems.
• with clauseproduced by · A WITH clause is a query-language construct that defines intermediate results or context for use by subsequent operations in the same statement.
• recursionuses · Recursion is a computational technique in which a definition or procedure refers to or invokes the same definition or procedure, directly or indirectly.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.