Teradata - SET Operators

SET operators combine results from multiple SELECT statement. This may look similar to Joins, but joins combines columns from multiple tables whereas SET operators combines rows from multiple rows.

Rules

The number of columns from each SELECT statement should be same.

The data types from each SELECT must be compatible.

ORDER BY should be included only in the final SELECT statement.

UNION

UNION statement is used to combine results from multiple SELECT statements. It ignores duppcates.

Syntax

Following is the basic syntax of the UNION statement.

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
UNION  

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

Example

Consider the following employee table and salary table.

The following UNION query combines the EmployeeNo value from both Employee and Salary table.

SELECT EmployeeNo 
FROM  
Employee 
UNION 

SELECT EmployeeNo 
FROM  
Salary;

When the query is executed, it produces the following output.

EmployeeNo 
----------- 
   101 
   102 
   103 
   104 
   105

UNION ALL

UNION ALL statement is similar to UNION, it combines results from multiple tables including duppcate rows.

Syntax

Following is the basic syntax of the UNION ALL statement.

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
UNION ALL 

SELECT col1, col2, col3…
FROM  
<table 2> 
[WHERE condition];

Example

Following is an example for UNION ALL statement.

SELECT EmployeeNo 
FROM  
Employee 
UNION ALL 

SELECT EmployeeNo 
FROM  
Salary;

When the above query is executed, it produces the following output. You can see that it returns the duppcates also.

 EmployeeNo 
----------- 
    101 
    104 
    102 
    105 
    103 
    101 
    104 
    102 
    103

INTERSECT

INTERSECT command is also used to combine results from multiple SELECT statements. It returns the rows from the first SELECT statement that has corresponding match in the second SELECT statements. In other words, it returns the rows that exist in both SELECT statements.

Syntax

Following is the basic syntax of the INTERSECT statement.

SELECT col1, col2, col3… 
FROM  
<table 1>
[WHERE condition] 
INTERSECT 

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

Example

Following is an example of INTERSECT statement. It returns the EmployeeNo values that exist in both tables.

SELECT EmployeeNo 
FROM  
Employee 
INTERSECT 

SELECT EmployeeNo 
FROM  
Salary; 

When the above query is executed, it returns the following records. EmployeeNo 105 is excluded since it doesn’t exist in SALARY table.

EmployeeNo 
----------- 
   101 
   104 
   102 
   103 

MINUS/EXCEPT

MINUS/EXCEPT commands combine rows from multiple tables and returns the rows which are in first SELECT but not in second SELECT. They both return the same results.

Syntax

Following is the basic syntax of the MINUS statement.

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
MINUS 

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

Example

Following is an example of MINUS statement.

SELECT EmployeeNo 
FROM  
Employee 
MINUS 

SELECT EmployeeNo 
FROM  
Salary;

When this query is executed, it returns the following record.

EmployeeNo 
----------- 
   105