UNION Teradata

Compatibility: ANSI

The UNION set operator is used to merge the rows from two or more sets. The join performed for a UNION is more similar to an OUTER JOIN.

The following is the syntax of the UNION:

To demonstrate the elimination of duplicates, the following UNION selects rows from the same table twice and does not use the ALL:

5 Rows ReturnedUNION

Now, the ALL is added to allow duplicates:

10 Rows ReturnedALL is added to allow duplicates

As mentioned previously, the second version using ALL runs faster. When there is no possibility of duplicates, always use ALL. However, if duplicates will cause a problem in the output, by all means, don't use the ALL.

The UNION is commonly used to create reports that combine data from slightly different domains. For instance, managers are responsible for departments and employees work in departments, for a manager.

The next UNION will return the departmental information and combine it with the employee information:

10 Rows Returnednext UNION
next UNION

Like the other SET operators, UNION will eliminate duplicates and the ALL can be used to prevent this from happening. Although there would never be any duplicates on this output, the ALL should be used to provide peak performance. Since there are no duplicates, do not force Teradata to attempt to eliminate them and the query will run faster.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics