Teradata SQL LEFT JOIN含NULL关联键结合GROUP BY结果异常问题咨询

1. 该现象并非Teradata特有 #

这是所有遵循ANSI SQL标准的数据库的通用行为：SQL中NULL与任何值（包括另一个NULL）做等值比较时，返回结果都是UNKNOWN，不会被判定为匹配成功。
你观察到左表key2/key3为NULL的行未符合预期，本质是当key2/key3为NULL时，t1.key2 = t2.key2、t1.key3 = t2.key3条件均不成立，这些行无法匹配到t2中key2/key3也为NULL的记录，只会返回t2字段全为NULL的结果。
另外你原SQL存在一处笔误：LEFT JOIN的ON条件中写的t1.key1 = t1.key1恒为真，相当于你最初的关联逻辑没有限制t1和t2的key1匹配，这会导致出现不必要的笛卡尔积关联结果，你后续简化关联条件后只保留了该逻辑，关联范围大幅放宽，因此会出现更多key1的去重值。

2. 满足需求的修改方案 #

你不需要预先对NULL做值映射，可以使用SQL标准语法IS NOT DISTINCT FROM替代等值判断，该语法会将两个NULL值判定为相等，完全符合你的关联需求。修改后的查询代码如下：

SELECT
    key1,
    secondvalue,
    count(DISTINCT firstvalue)
FROM
(
    SELECT
        t1.val AS firstvalue,
        t1.key1,
        t2.val AS secondvalue
    FROM table1 t1
    LEFT JOIN table2 t2 
        ON t1.key1 = t2.key1 -- 修正原笔误，关联两张表的key1
        AND t1.key2 IS NOT DISTINCT FROM t2.key2
        AND t1.key3 IS NOT DISTINCT FROM t2.key3
) AS Testcase
GROUP BY 1, 2

内容的提问来源于stack exchange，提问作者Romero Azzalini