Teradata SQL UNION and SQL UNION ALL with example

Teradata SQL UNION and UNION ALL are used to retrieve data from multiple SELECT blocks and merge them into one resultset. Below is the SQL UNION syntax:

SELECT EMP_NAME, EMP_CITY FROM EMPLOYEES
UNION
SELECT CUST_NAME, CUST_CITY FROM CUSTOMERS;

The above query will retrieve all the data from first SQL SELECT query and also retrieve the data from second SQL SELECT query and merge the data and display the resultset. SQL UNION will remove the duplicate entries and will display only one row if duplicate rows are present in final resultset.
Below is the SQL UNION ALL syntax:

SELECT EMP_NAME, EMP_CITY FROM EMPLOYEES
UNION ALL
SELECT CUST_NAME, CUST_CITY FROM CUSTOMERS;

SQL UNION ALL operates similarly as UNION and will display resultset combining result from multiple SELECT clauses. SQL UNION ALL will not remove duplicate entries and final resultset may have duplicate rows. Since, there is no duplicate rows checking in SQL UNION ALL it is faster than SQL UNION.

Important point to consider while writing SQL UNION & UNION ALL queries:

The SELECT statements should have equal number of columns and the columns datatypes should be same across all SELECT statements.

The UNION or UNION ALL operator will take technical metadata of the First SELECT statement for complete resultset. So you should make sure that all the columns in first SELECT clause should have highest column width in order to avoid truncation of column values in final resultset.

So when the target table is same and there are multiple SOURCES then SQL UNION or UNION ALL can be used.

One thought on “Teradata SQL UNION and SQL UNION ALL with example

Leave a Reply

Your email address will not be published. Required fields are marked *