hive的left join，条件放在on、whrere里的不同结果(含左右表各种情况）

一、left join中where里放右侧表的过滤条件的后果：将左表满足条件的数据也过滤掉了。

原因：在总的where里放的条件是在生成的中间大宽表的结果上做的过滤

我亲身经历的一次事故：本意是要过滤各自的数据，然后做左关联，那么左表的数据自然是不希望被过滤掉的，要不然就用join了。

但是不理解where条件的执行顺序，将右表的过滤条件也放在里where后面，结果丢失了大量的左表数据，做了次事故报告。

涨的姿势：如果要过滤右表的数据，要么放在右表的子查询里，要么放在on里。

二、left join 过滤条件放在on里和放在on之前的各自子查询里效果是不是一样的？

由一引发的引申：既然on是在关联的时候做的过滤，那么是不是可以省去先写各自的子查询，再关联，而可以直接将过滤条件写在关联条件里？

用实际数据做了实验。

结论：左表的过滤不能放在on里。放在on里的对左表的过滤是无效的。

做了四组实验：

实验一、将左右表的过滤条件都放在on里，子查询的where及最后的总where里都不放过滤条件；

实验二、将左右表的过滤都放在各自的子查询里，最后总的where里不放过滤条件；

实验三、将右表的过滤条件放在子查询里，左表的过滤条件放在最后总的where里；

实验四、将右表的过滤条件放在on里，左表的过滤条件放在最后总的where里。

结论：后三种情况都是对的，第一种情况错的，对左表的过滤放在on里并没有生效。

最安全的写法：实验二，即先将各自的过滤条件写在各自的子查询里，再进行过滤。

最简单的写法：实验四。即左表的过滤条件放在总的where里，右表的过滤条件放在on里。省去各自写一层子查询。但是执行效率是实验二高。

https://blog.csdn.net/cainiao1923/article/details/103594153这位博主也做了相关的测试，证明四是对的，但是他没有说二也是对的。

三、补充一个关系不是很大的having

HAVING must be used together with GROUP BY or SELECT DISTINCT

having 过滤是最后执行的，用于过滤聚合后的结果。所以如果有聚合的话，执行顺序是先where，再group by,然后having。