如何筛选满足另一表任意规则的numbers表行？PostgreSQL 15.1

我有一张numbers表：

还有一张rules表：

规则说明：

• LT：小于
• GT：大于
• EQ：等于

需求：从numbers表中选取所有满足rules表中任意一条规则的id，预期结果：

使用环境：PostgreSQL 15.1版本

推荐使用EXISTS子查询实现，性能更优，逻辑清晰：

SELECT DISTINCT n.id
FROM numbers n
WHERE EXISTS (
    SELECT 1
    FROM rules r
    WHERE 
        (r.rule_type = 'LT' AND n.n < r.n)
        OR (r.rule_type = 'GT' AND n.n > r.n)
        OR (r.rule_type = 'EQ' AND n.n = r.n)
);

逻辑解释 #

1. EXISTS子查询会逐行检查numbers表的记录，只要匹配rules表中任意一条规则就保留该行
2. 针对不同规则类型设置对应比较逻辑：
   • LT规则：判断numbers.n小于rules.n
   • GT规则：判断numbers.n大于rules.n
   • EQ规则：判断numbers.n等于rules.n
3. DISTINCT用于避免同一id因满足多条规则而重复输出（比如id=2同时符合LT和EQ规则）

备选方案（笛卡尔积过滤） #

如果数据量不大，也可以用CROSS JOIN结合条件过滤，最后去重：

SELECT DISTINCT n.id
FROM numbers n
CROSS JOIN rules r
WHERE 
    (r.rule_type = 'LT' AND n.n < r.n)
    OR (r.rule_type = 'GT' AND n.n > r.n)
    OR (r.rule_type = 'EQ' AND n.n = r.n);

这种方式会先生成两表的笛卡尔积，再筛选符合条件的记录，数据量大时效率不如EXISTS方案。

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