SQL
Temporary Tables vs CTEs vs Views vs Derived Tables
Four ways to name an intermediate result, and a decision rule for choosing between them based on scope, reuse and whether you need an index.
#CTE
Common table expressions and the WITH clause.
4 notes tagged
SQL
Hierarchical Data and Recursive Queries
Storing and querying trees: the adjacency list, walking it with a recursive CTE, and the materialised path and closure table alternatives.
SQL
Recursive CTEs
Query a hierarchy of unknown depth, generate a series of rows, or follow a chain of references - all with one self referencing CTE.
SQL
WITH: Single and Multiple CTEs
A CTE names a query so the rest of the statement can read it like a table. Learn single and chained CTEs, and when a CTE beats a subquery or a view.