Last Updated: 3/19/2026

Recursive Queries

Feldera supports recursive SQL queries through recursive CTEs (Common Table Expressions) and handles them incrementally. Recursive queries are useful for graph traversal, hierarchical data, and iterative computations.

Recursive CTEs

Define recursive queries using WITH RECURSIVE:


WITH RECURSIVE reachable(node) AS (
    -- Base case: start nodes
    SELECT source FROM edges WHERE source = 1
    
    UNION
    
    -- Recursive case: follow edges
    SELECT e.target
    FROM edges e
    JOIN reachable r ON e.source = r.node
)
SELECT * FROM reachable;

LATENESS Annotations

For incremental recursive queries, declare LATENESS on recursive columns:


CREATE TABLE edges (
    source INTEGER,
    target INTEGER,
    distance INTEGER LATENESS 10
);
 
WITH RECURSIVE shortest_paths(node, distance) AS (
    SELECT source, 0 FROM edges WHERE source = 1
    
    UNION
    
    SELECT e.target, r.distance + e.distance
    FROM edges e
    JOIN shortest_paths r ON e.source = r.node
)
SELECT * FROM shortest_paths;

The LATENESS attribute bounds how much values can decrease in each iteration, enabling efficient incremental computation.

Graph Traversal

Find all reachable nodes in a graph:


CREATE TABLE graph (
    from_node INTEGER,
    to_node INTEGER
);
 
WITH RECURSIVE reachable AS (
    SELECT from_node as node FROM graph WHERE from_node = 1
    UNION
    SELECT g.to_node
    FROM graph g
    JOIN reachable r ON g.from_node = r.node
)
SELECT DISTINCT node FROM reachable;

Hierarchical Queries

Process hierarchical data like organizational charts:


CREATE TABLE employees (
    id INTEGER,
    name VARCHAR,
    manager_id INTEGER
);
 
WITH RECURSIVE hierarchy AS (
    -- CEO (no manager)
    SELECT id, name, 0 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION
    
    -- Employees under each manager
    SELECT e.id, e.name, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy ORDER BY level, name;

Incremental Computation

Feldera evaluates recursive queries incrementally. When input data changes, only affected parts of the recursion are recomputed:


-- Add a new edge
INSERT INTO graph VALUES (5, 6);
-- Only paths involving nodes 5 and 6 are recomputed
 
-- Remove an edge
DELETE FROM graph WHERE from_node = 2 AND to_node = 3;
-- Only paths through this edge are recomputed

Limitations

Recursive queries must be monotonic (results can only grow, not shrink) unless LATENESS is specified
Infinite recursion is not supported
Negation in recursive queries requires careful handling

What’s Next

Sql Data Types: Understand LATENESS attributes
Materialized Views: Use recursive queries in views
Time Series Sql: Combine recursion with time-based queries