为什么SQL抱怨对联接键的引用不明确

Question

为什么Vertica在以下代码中返回zip3不明确的错误？由于它被用作左联接的键，因此对于两个表，它必须相等。

SELECT zip, zip3, city, zip3_name
FROM zip_codes
LEFT JOIN zip3_codes
USING (zip3);

这会产生以下错误：

Query 1 ERROR: ERROR:  Column reference "zip3" is ambiguous
DETAIL:  Reference "zip3" could refer to either "public.zip_codes.zip3" or "public.zip3_codes.zip3"

我可以通过选择zip.zip3、zip3.zip3或COALESCE(zip.zip3, zip3.zip3)来解决这个问题，但我很好奇为什么这是必要的。

Answer 1

虽然乍一看，列必须始终相同，但事实并非如此。JOIN实际上是一个LEFT JOIN，它返回第一个表中的所有行和第二个表中所有可能的匹配，当没有匹配时，给NULLs。

来自zip_codes的不匹配记录将在zip.zip3和zip3.zip3中产生不同的值，第一个给出一个值，第二个是NULL，所以在这种特殊情况下，交换这些值并不等效，您需要确定需要哪个表中的哪些列，并用其表名消除歧义。

此外，即使不是这样，错误仍然会弹出。如果我们将LEFT JOIN更改为INNER JOIN，则您的假设将为真，并且列将始终具有相同的值，但您仍然会得到相同的错误。

原因是SQL分析器不够聪明，无法实现值始终相同。按照标准，如果查询引用不同表中具有相同名称的列，则必须指定它来自哪个表。不会进一步尝试解决歧义。