如何在Snowflake中高效基于另一表查询的ID过滤大表？

首先是测试用临时表的创建语句：

create temporary table test_table (id number, col_a varchar);
insert into test_table values
    (1, 'a'), 
    (2, 'b'),
    (3, 'aa'),
    (4, 'a'),
    (6, 'bb'),
    (7, 'a'),
    (8, 'c');

create temporary table test_table_2 (id number, col varchar);
insert into test_table_2 values
    (1, 'aa'), 
    (2, 'bb'),
    (3, 'cc'),
    (4, 'dd'),
    (6, 'ee'),
    (7, 'ff'),
    (8, 'gg');

需求是：从test_table中筛选出col_a='a'的所有ID，再用这些ID过滤test_table_2的数据。

常见问题与错误写法 #

尝试用CTE配合IN子句时出现语法错误：

with cte as 
(
    select id from test_table
    where col_a = 'a'
)
select * from test_table_2 where id in cte;

错误信息：SQL compilation error: syntax error line 6 at position 39 unexpected 'cte'
原因是IN子句不能直接引用CTE名称，必须传入子查询结果。

当前可用但大表性能差的写法 #

使用JOIN可以实现需求，但大表场景下查询速度极慢：

with cte as 
(
    select id from test_table
    where col_a = 'a'
)
select t2.* from test_table_2 t2 join cte on t2.id=cte.id;

大表适配的高效实现方案 #

1. 修正IN子句写法并去重 #

如果test_table中col_a='a'的ID存在重复，先通过DISTINCT去重，减少后续匹配的行数：

select * 
from test_table_2 
where id in (
    select distinct id 
    from test_table 
    where col_a = 'a'
);

2. 建立合适的索引 #

索引是大表查询优化的核心，需确保以下索引存在：

• 给test_table创建col_a与id的联合索引：

  CREATE INDEX idx_test_table_cola_id ON test_table(col_a, id);
  

  此索引可让数据库快速定位col_a='a'的ID，避免全表扫描。
• 给test_table_2的id字段创建索引：

  CREATE INDEX idx_test_table2_id ON test_table_2(id);
  

  让数据库通过ID快速匹配test_table_2中的行，提升过滤效率。

3. 使用EXISTS子查询 #

EXISTS子查询的优势是找到匹配项后立即停止扫描，无需处理所有匹配行，在大表场景下性能优于JOIN或IN：

select t2.*
from test_table_2 t2
where exists (
    select 1 
    from test_table t1
    where t1.id = t2.id 
      and t1.col_a = 'a'
);

4. 物化筛选结果（重复查询场景） #

如果需要多次复用这个ID过滤条件，可以先将筛选出的ID存入带索引的临时表，再进行关联查询：

-- 创建存储过滤ID的临时表并去重
create temporary table filtered_ids as 
select distinct id from test_table where col_a = 'a';
-- 给临时表的ID字段建索引
create index idx_filtered_ids on filtered_ids(id);

-- 用物化后的ID表过滤test_table_2
select t2.* from test_table_2 t2 join filtered_ids fi on t2.id = fi.id;

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