Search code examples
sqlsnowflake-cloud-data-platformunioncoalesce

Coalesce gives repeat columns that prevents doing a union

I have two tables (Table 1 and Table 2) that I join to then do a coalesce. So...

SELECT t1.*,
coalesce(t1.A, t2.A_T) as A
FROM table1 as t1
INNER JOIN table2 as t2 
 ON t1.key = t2.key

enter image description here

Doing what I said above, I will get a repeated A column given I want to replace missing values in A from Table 1 with Table 2's A column. I want to do a union but a repeated A column is an issue (as seen in Table 3). You cannot do a union with different numbers of columns.

How can I fix this issue?

What I want:

enter image description here

Note: the actual tables I am working with have over 40 columns each. But this example is to get to the point of my issue.

Solution

  • Snowflake supports exclude so you could do

    select coalesce(t1.A, t2.A_T) as A, t1.* exclude(t1.A)
from ...

    Note that your problem stems from the fact that you are using * to select columns instead of being explicit about which columns to select.