join - Hive:LEFT JOIN 与 JOIN 在 ON 子句中使用过滤器给出不同的结果

Question

假设有两个表:

    table1.c1   table1.c2
1   1           A
2   1           B
3   1           C
4   2           A
5   2           B

和

    table2.c1   table2.c2
1   2           A
2   2           D
3   3           A
4   3           B

当我这样做时:

select distinct t1.c1, t2.c2
from
schema.table1 t1
join
schema.table2 t2
on (t1.c2 = t2.c2 
    and t1.c1 = t2.c1
    and t1.c1 = 2)

在 Hive 中，我得到:

    t1.c1   t2.c2
1   2   A

这是预期的结果，没有问题。但是，当我这样做时:

select distinct t1.c1, t2.c2
from
schema.table1 t1
left join
schema.table2 t2
on (t1.c2 = t2.c2 
    and t1.c1 = t2.c1
    and t1.c1 = 2)

我得到:

    t1.c1   t2.c2
1   1       NULL
2   2       NULL
3   2       A

因此，ON 子句中的过滤器似乎没有像我预期的那样工作。过滤器 t1.c1 = t2.c1 和 t1.c1 = 2 没有被应用，在 LEFT JOIN 中，它没有在第二个表所以 t2.c2 是 NULL。

我想答案一定在 doc 中(可能在“Joins occur BEFORE WHERE CLAUSES”部分？)但我仍然不明白其中的区别。

给出不同结果的过程是怎样的？

Answer 1

这就是 LEFT (OUTER) JOIN 的工作方式:

您在 ON 子句中指定了一些匹配条件。如果在“右”表中找到匹配行，则将其连接到“左”表中的行。如果没有匹配的行，它仍然会返回“左”行以及“右”表中设置为空的所有字段。因此它永远不会根据 ON 条件过滤“左”表中的任何行。使用 Hive-documentation的术语:左表是“保留行表”，而右表是“空供应表”。

这与 INNER JOIN 相反，它只返回在另一个表中具有匹配伙伴的行。所以没有“保留表”，也不需要“空供应表”