I was helping on this question. There you have a table border to indicate countryA and countryB have a common border. And want to know what country you can reach starting from country S Sweden.I did my recursion and works ok. But I feel I cheat a little bit. Taking advantage the number of countries are limited put a break on 300. But in a different problem where I don't know the upper limit won't be able to do the same.How I optimize my query so recursion doesn't travel the same path again?SQL Fiddle DemoWITH RECURSIVE travel(r_level, country) AS (    select distinct 1 as r_level,                    CASE WHEN country1 = 'S' THEN country2                         ELSE country1                              END as country    from borders     where country1 = 'S'        or country2 = 'S'        UNION     select distinct t.r_level + 1 as r_level,                    CASE WHEN b.country1 = t.country THEN b.country2                         ELSE b.country1                    END as country               from borders b    join travel  t      ON (b.country1 = t.country OR b.country2 = t.country)     AND (b.country1 <> 'S' AND b.country2 <> 'S')    WHERE t.r_level < 300)SELECT DISTINCT countryFROM travelI can't use subquery based on the same recursive table:AND (    b.country1 NOT IN (SELECT country FROM Travel)     AND b.country2 NOT IN (SELECT country FROM Travel)    )
Find all the countries I can visit by just crossing direct borders
sql;recursion;graph;postgresql