This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Graph Queries in SQL: Recursive CTEs, Adjacency Lists, and WITH RECURSIVE

Relational databases can model and query graph data effectively using recursive Common Table Expressions (CTEs). While not as optimized as dedicated graph databases, SQL-based graph queries handle many real-world use cases without adding infrastructure.

Modeling Graphs in SQL

The adjacency list model represents nodes and edges as separate tables:

CREATE TABLE nodes (

id BIGSERIAL PRIMARY KEY,

label TEXT NOT NULL,

properties JSONB DEFAULT '{}'

);

CREATE TABLE edges (

id BIGSERIAL PRIMARY KEY,

source_id BIGINT NOT NULL REFERENCES nodes(id),

target_id BIGINT NOT NULL REFERENCES nodes(id),

edge_type TEXT NOT NULL,

properties JSONB DEFAULT '{}',

created_at TIMESTAMPTZ DEFAULT NOW()

);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Indexes for graph traversal

CREATE INDEX idx_edges_source ON edges (source_id);

CREATE INDEX idx_edges_target ON edges (target_id);

CREATE INDEX idx_edges_type ON edges (edge_type);

For an organizational hierarchy, the "manager-employee" relationship:

INSERT INTO nodes (id, label) VALUES

(1, 'Alice CEO'),

(2, 'Bob CTO'),

(3, 'Carol CFO'),

(4, 'Dave Engineering Lead'),

(5, 'Eve Senior Engineer'),

(6, 'Frank Junior Engineer');

INSERT INTO edges (source_id, target_id, edge_type) VALUES

(1, 2, 'manages'),

(1, 3, 'manages'),

(2, 4, 'manages'),

(4, 5, 'manages'),

(5, 6, 'manages');

WITH RECURSIVE Basics

A recursive CTE has two parts: a base term and a recursive term, joined by UNION ALL:

WITH RECURSIVE org_chart AS (

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Base: find the CEO

SELECT id, label, 0 AS depth, ARRAY[id] AS path

FROM nodes

WHERE id = 1

UNION ALL

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Recursive: find direct reports

SELECT n.id, n.label, oc.depth + 1, oc.path || n.id

FROM nodes n

JOIN edges e ON e.target_id = n.id AND e.edge_type = 'manages'

JOIN org_chart oc ON oc.id = e.source_id

)

SELECT repeat(' ', depth) || label AS hierarchy

FROM org_chart

ORDER BY path;

Result:

Alice CEO

Bob CTO

Dave Engineering Lead

Eve Senior Engineer

Frank Junior Engineer

Carol CFO

Graph Traversal Patterns

Shortest Path (BFS)

WITH RECURSIVE bfs AS (

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Base: start node

SELECT id AS node_id, 0 AS distance, ARRAY[id] AS path

FROM nodes WHERE id = 1

UNION ALL

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Explore neighbors

SELECT e.target_id, bfs.distance + 1, bfs.path || e.target_id

FROM bfs

JOIN edges e ON e.source_id = bfs.node_id

WHERE NOT e.target_id = ANY(bfs.path) -- avoid cycles

AND bfs.distance < 10 -- max depth

)

SELECT * FROM bfs

WHERE node_id = 6 -- target node

LIMIT 1;

All Paths Between Two Nodes

WITH RECURSIVE paths AS (

SELECT e.source_id, e.target_id, ARRAY[e.source_id, e.target_id] AS path

FROM edges e

WHERE e.source_id = 1 AND e.target_id

---

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.