The SET operators in Teradata manipulate the result sets of two or more queries by combining the results of each individual query into a single result set.
Most common SET operators are as below-
| Operator | Description |
| UNION | Combines the result sets of two or more SELECT statement. Duplicate rows are eliminated. |
| UNION ALL | Combines the result sets of two or more SELECT statement. Duplicate rows are not eliminated. |
| INTERSECT | Select only those rows which appears in all the result sets generated by the individual SELECT statement. |
| MINUS/EXCEPT | Returns only those rows from the first result sets but not in the second result set. |
Teradata SET operator Rules
- The numbers of column in the each SELECT statement should be same.
- The data type for each column should be matched or compatible.
- ORDER BY, if used, should be included only in the final SELECT statement.
- Aliasing, Title, Format should be used in the very first SELECT statement, others will be ignored.
Teradata UNION
A UNION operator combines the result sets of two or more SELECT statements together as a single result set. It will remove the duplicate rows.
Teradata UNION Example
Consider below two customer tables related to savings and current account customer.
Savings customer
| cust_id | income | age | years_with_bank | nbr_children | gender | marital_status |
| 1363355 | 13087 | 22 | 1 | 0 | M | 2 |
| 1363329 | 0 | 6 | 0 | 0 | F | 1 |
| 1362672 | 16319 | 50 | 0 | 1 | F | 2 |
| 1362629 | 0 | 6 | 0 | 0 | M | 1 |
| 1362611 | 24115 | 48 | 8 | 1 | F | 2 |
Current account customer
| cust_id | income | age | years_with_bank | nbr_children | gender | marital_status |
| 1363355 | 13087 | 22 | 1 | 0 | M | 2 |
| 1362629 | 0 | 6 | 0 | 0 | M | 1 |
| 1363056 | 2442 | 19 | 1 | 0 | F | 1 |
Now, using below query, we can all the savings and current account holder customer.
sel cust_id from teradatapoint.customer_current UNION sel cust_id from teradatapoint.customer_savings;
When executed, the above query produces below result. Note that only the unique cust_id has been returned.
cust_id ----------- 1362611 1362629 1362672 1363056 1363329 1363355
Teradata UNION ALL
This one is also work same as UNION except it will not eliminate the duplicate from the final result set.
Teradata UNION ALL Example
Return all the savings and current account holder customer id.
sel cust_id from teradatapoint.customer_current UNION ALL sel cust_id from teradatapoint.customer_savings;
When executed, the above query produces below result.
*** Query completed. 8 rows found. One column returned. *** Total elapsed time was 1 second. cust_id ----------- 1363355 1363355 1362629 1362672 1363056 1363329 1362629 1362611
Teradata INTERSECT
It returns only the rows which are common for all result sets returned by the individual SELECT statement.
Teradata INTERSECT Example
The following example returns only the customer id who has both the savings and current account in the bank.
sel cust_id from teradatapoint.customer_current INTERSECT sel cust_id from teradatapoint.customer_savings;
When executed, the above query produces below result.
Output of the above query is as below-
*** Query completed. 2 rows found. One column returned. *** Total elapsed time was 1 second. cust_id ----------- 1362629 1363355
Teradata MINUS/EXCEPT
The MINUS/EXCEPT operator returns only those rows which are present in the first result set but not in the second one.
Teradata MINUS/EXCEPT Example
The following example fetches the customer id who has only savings account but not current account.
sel cust_id from teradatapoint.customer_savings MINUS sel cust_id from teradatapoint.customer_current;
When executed, the above query produces below result.
Output of the above query is as follows-
*** Query completed. 3 rows found. One column returned. *** Total elapsed time was 1 second. cust_id ----------- 1362672 1363329 1362611