UNION ALL is a SQL operator that allows the combination of multiple resultsets. The syntax is the following:
query_expression_1 UNION [ ALL ] query_expression_2
For more information, please review the following Teradata documentation.
Column Size differences
Even though the operator is translated into the same operator in Snowflake, there could be detailed differences in functional equivalence. For example, the union of different columns which have different column sizes. Teradata does truncate the values when the first SELECT statement contains less space in the columns.
Teradata behavior
Same behavior in ANSI and TERA session modes.
For this example, the following input will show the Teradata behavior.
CREATETABLEtable1(col1 VARCHAR(20));INSERT INTO table1 VALUES('value 1 abcdefghijk');INSERT INTO table1 VALUES('value 2 abcdefghijk');CREATETABLEtable2(col1 VARCHAR(10));INSERT INTO table2 VALUES('t2 row 1 a');INSERT INTO table2 VALUES('t2 row 2 a');INSERT INTO table2 VALUES('t2 row 3 a');
t2 row 3 a
value 1 abcdefghijk --> NOT truncated
t2 row 1 a
t2 row 2 a
value 2 abcdefghijk --> NOT truncated
Workaround to get the same functionality
In this case, the size of the column of the table2 is 10 and the table1 is 20. So, the size of the first column in the query should be the element to complete the LEFT() function used here. Review more information about the Snowflake LEFT function HERE.
SELECT col1 FROM table2 -- size (10)UNION ALLSELECTLEFT(col1, 10) AS col1 FROM table1;
t2 row 1 a
t2 row 2 a
t2 row 3 a
value 1 ab
value 2 ab
Case 3 - multiple columns - same size by table: UNION ALL for columns varchar (20) over columns varchar (10)
For this case, it is required to set up new data as follows:
The column with the smallest size from the first SELECT is used to determine the size of the columns from the second SELECT.
SELECTcol1, col2 FROMtable6UNION ALLSELECTLEFT(col1, 5) as col1, LEFT(col2, 5) AS col2 FROMtable5;
col1
col2
t2 row 3 a
t2 3b
value 1 ab
value
t2 row 1 a
t2 1b
t2 row 2 a
t2 2b
value 2 ab
value
Other considerations about column size differences
CHAR and VARCHAR behave the same.
Number columns may behave differently. The numbers cannot be truncated, so there is an overflow in the Teradata environment. So, this is not applied to these data types. Review the following example:
-- Teradata number sample CREATETABLEtable11(col1 NUMBER(2));INSERT INTO table11 VALUES(10);INSERT INTO table11 VALUES(10);CREATETABLEtable12(col1 NUMBER(1));INSERT INTO table12 VALUES(1);INSERT INTO table12 VALUES(1);INSERT INTO table12 VALUES(1);-- ERROR! Overflow occurred when computing an expression involving table11.col1SELECT col1 FROM table12UNION ALLSELECT col1 FROM table11;