2 ms·
> Is cross-joining a CTE to itself realistic? Yes! Technically you have to say `WITH RECURSIVE ...` first, but yes, it's a very common thing to do to, for exa
by cryptonector 3y ago
> Is cross-joining a CTE to itself realistic?
Yes! Technically you have to say `WITH RECURSIVE ...` first, but yes, it's a very common thing to do to, for example, compute the transitive closure of nested groups. Just remember to use `UNION` not `UNION ALL`! (Otherwise you will get an infinite loop if there's a circular group.)
WITH RECURSIVE closure AS (
// Seed the closure
SELECT parent AS parent, child AS child
FROM groups
// Repeat the right side of the UNION
// until no new rows are added
UNION
// Recursively join the closure to the
// groups
SELECT g.parent, c.child
FROM groups g
JOIN closure c ON g.child = c.parent
);