如何用IN与AND实现SQL整体条件过滤而非行级过滤

问题背景 #

现有数据表如下：

我需要基于一组ids筛选记录，但要求指定的所有ID对应的记录必须全部满足isSearchable = true，才返回这些记录；只要有一个ID的isSearchable为false，就返回空结果。

当前执行的SQL：

SELECT *
FROM my_table
WHERE id IN (1, 3)
  AND isSearchable = true

该查询仅返回ID=1的记录，不符合预期——我希望此时返回无结果，因为ID=3的isSearchable为false。

解决方案 #

以下几种方法可实现需求：

方法1：用NOT EXISTS校验全量ID

SELECT t.*
FROM my_table t
WHERE t.id IN (1, 3)
AND NOT EXISTS (
    SELECT 1
    FROM my_table
    WHERE id IN (1, 3)
    AND isSearchable != true
)

逻辑：先确认指定ID集合中不存在isSearchable为false的记录，才返回所有符合ID条件的行；若有任意一个ID不满足，查询直接返回空。

方法2：聚合后用HAVING统一验证

SELECT t.*
FROM my_table t
WHERE t.id IN (1, 3)
GROUP BY t.id, t.name, t.isSearchable
HAVING COUNT(CASE WHEN isSearchable != true THEN 1 END) = 0
AND COUNT(DISTINCT t.id) = 2 -- 这里的数值要和指定的ID数量一致，示例中是2个ID

逻辑：先聚合指定ID的记录，检查其中无isSearchable为false的行，同时确认获取到的ID数量与指定数量匹配（避免部分ID不存在的情况），满足条件才返回结果。

方法3：窗口函数全局判断

SELECT id, name, isSearchable
FROM (
    SELECT *,
           MAX(CASE WHEN isSearchable != true THEN 1 ELSE 0 END) OVER () AS has_unsearchable
    FROM my_table
    WHERE id IN (1, 3)
) sub
WHERE has_unsearchable = 0

逻辑：子查询中用窗口函数统计指定ID集合内是否存在不符合条件的记录，外层仅返回无不符合记录的结果。

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