Teradata - SET Operators

Advertisements

Previous Page
Next Page  

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 duplicates.

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.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Robert Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Robert James 1/4/2008 3 12/1/1984
EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

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 duplicate 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 duplicates 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
Advertisements