November 15



By Roland Wenzlofsky

November 15, 2019

sql, union, union all

The Teradata UNION ALL statement takes all rows from the intermediate results and puts them together into the result set. The UNION statement additionally removes duplicate rows before returning the result set.


Both statements, the Teradata UNION and the Teradata UNION ALL, combine the result sets of two or more SQL statements:

SELECT col1 FROM TheTable1 UNION SELECT col1 FROM TheTable2;
SELECT col1 FROM TheTable1 UNION ALL SELECT col1 FROM TheTable2; 

All statements combined with UNION have to return the same number of columns, and the data types of all columns across the participating select statements have to match. 

If they don't match, the data types of the very first SQL select statement will be the relevant ones and not match columns of the remaining statements are implicitly cast to the same data type the very first select statement has.

Keep this in mind, especially if your column is a character data type, as this could cause hidden truncation of text columns –  a problem that is tough to discover.

UNION ALL vs. UNION Performance

teradata union

UNION ALL always gives better performance than UNION.

For UNION, The AMPs must sort the resulting rows of the spools of all queries in order to have and recognize duplicates side by side.

Sorting is expensive and makes the difference in performance between UNION and UNION ALL.

Only use UNION if you need to remove row duplicates. If there are no row duplicates in the data, always avoid the sort step by using UNION ALL.

Impact of Character Set

With the UNION operator, it should also be noted that the character set also has an influence on the query. Let me give you an example:

SELECT col1 FROM TheTable1 UNION SELECT col1 FROM TheTable2;

If the table “Table2” in column “col1” contains characters that are not contained in the column “col1” of table “Table1”, this may cause an error.

The risk is high if the table “Table1” is CHARACTER SET LATIN and the table “Table2” is CHARACTER SET UNICODE.

If the table “Table2” contains UNICODE characters, this leads to an error, since the CHARACTER SET of the table “Table1” determines the character set of the total result.


teradata intersect

The Intersect operator returns as Ergenis row duplicate of two subqueries, that is, all rows that are identical in both subqueries.

SELECT col1,col2 FROM TheTable1 INSERTSECT SELECT col1,col2 FROM TheTable2;

Similar to UNION ALL, INTERSECT ALL returns row duplicates.

For performance reasons, INTERSECT should only be used if duplicates must be removed. INTERSECT ALL should be the preferred operator


teradata minus

The MINUS/EXCEPT operator returns as result all rows of the first query which are not present in the second query:

SELECT col1,col2 FROM TheTable1 MINUS SELECT col1,col2 FROM TheTable2;
SELECT col1,col2 FROM TheTable1 EXCEPT SELECT col1,col2 FROM TheTable2; 

It doesn't matter if you use MINUS or EXCEPT, both operators return the same result.

MINUS can be used to determine whether 2 tables are identical. For this the MINUS operator must be executed in both directions:

SELECT col1,col2 FROM TheTable1 MINUS SELECT col1,col2 FROM TheTable2;
SELECT col1,col2 FROM TheTable2 EXCEPT SELECT col1,col2 FROM TheTable1; 

In addition, you must first check whether both tables to be compared are actually populated, otherwise MINUS provides equality of the tables although this is not given.

Precedence of SET Operators

The remaining set operators (UNION, UNION ALL, EXCEPT, MINUS) all have the same priority. When using SET operators, it is therefore recommended that you always use parentheses to ensure the desired priority.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Never miss a good story!

     Subscribe to our newsletter to keep up with the latest trends!