Recursive CTE for graph traversal in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Recursive CTE in SQL?

A Recursive CTE (Common Table Expression) is a SQL query that refers to itself to repeatedly execute and build a result set. It is useful for tasks like traversing hierarchical or graph data.

intermediate

How does a Recursive CTE work for graph traversal?

It starts with a base query selecting initial nodes, then repeatedly joins the CTE to the graph edges to find connected nodes until no new nodes are found.

beginner

What are the two main parts of a Recursive CTE?

1. Anchor member: the base query that runs first.<br>2. Recursive member: the query that references the CTE itself to find connected rows.

intermediate

Why is it important to have a termination condition in a Recursive CTE?

Without a termination condition, the recursion would run forever. The query stops when no new rows are added in the recursive step.

intermediate

Give a simple example of a Recursive CTE to find all reachable nodes from a start node in a graph.

WITH RECURSIVE reachable_nodes AS ( SELECT id FROM nodes WHERE id = 1 -- anchor member UNION SELECT e.target_id FROM edges e JOIN reachable_nodes r ON e.source_id = r.id -- recursive member ) SELECT * FROM reachable_nodes;

What does the anchor member of a Recursive CTE do?

A Defines the starting point of recursion

B Terminates the recursion

C Joins tables without recursion

D Deletes rows from the table

The anchor member defines the initial rows from which recursion begins.

In graph traversal using Recursive CTE, what does the recursive member typically do?

A Selects unrelated rows

B Joins the CTE to graph edges to find connected nodes

C Deletes visited nodes

D Creates new tables

The recursive member joins the CTE to edges to find nodes connected to already found nodes.

What happens if a Recursive CTE has no termination condition?

A It runs forever causing an error or timeout

B It returns an empty result

C It runs once and stops

D It automatically optimizes

Without termination, recursion never stops, causing infinite loops or errors.

Which keyword is used to define a Recursive CTE in PostgreSQL?

A WITH LOOP

B RECURSIVE SELECT

C WITH RECURSIVE

D CTE RECURSIVE

PostgreSQL uses 'WITH RECURSIVE' to start a recursive CTE.

What kind of data is Recursive CTE especially useful for?

A Unrelated random data

B Flat tables without relationships

C Single row tables

D Hierarchical or graph data

Recursive CTEs are designed to handle hierarchical or graph-like data structures.

Explain how a Recursive CTE can be used to traverse a graph in SQL.

Think about starting points and how to find connected nodes repeatedly.

Describe the importance of the termination condition in a Recursive CTE.

What happens if recursion never stops?