Snowflake中On子句含Between条件时Inner Join比Left Join更快的原因？

你在Snowflake中使用BETWEEN作为JOIN关联条件时，发现INNER JOIN的执行速度远快于LEFT JOIN（示例查询中快2倍，大数据集下差异更明显），以下是具体原因分析和解决办法：

示例查询 #

WITH 
orders AS (
    SELECT * FROM (VALUES
        (1, '2024-11-01'),
        (2, '2024-11-05'),
        (3, '2024-11-10')
    ) AS o(order_id, order_date)
),
promotions AS (
    SELECT * FROM (VALUES
        ('A', '2024-11-01', '2024-11-03'),
        ('B', '2024-11-04', '2024-11-06'),
        ('C', '2024-11-08', '2024-11-12')
    ) AS p(promo_id, start_date, end_date)
)

SELECT 
    o.order_id,
    o.order_date,
    p.promo_id,
    p.start_date,
    p.end_date
FROM 
    orders o
JOIN 
    promotions p
ON 
    o.order_date BETWEEN p.start_date AND p.end_date;

一、性能差异的核心原因 #

• 结果集与过滤逻辑差异
  INNER JOIN会在关联阶段直接过滤掉不满足BETWEEN条件的记录，最终仅保留两边匹配的数据，后续处理的数据量更小；而LEFT JOIN必须强制保留左表（orders）的所有记录，即使右表无匹配项，这意味着Snowflake要先处理左表全量数据，再逐一完成范围匹配，数据处理量远大于INNER JOIN。
• 优化器执行策略限制
  针对INNER JOIN的范围关联，Snowflake优化器可以选择更高效的路径：比如对右表的日期范围做分区裁剪、利用聚类键或搜索优化服务快速定位匹配数据，甚至预聚合右表范围条件减少匹配次数；但LEFT JOIN要求保留左表全部行，优化器无法提前裁剪左表数据，关联阶段的计算量会显著增加，大数据集下差异被进一步放大。
• 数据倾斜的影响放大
  如果左表存在日期分布不均（如某时段订单量极大），LEFT JOIN会强制处理这些热点数据；而INNER JOIN可能通过右表的范围过滤自动避开部分热点，降低计算压力。

二、优化LEFT JOIN性能的可行方案 #

1. 给右表设置聚类键
   对promotions表按start_date和end_date设置聚类键，让Snowflake在关联时快速定位与左表订单日期匹配的促销记录范围，减少扫描的数据量：

   ALTER TABLE promotions CLUSTER BY (start_date, end_date);
   

2. 预过滤左表数据（业务允许时）
   如果不需要左表全部历史数据，先对orders表做日期范围过滤，减少参与关联的左表数据量：

   WITH filtered_orders AS (
       SELECT * FROM orders WHERE order_date BETWEEN '2024-11-01' AND '2024-11-30'
   )
   SELECT 
       o.order_id, o.order_date, p.promo_id, p.start_date, p.end_date
   FROM filtered_orders o
   LEFT JOIN promotions p
       ON o.order_date BETWEEN p.start_date AND p.end_date;
   

3. 启用搜索优化服务
   针对频繁做范围关联的promotions表，启用搜索优化服务，加快日期范围匹配的查找速度：

   ALTER TABLE promotions ADD SEARCH OPTIMIZATION ON (start_date, end_date);
   

4. 改写查询模拟LEFT JOIN效果
   先通过INNER JOIN获取匹配记录，再用UNION ALL拼接左表未匹配的记录，借助INNER JOIN的高效执行逻辑，同时保留LEFT JOIN的结果：

   WITH matched AS (
       SELECT 
           o.order_id, o.order_date, p.promo_id, p.start_date, p.end_date
       FROM orders o
       JOIN promotions p
           ON o.order_date BETWEEN p.start_date AND p.end_date
   ),
   unmatched AS (
       SELECT 
           o.order_id, o.order_date, NULL AS promo_id, NULL AS start_date, NULL AS end_date
       FROM orders o
       WHERE NOT EXISTS (
           SELECT 1 FROM promotions p
           WHERE o.order_date BETWEEN p.start_date AND p.end_date
       )
   )
   SELECT * FROM matched
   UNION ALL
   SELECT * FROM unmatched;
   

   这种写法在大数据集下通常比直接LEFT JOIN更快，因为NOT EXISTS子查询可利用右表的索引/聚类优化，且UNION ALL的开销远低于LEFT JOIN的全量关联。

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