在雪花SQL中执行完全外部联接时无法获取所有行

Question

我有两张桌子。第一表是

Category       Date        Sales_1
Chocs          2020-10-10  2091847.8100
Biscuits       2020-10-10  1.6000
Rice           2020-10-10  597003.4900
Sugar          2020-10-10  1533084.3100
Chips          2020-10-10  3.5000
Sweet          2020-10-10  3.0000

第二表是

Category       Date        Sales_2
Chocs          2020-10-10  2091847.8100
Biscuits       2020-10-10  1.6000
Rice           2020-10-10  597003.4900
Sugar          2020-10-10  1533084.3100
Spices         2020-10-10  90748.89
Chips          2020-10-10  3.5000
Sweet          2020-10-10  3.0000

如果我完全加入了表1和表2中的类别。即

   Category       Date            Sales_1         Sales_2
   Chocs          2020-10-10      2091847.8100    NULL
   Biscuits       2020-10-10      1.6000          NULL
   Rice           2020-10-10      597003.4900     NULL
   Sugar          2020-10-10      1533084.3100    NULL
   NULL           NULL            NULL            90748.89
   Chips          2020-10-10      3.5000          NULL
   Sweet          2020-10-10      3.0000          NULL

它不包括在about输出中的值以下

Category       Date        Sales_1   Sales_2
NULL           NULL        NULL      90748.89

预期的输出必须是，它应该返回所有行。我尝试了下面的雪花SQL查询

select coalesce(table_1.category,table2.category) cat,
       coalesce(table_1.date,table2.date) dt,
       coalesce(table_1.sales,table2.sales) sales
from table_1 
full outer join table_2 on table_1.category = table_2.category

有人能帮我吗？

Answer 1

SQL查询如下。和上面一样，我的数据有个错误。

select coalesce(table_1.category,table2.category) cat,
       coalesce(table_1.date,table2.date) dt,
       coalesce(table_1.sales,table2.sales) sales
from table_1 
full outer join table_2 on table_1.category = table_2.category