CTEs and Recursion
SQL: lesson 12 of 15
Name a result, then build the next one on top of it.
Lesson 12 of 15 · 6 min
CTEs and Recursion
Step 1 of 9
Five gates, each naming the one it feeds from. The chain is in the data, not in the schema.
The Idea
A common table expression names a query so the next one can use it. Add RECURSIVE and the CTE may refer to itself: an anchor row set, then a step that joins the previous pass back onto the table, repeated until a pass returns nothing.
Real-World Example
Tracing a waterway. You start at the spring, ask which channel it feeds, then ask that one the same question, and keep going until you reach the sea. Each answer is the next question's input.
The Code
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""CREATE TABLE gate (name TEXT, feeds TEXT);
INSERT INTO gate VALUES ('spring',NULL),('mill','spring'),
('town','mill'),('harbour','town'),('quarry',NULL);""")
print(db.execute("""
WITH RECURSIVE downstream(name, hops) AS (
SELECT name, 0 FROM gate WHERE name = 'spring' -- anchor
UNION ALL
SELECT g.name, d.hops + 1 -- one step further
FROM gate g JOIN downstream d ON g.feeds = d.name)
SELECT name, hops FROM downstream ORDER BY hops""").fetchall())
# [('spring', 0), ('mill', 1), ('town', 2), ('harbour', 3)]Your turn
Fill in the blank.
WITH RECURSIVE n(i) AS (
SELECT 1
___ ALL
SELECT i + 1 FROM n WHERE i < 4)
SELECT group_concat(i) FROM n; -- 1,2,3,4Mini quiz
1 / 3