Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Does Snowflake NATURAL JOIN support outer-style join?
I like using NATURAL JOIN in Snowflake, because I find it more elegant than explicit join clauses.
However, it appears that the natural join behaves similar to an inner join, in that null values of the join key(s) are dropped. In some cases, I want to do a natural join, but I want to include columns where the join key is a null. I tried OUTER NATURAL JOIN since Snowflake syntax appears to support it:
A
NATURAL JOINcan be combined with anOUTER JOIN.
But when I tried, it still dropped nulls.
Is the concept of a natural join fundamentally opposed to retaining nulls? Or is there a way to do it?
1 answer
Snowflake's NATURAL JOIN follows the SQL standard in that it is simply an (inner) equi-join with implied column names. (This is discouraged as it's safer and clearer to just explicitly write the join condition.) See Snowflake join docs. In the equality comparison, NULLs will not be retained. The outer join may still add back in NULLs for unmatched rows.
0 comment threads