怎样在SQL中对包含分区的两张大表实现同分区对等JOIN？-SQL-PHP中文网

直接写join导致全表扫描，是因为分区裁剪需where中对两张表的分区列分别做确定性等值过滤，若只写一侧或用函数包装，优化器无法推导，被迫扫描全部分区。

怎样在sql中对包含分区的两张大表实现同分区对等join？

为什么直接写 `JOIN` 会导致全表扫描？

当两张大表都按相同字段（比如 dt）做了分区，但 SQL 中没显式约束分区条件，优化器通常无法推导出“只查某几个分区”，就会扫全量数据。典型表现是执行计划里出现大量 PartitionKey: ALL 或 Scan all partitions，哪怕你只想要 dt = '2024-01-01' 的结果。

关键点在于：分区裁剪（partition pruning）不是自动生效的，它依赖 WHERE 条件中对分区列的**确定性等值过滤**，且该列必须在 JOIN 条件之外单独出现。

错误写法：SELECT * FROM t1 JOIN t2 ON t1.id = t2.id AND t1.dt = t2.dt WHERE t1.dt = '2024-01-01' —— 看似合理，但某些引擎（如 Hive 3.1 之前、Spark SQL 默认配置）可能仍无法对 t2 做分区裁剪
正确姿势：WHERE 中分别限定两张表的分区列，且用等值条件

`WHERE` 必须双侧显式过滤分区列

即使两张表分区键同名同值，也必须各自写一遍过滤条件，不能只写一边。这是多数 SQL 引擎（Hive、Trino、Spark SQL）的硬性要求，否则右侧表不裁剪。

示例（以 Hive/Trino 为例）：

SELECT t1.user_id, t1.amount, t2.status
FROM sales t1
JOIN user_profile t2 ON t1.user_id = t2.user_id
WHERE t1.dt = '2024-01-01'
  AND t2.dt = '2024-01-01';

如果只写 t1.dt = '2024-01-01'，t2 可能扫描全部历史分区
分区列类型要一致（比如都是 STRING，别一个是 DATE），否则隐式转换会破坏裁剪
避免用函数包装分区列，如 WHERE to_date(t1.dt) = '2024-01-01' —— 裁剪失效

分区键不在 JOIN 条件里？那就先 `WHERE` 后 `JOIN`

如果业务逻辑要求按非分区字段关联（比如 user_id），但你还想利用分区加速，唯一可靠方式是把分区过滤提前到子查询或 CTE 中，确保 JOIN 输入已是裁剪后的结果集。

不要依赖优化器“猜”你的意图：

WITH t1_part AS (
  SELECT * FROM sales WHERE dt = '2024-01-01'
),
t2_part AS (
  SELECT * FROM user_profile WHERE dt = '2024-01-01'
)
SELECT *
FROM t1_part t1
JOIN t2_part t2 ON t1.user_id = t2.user_id;