Redshift用WITH+JOIN替代WHERE IN的写法及性能问题咨询

你原有SQL的错误在于CTE定义不需要使用INSERT语句，直接在AS后接VALUES子句即可构造多行常量集合，正确写法如下：

WITH temp_table(id) AS (
  VALUES 
    ('a'),
    ('b'),
    ('c')
)
SELECT * 
FROM real_data rd
INNER JOIN temp_table tt 
  ON rd.id = tt.id;

如果你的常量值数量较多，还可以选择提前创建真实的临时表并指定分布键为关联字段id，能获得比CTE更好的性能：

CREATE TEMP TABLE temp_table (id VARCHAR)
DISTKEY(id);

INSERT INTO temp_table VALUES
('a'),
('b'),
('c');

SELECT * 
FROM real_data rd
INNER JOIN temp_table tt 
  ON rd.id = tt.id;

• 匹配值数量为5、10时：性能完全足够，优化器会直接将IN子句展开为多个等值判断，和临时表关联查询的性能几乎没有差异，这种场景下WHERE IN写法更简洁，不需要特意换用关联写法。
• 匹配值数量为100时：绝大多数场景性能依然足够，只有当real_data表体量达到TB级、行数超十亿，且关联字段id不是表的分布键/排序键时，才会出现可感知的性能差异，这时候换用临时表关联的写法一般能获得10%~30%的性能提升。

当匹配值数量超过1000时，Redshift会触发IN子句的长度限制，此时必须使用临时表/CTE关联的方式实现匹配。

内容的提问来源于stack exchange，提问作者Hunor Kovács