Union all in snowflake

user_17199115 0
Viewed 58

I'm using union all in my code but one of the tables contains a column that the other one doesn't, how can I insert that column in the output, I don't wanna add it in the table itself.. I need that column to be there and the number of columns should be the same from both tables

Thanks

snowflake-cloud-data-platform analysis
1 Answers

The normal way to deal with this is to pad the short query with 'dummy' columns.

Suppose TABLE1 has four columns called COL1, COL2, COL3 and COL4. The TABLE2 3 called COLA, COLB and COLC.

SELECT 
    COL1
    ,COL2
    ,COL3 
    ,COL4  
FROM TABLE1
UNION
SELECT 
    COLA
    ,COLB
    ,COLC
    ,NULL -- or empty string or zero or any padding value compatible with the data-type of COL4.
FROM TABLE2 ;

The results should have 4 columns (COL1 thru COL4) because the column names are taken from the first query.

This practice of padding out data structures to the same signature is common in Databases when combining related but different types and I know it as The Battenberg Manoeuvre.

user_42136620
Related