Amey-Thakur Common table expressions
A CTE gives a subquery a name and lifts it out of the nest, turning a query you read inside-out into one you read top-down. The main risk is treating them as free abstraction when the engine may or may not inline them.
Method
- Name each step for what it produces. A CTE called filtered_orders documents the query better than a comment, while one called t1 wastes the mechanism entirely.
- Build in stages that each make sense alone. Each CTE should be selectable on its own for debugging, which is the practical benefit over a nested subquery.
- Keep the chain shallow. More than four or five stacked CTEs usually means the query is doing several jobs and should become a view, a materialised table, or separate queries.
- Know whether your engine inlines or materialises. Some optimise through the CTE and some evaluate it once as a barrier, which changes performance dramatically and sometimes helps (see query-plan-reading).
- Do not repeat an expensive CTE. Referencing the same CTE several times may recompute it per reference depending on the engine, so check the plan before assuming reuse is free.
- Reach for recursion only when the data is hierarchical. Recursive CTEs walk trees and graphs, which is a different tool from staging (see recursive-queries).
Boundaries
- CTEs improve readability; they do not by themselves improve performance and can hinder it by blocking optimisation.
- A CTE used by many queries wants to be a view or a table instead.
- Deeply chained CTEs can be harder to debug than the nesting they replaced if each stage is not independently meaningful.