DataTerminology.comNEW
Also known as: CTE, WITH Query
A named, temporary result set defined with WITH that can be referenced within a single SQL statement.
DESCRIPTION
Common table expressions improve SQL readability and modularity by factoring complex logic into named subqueries. They exist for the duration of a single statement and can be referenced multiple times. CTEs support both non-recursive patterns and recursive queries for hierarchical traversal. They ease maintenance, testing, and optimization by isolating steps. Depending on the engine, they may be inlined or materialized during execution. While expressive, overuse can hinder performance if the same CTE recomputes frequently without optimization. Use judiciously alongside views and temp tables.
ALTERNATIVE DEFINITIONS
Microsoft Learn
Specifies a temporary named result set, known as a common table expression (CTE).
Concise definition emphasizing the temporary, named result set.
PostgreSQL Documentation
A WITH query is a way to write auxiliary statements for use in a larger query.
Emphasizes auxiliary statements and composition of larger queries.
RELATED TERMS
Recursive CTE Derived Table View Window Function Query Optimization Query Planning SQL Modularity Refactoring
COMPETING TERMS
KNOWLEDGE AREAS
Programming Languages Data Engineering Data Warehousing & BI
TARGET AUDIENCE
Data Engineer Data Analyst BI Specialist Data Modeler Software Developer
EXTERNAL SOURCES
- Wikipedia - Hierarchical and recursive queries in SQL
- Microsoft - CTE syntax and examples
- PostgreSQL - WITH queries and recursion
TOP QUESTIONS
Why use a Common Table Expression instead of a subquery?
CTEs improve readability, allow multiple references to the same logic, and support recursion. Subqueries can be terse but may be harder to maintain.
Category: Alternatives
Are Common Table Expressions materialized?
It depends on the database. Some inline CTEs during optimization, while others may materialize them based on heuristics or hints.
Category: Misunderstanding
What are common mistakes with CTEs?
Chaining too many CTEs can degrade performance. Reusing a heavy CTE without materialization can force repeated computation.
Category: Mistakes
Can CTEs be used for updates and deletes?
Yes, many platforms allow CTEs to feed UPDATE, DELETE, and INSERT statements, improving clarity of complex mutations.
Category: Implementation
How do CTEs aid query optimization?
They modularize logic, allowing optimizers to reason about intermediate steps and sometimes enable predicate pushdown or reuse.
Category: Performance
When was the CTE concept standardized?
CTEs were introduced in SQL:1999, with recursive queries formalized for hierarchical operations.
Category: Origin
What’s a hot topic around CTEs?
Optimizers are improving to avoid redundant CTE recomputation and support query hints for materialization strategies.
Category: Topic
How do CTEs compare to views?
CTEs are scoped to a single statement, while views are reusable schema objects with security, dependencies, and discoverability.
Category: Alternatives
Where should I start learning about CTEs?
Study vendor documentation and practice refactoring a complex query into readable, testable CTE blocks.
Category: Learning