Hive 中 on和where的区别

Hive 中 ON 和 WHERE 的区别

✅ 核心区别总结

维度	ON	WHERE
作用时机	JOIN 时过滤	JOIN 后过滤
影响结果	影响 JOIN 逻辑	不影响 JOIN 逻辑
适用范围	JOIN 操作	所有查询
性能影响	早期过滤，性能好	晚期过滤，性能差

---

✅ 详细对比分析

1. 执行时机差异

ON 条件 - JOIN 时执行

-- ON 条件在 JOIN 阶段执行
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND b.status = 'active';

• 执行逻辑：先根据 a.id = b.id 和 b.status = 'active' 进行 JOIN
• 结果：LEFT JOIN 中，不满足 b.status = 'active' 的 b 表记录不会 JOIN 进来

WHERE 条件 - JOIN 后执行

-- WHERE 条件在 JOIN 完成后执行
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.status = 'active';

• 执行逻辑：先完成 JOIN，再过滤结果
• 结果：LEFT JOIN 变成 INNER JOIN 效果（因为 WHERE 过滤了 NULL 记录）

---

2. 对不同 JOIN 类型的影响

A. INNER JOIN - 效果相同

-- 这两种写法结果相同
SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.id AND b.status = 'active';

SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.id
WHERE b.status = 'active';

B. LEFT/RIGHT JOIN - 效果不同

-- ON 条件：保留 a 表所有记录
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND b.status = 'active';
-- 结果：a 表所有记录都保留，b 表不匹配的为 NULL

-- WHERE 条件：LEFT JOIN 变成 INNER JOIN
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.status = 'active';
-- 结果：只保留 b.status = 'active' 的记录

---

3. 性能影响

ON 条件 - 早期过滤

-- 推荐：在 JOIN 时就过滤
SELECT a.*, b.*
FROM large_table a
LEFT JOIN small_table b ON a.id = b.id AND b.category = 'important';
-- 优点：减少 JOIN 操作的数据量

WHERE 条件 - 晚期过滤

-- 不推荐：先 JOIN 再过滤
SELECT a.*, b.*
FROM large_table a
LEFT JOIN small_table b ON a.id = b.id
WHERE b.category = 'active';
-- 缺点：JOIN 完成后才过滤，数据量大

---

4. 实际业务场景示例

场景 A：用户与订单统计

-- 需求：统计所有用户订单数，只统计有效订单
CREATE TABLE users (user_id STRING, name STRING);
CREATE TABLE orders (order_id STRING, user_id STRING, status STRING);

-- ✅ 正确：使用 ON 过滤有效订单
SELECT u.name, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 'completed'
GROUP BY u.user_id, u.name;

-- ❌ 错误：使用 WHERE 会丢失无订单用户
SELECT u.name, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'  -- 这样 LEFT JOIN 变成 INNER JOIN
GROUP BY u.user_id, u.name;

场景 B：维度表关联

-- 需求：关联商品信息，只保留有效商品
SELECT s.*, p.product_name
FROM sales s
LEFT JOIN products p ON s.product_id = p.product_id AND p.is_active = 'Y';

-- 如果用 WHERE：
SELECT s.*, p.product_name
FROM sales s
LEFT JOIN products p ON s.product_id = p.product_id
WHERE p.is_active = 'Y';  -- 丢失非活跃商品的销售记录

---

5. 性能优化建议

A. 过滤条件前移

-- ✅ 优化：将过滤条件放在 ON 中
SELECT a.*, b.*
FROM big_table a
JOIN small_table b ON a.id = b.id 
  AND b.status = 'active' 
  AND b.category IN ('A', 'B');

-- ❌ 非优化：过滤条件放在 WHERE
SELECT a.*, b.*
FROM big_table a
JOIN small_table b ON a.id = b.id
WHERE b.status = 'active' AND b.category IN ('A', 'B');

B. 复合条件处理

-- ✅ 复杂条件合理分配
SELECT a.*, b.*, c.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id AND b.process_date >= '2024-01-01'
LEFT JOIN table_c c ON a.id = c.a_id AND c.status = 'valid'
WHERE a.dt = '2024-11-06';  -- 时间分区过滤

---

6. 特殊情况处理

A. 多个 JOIN 条件

-- ✅ 正确：每个 JOIN 的过滤条件放在对应 ON 中
SELECT *
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id AND b.status = 'active'
LEFT JOIN table_c c ON a.id = c.a_id AND c.category = 'important'
WHERE a.create_time > '2024-01-01';

B. NULL 值处理

-- ON 条件对 NULL 的影响
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id AND b.name IS NOT NULL;
-- 结果：b.name 为 NULL 的记录不会 JOIN 进来

-- WHERE 条件对 NULL 的影响
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
WHERE b.name IS NOT NULL;
-- 结果：LEFT JOIN 变成 INNER JOIN

---

📌 面试总结

ON 和 WHERE 的核心区别：

1. 执行时机：ON 在 JOIN 时执行，WHERE 在 JOIN 后执行
2. 对结果影响：ON 影响 JOIN 逻辑，WHERE 不影响 JOIN 类型
3. 性能：ON 早期过滤性能更好
4. 最佳实践：JOIN 相关过滤用 ON，全局过滤用 WHERE

记忆口诀：ON 控制 JOIN，WHERE 控制结果