SQL筛选含特定key关联id行的最优方法及Vertica性能疑问

假设我们有一张结构如下的表：

(
  id int,
  value int,
  key int
)

需求是仅保留那些对应id中至少出现过指定key的行。例如当目标key为5时，原始表数据：

id value key
1  1     1
1  1     2
2  1     5
2  3     2
3  5     4
3  5     5

期望得到的结果是：

id value key
2  1     5
2  3     2
3  5     4
3  5     5

原因是id为1的行中没有key=5的记录，而id为2、3的行存在符合条件的记录。

目前已知两种实现方式，都需要先创建临时表tmp_table存储符合要求的id：

select [distinct] id from example_table where key=5

之后有两种处理方式：

1. 筛选查询：

select * from example_table where id in tmp_table

2. 关联查询：

select e.* from example_table e join tmp_table t on e.id = t.id

针对上述场景，有以下三个问题：

1. 这两种方式在性能上是否存在差异？包括通用情况及Vertica数据库中的表现。
2. 使用关联查询时必须添加distinct，否则数据会重复。那第一种筛选方式是否需要添加distinct？一方面distinct操作开销较高，另一方面如果没有哈希表优化，in子句应该更快。
3. 是否存在第三种实现该需求的方法？

1. 性能差异分析 #

通用数据库场景 #

多数现代数据库的查询优化器会把IN子查询和JOIN查询优化成相似的执行计划（比如哈希匹配或嵌套循环），性能差距不大。但如果tmp_table里的id数量极多，IN子句可能会因为列表过长出现解析或执行效率下降的情况；而JOIN在这种场景下通常更稳定。

Vertica数据库场景 #

Vertica作为列式MPP数据库，对JOIN的优化尤其是哈希join非常成熟，数据量较大时，JOIN的性能表现通常优于IN子句。因为Vertica的分布式架构下，JOIN可以更好地利用节点并行处理，而IN子句如果是子查询形式（而非临时表），可能会触发不同的执行路径，效率略低。如果已经预先创建了去重后的tmp_table，两者的差距会缩小，但JOIN依然更适配Vertica的并行处理模型。

2. 关于distinct的必要性 #

• 关联查询：必须加distinct，因为如果tmp_table里存在重复的id（比如同一个id多次出现key=5的记录），JOIN会将主表中对应id的每一行都和tmp_table里的重复id匹配，导致结果重复。
• IN筛选方式：不需要加distinct。因为IN子句会自动去重判断——只要id存在于列表中，无论出现多少次，都会被判定为符合条件，重复的id不会影响最终结果。而且去掉distinct能避免额外的排序或哈希去重开销，提升效率。即使没有哈希表优化，IN子句处理重复id时也不会多做无用功，所以放心去掉即可。

3. 第三种实现方法 #

当然有，推荐两种常用的：

方法一：使用EXISTS子查询 #

不需要创建临时表，直接在主查询里关联子查询判断存在性：

select * 
from example_table e
where exists (
    select 1 
    from example_table e2 
    where e2.id = e.id 
      and e2.key = 5
)

这种方式的优势是不需要额外维护临时表，查询优化器通常会把它优化成高效的半连接（Semi Join），只判断存在性而不产生重复数据，性能和JOIN/IN相当，甚至在部分场景下更优。

方法二：窗口函数 #

利用窗口函数统计每个id下是否出现过目标key，再筛选符合条件的行：

select id, value, key
from (
    select *,
           max(case when key = 5 then 1 else 0 end) over (partition by id) as has_target_key
    from example_table
) t
where has_target_key = 1

这种方式适合需要同时计算其他聚合信息的场景，一次扫描表就能完成判断，在大表扫描时可以减少IO次数，Vertica对窗口函数的并行支持也很好，性能表现不错。

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