Hive:使用 2 个 ON 条件和 NULL 进行 JOIN 的最佳方法

Question

我有 2 个表格，如下所示。

Table_A

ID1  ID2  NAME
112  NULL ADAM
132  990  BRIAN
NULL 980  CARL

Table_B

ID1  ID2  SURNAME
112  NULL LEVINE
132  990  LARA
NULL 980  JOHNSON

如果我按如下所示加入表，则空比较将不起作用，因此不会返回 ADAM 的姓氏

SELECT A.NAME,B.SURNAME
FROM
TABLE_A A
LEFT JOIN
TABLE_B B
ON A.ID1 = B.ID1
AND
A.ID2 = B.ID2;

我在 ID2 的 ON 子句中添加了对 NULL 的检查，这确实有效，但事实证明，即使是小表，该操作也很昂贵。(见下文(

SELECT A.NAME,B.SURNAME
FROM
TABLE_A A
LEFT JOIN
TABLE_B B
ON 
(A.ID1 = B.ID1 OR (A.ID1 IS NULL AND B.ID1 IS NULL))
AND
(A.ID2 = B.ID2 OR (A.ID2 IS NULL AND B.ID2 IS NULL));

进行这种比较的正确方法是什么？

Answer 1

要像普通值一样连接 NULL，请使用NVL()函数将NULL替换为数据中通常不使用的一些值，例如-9999：

SELECT A.NAME,B.SURNAME
FROM
TABLE_A A
LEFT JOIN
TABLE_B B
ON NVL(A.ID1,-9999) = NVL(B.ID1,-9999)
AND
NVL(A.ID2,-9999) = NVL(B.ID2,-9999);

Answer 2

这是一个典型的情况场景，需要 NULL 安全相等运算符，Hive 使用 GenericUDF<=>原生支持该运算符。这个运算符，正如我引用的：

Returns same result with EQUAL(=) operator for non-null operands, 
but returns TRUE if both are NULL, FALSE if one of the them is NULL.

因此，SQL非常简单，如下所示：

select 
a.name,
b.surname
from table_a a
left join table_b b
on a.id1 <=> b.id1 and a.id2 <=> b.id2;

Answer 3

Hive 不支持on条件下的or表达式。
连接条件应包含purely equality expression个。

我更喜欢COALESCE功能：

SELECT A.NAME,B.SURNAME
FROM
TABLE_A A
LEFT JOIN
TABLE_B B
ON 
COALESCE(A.ID1, 'missing') = COALESCE(B.ID1, 'missing') 
AND
COALESCE(A.ID2, 'missing') = COALESCE(B.ID2, 'missing')