Teradata Set Operators: UNION, UNION ALL, INTERSECT, EXCEPT/MINUS

  • Post author:
  • Post last modified:May 14, 2019
  • Post category:General
  • Reading time:4 mins read

You can use the Teradata set operators to combine similar data sets from two or more SELECT statements in the query. The data types of the columns which are being used in the Teradata SET operators should match or explicitly type cast column values to required data types.

Teradata Set Operators

The SET operators are similar to the JOINs, the only difference is that join combines the columns from different tables whereas SET operators combine rows from different tables.

Read:

In order to use SET operators, following condition must be met:

  1. SELECT statement should include same number of columns from the participating tables
  2. Data type of the column being used in the SELECT statement should match
  3. The ORDER BY, if any, should be included in the last SELECT statements

Types of Teradata SET Operators

Teradata supports three types of SET operators:

  • UNION [DISTINCT] and UNION ALL
  • INTERSECT [DISTINCT]
  • EXCEPT [DISTINCT] or MINUS [DISTINCT]

Teradata SET Operator Syntax

Below is the syntax to use the SET operators

<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>]*
[ORDER BY …]

Teradata UNION and UNION ALL SET Operators

The UNION and UNION ALL operators combines the results of two similar SELECT statements into a single result that contains the rows that are returned by both SELECT statement. These operations are logical OR.

The difference between Teradata UNION and UNION ALL expression is, UNION removes duplicate rows from the result set whereas UNION ALL expression does not remove any duplicates values from the result set.

Teradata UNION and UNION ALL Examples

select ID, name from pat1
UNION

select ID, name from pat2;
ID name
1 ABC
2 BCD
3 CDE
4 DEF
5 EFG
6 AAA
7 BBB

select ID, name from pat1
UNION ALL
select ID, name from pat2;

ID name
1 ABC
1 ABC
2 BCD
3 CDE
4 DEF
5 EFG
6 AAA
7 BBB

Teradata INTERSECT SET Operator

The INTERSECT operation combines the similar results of two SELECT statements into a single result that contains all the rows common to both SELECT statements.

Teradata INTERSECT Examples

select ID, name from pat1
INTERSECT 
select ID, name from pat2;

ID name
1 ABC

Teradata EXCEPT/MINUS Operator

The EXCEPT/MINUS operation finds the difference between the two SELECT statements and the result contains the rows from the first SELECT statement. We can use either EXCEPT or MINUS in the statements. Both names are synonym to each other.Teradata EXCEPT/MINUS

Teradata EXCEPT/MINUS Examples

select ID, name from pat1
EXCEPT 
select ID, name from pat2;

ID name
2 BCD
3 CDE
5 EFG
4 DEF

select ID, name from pat1
MINUS 
select ID, name from pat2;

ID name
2 BCD
3 CDE
5 EFG
4 DEF

Teradata Set Operator Precedence Ordering

INTERSECT takes higher precedence than the other set operations. UNION and EXCEPT/MINUS have the same precedence order. You can override the precedence ordering by using parenthesis.

Tags: