If you're interested in this I would also recommend you check out Logica[0], which is a datalog-like language that is explicitly made to compile to SQL queries.
Click on the SQL tab.
> Among database theoreticians Datalog and SQL are known to be equivalent. And indeed the conversion from Datalog to SQL and back is often straightforward.
I was skeptical, and indeed the sample I linked to shows 8 lines of Datalog turning into 265 lines of SQL. In defense of SQL, I note WITH RECURSIVE wasn't used.
CREATE TABLE edges (
source INT,
target INT
);
INSERT INTO edges (source, target)
SELECT generate_series as source, generate_series + 1 as target
FROM generate_series(0, 999);
WITH RECURSIVE path_lengths AS (
-- Base case: Direct paths from edges
SELECT source, target, 1 AS distance
FROM edges
UNION ALL
-- Recursive step: Double the path length by joining on intermediate nodes
SELECT p.source, e.target, p.distance + 1 AS distance
FROM path_lengths AS p
JOIN edges AS e ON p.target = e.source
WHERE p.distance < 256 -- Control the recursion depth (2^8=256 for C8 equivalent)
)
-- Final query to select paths from source 0, similar to the logic program's final goal
, final_paths AS (
SELECT source, target, MIN(distance) AS distance
FROM path_lengths
WHERE source = 0
GROUP BY source, target
)
SELECT * FROM final_paths
ORDER BY source, target;
really readable, nice syntax imo