Using Multiple Set Operators in a Single Request Teradata

As mentioned earlier, all of the Set operators may be combined into a single request. The list of considerations indicated that the order of processing is: INTERSECT, UNION and EXCEPT/MINUS. If there is more than one of any of these SET operators, they will be performed in the order coded, from top to bottom. However, the sequence may be changed at any time by enclosing any operator and its associated SELECT requests in parentheses.

The following combines one of each operator without changing the natural sequence:

9 Rows Returnedfollowing combines one of each operator without changing the natural sequence

Then, to change the processing order of the set operators, parentheses are used below to cause the EXCEPT to execute first, then the INTERSECT and lastly, the UNION:

10 Rows ReturnedINTERSECT and lastly, the UNION

As mentioned earlier, it takes some planning to combine them. Also be sure to notice that every SELECT must have the same number of columns and they must be of the same domain. It becomes apparent that it is not necessarily an easy request to write and it is a bit limited in its flexibility. That is why we tend to use them individually to populate derived tables or in views for joining against other tables or to eliminate rows from multiple answer sets.

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

Teradata Topics