分区表左连接时ON与WHERE重复设左表条件为何性能更优？

问题背景 #

我原本对JOIN操作的ON与WHERE子句的理解是：左表过滤条件放在WHERE子句，右表过滤条件放在ON子句，示例如下：

select * 
from A 
left join B 
  ON A.something=B.something 
 AND {some_condition_on_B} 
WHERE {some_condition_on_A}

但在处理分区表A时，遇到了明显的性能差异：

• 以下查询执行速度极慢：

select * 
from A 
left join B 
  ON A.something=B.something 
WHERE {A.foo = bar}

• 而以下查询执行速度明显更快：

select * 
from A 
left join B 
  ON A.something=B.something 
 AND {A.foo = bar}
WHERE {A.foo = bar}

从逻辑上看两者结果完全一致，但性能表现天差地别，想明确背后的原因。

核心原因：分区剪枝的触发时机差异 #

这个问题的关键在于分区表的剪枝逻辑执行时机，尤其是分布式SQL引擎（如Hive、Spark SQL）的优化行为：

• 第一种查询中，WHERE A.foo = bar的过滤条件，部分引擎会安排在JOIN操作完成后执行。这意味着引擎会先加载A表的所有分区数据，和B表完成左JOIN，之后再过滤出符合A.foo=bar的行。大量无关分区的数据参与了JOIN计算，导致IO和计算量暴增，性能自然低下。
• 第二种查询中，把A.foo=bar同时写入ON子句后，引擎会将这个条件视为JOIN的前置过滤条件，在JOIN操作开始前就触发分区剪枝——只加载A表中符合A.foo=bar的分区数据，再和B表做JOIN。最后WHERE子句的过滤只是做逻辑上的兜底（确保结果正确性），但实际参与JOIN的数据量已经大幅减少，因此性能明显提升。

补充说明 #

你之前“左表过滤放WHERE”的结论是逻辑正确性层面的总结，但在性能优化场景下，需要结合执行引擎的优化逻辑调整：

• 左JOIN中，ON子句的左表过滤条件，逻辑上和WHERE子句的左表过滤完全等价——都会排除左表中不符合条件的行，不会出现右表过滤那种“保留左表行、右表补NULL”的情况，因此两个查询的最终结果一致。
• 对于分区表，优先在ON子句中添加左表过滤条件，能帮助引擎更早触发分区剪枝，避免不必要的数据加载和计算。

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