Teradata SET operators



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