如何用SQL排除存在跨列引用关联的行？

需求回顾 #

现有行程表travel_history，结构及示例数据如下：

需要排除两类行：

• REF不为空且其值是表中某行的SOL_ID
• REF为空，但自身SOL_ID被其他行的REF引用

最终需保留：

• REF为空且自身SOL_ID未被任何行REF引用的行
• REF的值不存在于表中任何行SOL_ID的行

原代码问题分析 #

你之前尝试的自连接写法存在两处核心问题：

1. JOIN travel_history th2 on th1."SOL_ID" = th2."SOL_ID" 是将行与自身匹配，无法实现跨行的引用判断
2. NOT IN 子句使用错误，直接引用单字段th2."REF"或th2."SOL_ID"不符合语法，需用子查询形式

正确SQL实现 #

使用NOT EXISTS子查询来精准判断排除条件，代码如下：

SELECT *
FROM travel_history th
WHERE
    -- 条件1：当前行的REF值不存在于任何行的SOL_ID中
    NOT EXISTS (
        SELECT 1
        FROM travel_history
        WHERE SOL_ID = th.REF
    )
    AND
    -- 条件2：当前行的SOL_ID未被任何行的REF引用
    NOT EXISTS (
        SELECT 1
        FROM travel_history
        WHERE REF = th.SOL_ID
    );

代码说明 #

1. 第一个NOT EXISTS：检查当前行的REF是否是表中某行的SOL_ID，如果是则排除该行
2. 第二个NOT EXISTS：检查当前行的SOL_ID是否被其他行的REF引用，如果被引用则排除该行
3. 两个条件同时满足时，该行即为需要保留的行

验证结果 #

代入示例数据后，查询结果将返回：

完全符合需求预期。

内容的提问来源于stack exchange，提问作者L Z B