Recursive CTE - Graph Traversal
Recursive CTE - Graph Traversal is a SQL statement in the CTE (WITH) category. Recursive CTE for traversing graph structures (friends of friends, network paths, bus routes). The syntax is WITH RECURSIVE graph AS (...) SELECT * FROM graph;. It returns graph traversal result. A typical example: WITH RECURSIVE friends_network AS ( SELECT user_id, 0 AS depth, ARRAY[user_id] AS path FROM users WHERE user_id = 1 UNION ALL SELECT f.friend_id, fn.depth + 1, fn.path || f.friend_id FROM friendships f JOIN friends_network fn ON f.user_id = fn.user_id WHERE NOT f.friend_id = ANY(fn.path) -- Avoid cycles ) SELECT DISTINCT user_id, depth FROM friends_network ORDER BY depth; -- Finds all friends up to any depth (social network graph) A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures).