Recursive join¶
Repeatedly join relational query results to a relation to derive further rows from seeds, with duplicate and stopping rules determining whether a completed result is reached.
Core Idea¶
A recursive join is a relational query pattern that starts from seed rows and repeatedly joins rows derived at one step to another relation to derive further rows. It can expand an assembly into subparts or follow an employee reporting chain. A recursive calculation without such a join is not this narrower pattern. Whether the expression returns a completed result depends on the row shape, duplicate rule, data cycles, and stopping condition.[ref-9dcd00db75ae][ref-2894a8297a0b]
Scope of Application¶
PostgreSQL documents a product-parts query that joins previously included parts to a parts relation and multiplies quantities. Microsoft documents an employee-hierarchy query that joins prior DirectReports rows to an employee table. The two differ in data and output, but both have anchor rows, recursive working rows, a joined relation, and derived results. Their syntax and implementation details belong to the respective database systems.[ref-9dcd00db75ae][ref-2894a8297a0b]
Clarity¶
Ask separately whether a query contains a recursive join and whether it finishes with the intended answer. UNION removes duplicate complete rows in PostgreSQL, whereas UNION ALL keeps them. If output includes changing depth or path values, even UNION may fail to suppress cyclic revisits. A depth limit can stop a query without computing full reachability.[^ref-9dcd00db75ae]
Manages Complexity¶
Unroll the operation as a small repeated state transition: establish anchors, join current working rows to the relation, project new rows, apply duplicate rules, and check for an empty next step or another declared stop. This reveals whether quantities or paths are meaningful and where a cycle could cause an unbounded result. There is no universal rule that every recursive join deduplicates or reaches a fixed point.[ref-9dcd00db75ae][ref-2894a8297a0b]
Abstract Reasoning¶
To assess a query, identify its anchor and recursive term, then trace one row through the join predicate. If a relation contains a cycle, ask whether the projected whole row repeats or changes. If it changes, a visited-key, path, depth guard, or explicit cycle rule may be needed for a completed answer. Check whether the requested result is full closure, bounded traversal, or path/quantity enumeration before choosing the stopping and duplicate semantics.[^ref-9dcd00db75ae]
Knowledge Transfer¶
The same role map transfers between parts expansion and employee hierarchy inside relational-query work. Graph traversal outside a relational system may be analogous but is not literally a recursive join without a relation operand and join operation. The typed parent Relation is an internal constituent of every such join; the full database-specific operation is not itself a new Prime.[ref-9dcd00db75ae][ref-2894a8297a0b]
Example¶
PostgreSQL's included_parts query anchors parts directly included in our_product. Its recursive term joins prior included_parts rows to parts where a prior subpart is the parent part, multiplies quantities, and finally sums totals by subpart. The anchors start the traversal, the working rows carry prior results, parts supplies the joined relation, and quantity is part of the derived row. Because this example uses UNION ALL and quantity-bearing rows, it should not be read as an automatic unique-node closure or a universal termination rule.[^ref-9dcd00db75ae]
Relationships to Other Abstractions¶
Current abstraction Recursive join Domain-specific
Parents (1) — more general patterns this builds on
-
Recursive join is part of Relation Prime
A recursive join contains a relation as the operand repeatedly matched against recursively derived rows.
Hierarchy path (1) — routes to 1 parentless root
- Recursive join → Relation
Neighborhood in Abstraction Space¶
Recursive join sits in a sparse region of the domain-specific corpus (99th percentile for distinctiveness): few abstractions share its structure, so a faithful description tends to retrieve it precisely.
Family — Unclustered & Miscellaneous (2551 abstractions)
Nearest neighbors
- Relational Model — 0.76
- Tree Sort — 0.75
- Postings List — 0.75
- L-Attributed Grammar — 0.75
- Query Optimization — 0.74
Computed from structural-signature embeddings · 2026-10-08
Not to Be Confused With¶
An ordinary join runs once rather than feeding derived rows into another join. A general recursive CTE may compute arithmetic without a relational join. Transitive closure is a possible completed reachability result, not the identity or guaranteed outcome of every recursive-join expression. Query rewriting transforms an expression into an equivalent form; recursive join evaluates a repeated relational derivation. The approved strict DAG edge is composition/part_of to Relation, with the relation inside the query; live Recursion, Iteration, and Fixed Point are not asserted as all-instance parents because unsafe cyclic queries may fail their stopping or convergence conditions.[ref-9dcd00db75ae][ref-2894a8297a0b]
References¶
[^ref-9dcd00db75ae]: PostgreSQL Global Development Group. PostgreSQL 18 Documentation, 7.8 WITH Queries (Common Table Expressions). §§7.8.2 and 7.8.2.2. Anchor/recursive-term evaluation; UNION versus UNION ALL; product-parts example; cycle-detection example.
[^ref-2894a8297a0b]: Microsoft. Recursive queries using common table expressions (Transact-SQL). Last updated 2026-09-21. Structure, Pseudocode and Semantics, and employee-hierarchy example with code walkthrough.