PostgreSQL 13递归CTE中如何条件性使用INNER JOIN？

问题原因 #

你当前的SQL语句中，两个INNER JOIN是逻辑与的关系，只有同时满足bt.property = test_table.property和bt2.id = test_table.innerProperty的行才会被保留。而你的数据里没有同时符合这两个条件的行，所以返回0行。

直接用OR连接条件导致无限循环，通常是因为递归CTE缺少明确的终止条件，查询会不断匹配重复行，无法停止递归。

解决方案 #

要实现“第一个连接无结果时用第二个”的逻辑，同时避免无限循环，可以通过以下两种方式处理：

方案1：LEFT JOIN + 递归终止条件 #

用LEFT JOIN同时关联两张表，筛选出至少满足一个连接的行，同时在递归部分添加明确的终止条件（比如层级限制、排除已处理行）：

WITH RECURSIVE test_cte AS (
    -- 初始查询：获取满足任一连接条件的行
    SELECT 
        t.stuff,
        t.property,
        t.innerProperty,
        1 AS recursion_level -- 标记递归层级，用于终止
    FROM test_table t
    LEFT JOIN base_table bt ON bt.property = t.property
    LEFT JOIN base_table bt2 ON bt2.id = t.innerProperty
    WHERE bt.property IS NOT NULL OR bt2.id IS NOT NULL

    UNION ALL

    -- 递归部分：根据业务逻辑关联下一级数据，同时添加终止条件
    SELECT 
        t2.stuff,
        t2.property,
        t2.innerProperty,
        c.recursion_level + 1 AS recursion_level
    FROM test_cte c
    -- 替换成你的递归关联逻辑，比如从当前行关联子节点
    JOIN test_table t2 ON t2.parent_id = c.id -- 示例关联条件，需根据实际表结构调整
    LEFT JOIN base_table bt ON bt.property = t2.property
    LEFT JOIN base_table bt2 ON bt2.id = t2.innerProperty
    WHERE (bt.property IS NOT NULL OR bt2.id IS NOT NULL)
      AND c.recursion_level < 10 -- 限制最大递归层数，避免无限循环
      AND NOT EXISTS ( -- 排除已处理过的行，避免重复递归
          SELECT 1 FROM test_cte c2 WHERE c2.stuff = t2.stuff
      )
)
-- 最终查询，可根据需求去重或筛选
SELECT DISTINCT stuff FROM test_cte;

方案2：UNION ALL拆分两种连接场景 #

将满足第一个连接的行和仅满足第二个连接的行用UNION ALL分开，再处理递归，同样添加终止条件：

WITH RECURSIVE test_cte AS (
    -- 场景1：满足第一个INNER JOIN的行
    SELECT 
        t.stuff,
        t.property,
        t.innerProperty,
        1 AS recursion_level
    FROM test_table t
    INNER JOIN base_table bt ON bt.property = t.property

    UNION ALL

    -- 场景2：仅满足第二个INNER JOIN的行（排除已在场景1中的行）
    SELECT 
        t.stuff,
        t.property,
        t.innerProperty,
        1 AS recursion_level
    FROM test_table t
    INNER JOIN base_table bt2 ON bt2.id = t.innerProperty
    WHERE NOT EXISTS (
        SELECT 1 FROM base_table bt WHERE bt.property = t.property
    )

    UNION ALL

    -- 递归部分：关联下一级数据，添加终止条件
    SELECT 
        t2.stuff,
        t2.property,
        t2.innerProperty,
        c.recursion_level + 1 AS recursion_level
    FROM test_cte c
    JOIN test_table t2 ON t2.parent_id = c.id -- 替换为实际递归关联条件
    LEFT JOIN base_table bt ON bt.property = t2.property
    LEFT JOIN base_table bt2 ON bt2.id = t2.innerProperty
    WHERE (bt.property IS NOT NULL OR bt2.id IS NOT NULL)
      AND c.recursion_level < 10
)
SELECT DISTINCT stuff FROM test_cte;

关键注意事项 #

• 递归CTE必须包含初始查询和递归部分，两者通过UNION ALL连接。
• 递归部分必须添加明确的终止条件，比如限制递归层数、排除已处理的行，否则极易出现无限循环。
• 若不需要递归逻辑，直接去掉RECURSIVE关键字，按上述方案处理连接即可。

内容的提问来源于stack exchange，提问作者RinnieR