There are many SET operators (UNION,MINUS & INTERSECT) available in teradata and they work in similar fashion as the mathematical SET operations.

Teradata: SET Operators

Teradata set operators provide ways to combine similar datasets from two or more queries into a single dataset. There are many SET operators available in teradata and they work in similar way as the mathematical SET operations. These can also be used to compare 2 tables.

• UNION
• UNION ALL
• INTERSECT
• INTERSECT ALL
• MINUS
• MINUS ALL
• UNION vs UNION ALL
• INTERSECT vs INTERSECT ALL
• MINUS vs MINUS ALL

Table 1
CREATE MULTISET VOLATILE TABLE setoperators_1(
   id INTEGER,
   name VARCHAR(100)
)
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;


Table 2
CREATE MULTISET VOLATILE TABLE setoperators_2(
   id INTEGER,
   name VARCHAR(100)
)
PRIMARY INDEX(id)
ON COMMIT PRESERVE ROWS;

Table 1:
INSERT INTO setoperators_1 VALUES(1,'Teradata');
INSERT INTO setoperators_1 VALUES(1,'Teradata');
INSERT INTO setoperators_1 VALUES(2,'Database');
INSERT INTO setoperators_1 VALUES(3,'Oracle');
INSERT INTO setoperators_1 VALUES(4,'Vertica');
INSERT INTO setoperators_1 VALUES(4,'Vertica');

Table 2:
INSERT INTO setoperators_2 VALUES(1,'Teradata');
INSERT INTO setoperators_2 VALUES(1,'Teradata');
INSERT INTO setoperators_2 VALUES(2,'Database');
INSERT INTO setoperators_2 VALUES(5,'DB2');
INSERT INTO setoperators_2 VALUES(6,'MYSQL');

• UNION: This operator returns all the records from the multiple tables but it will return only single record even if the same record in present in the multiple tables.

  
  SELECT id,name from setoperators_1
  UNION
  SELECT id,name from setoperators_2;
  
  Output:
  id   name
  ---  -------------
   1    Teradata
   2    Database
   3    Oracle
   4    Vertica
   5    DB2
   6    MYSQL
  

  → Problem with UNION in Teradata
  • UNION will return the values with maximum field length of first table in the UNION sub-query.
  • This mean values will get truncated from subsequent tables for columns having more size than that of first table in the subquery.

    
    SELECT 'A' FROM setoperators_2
    UNION
    SELECT 'AB' FROM setoperators_2;
    
    Output:
    'A'
    ---
    A
    

  → Solution
  • Do a cast to maximum length of each field in order to avoid values getting truncated.

    
    SELECT CAST('A' AS VARCHAR(10)) FROM setoperators_2
    UNION
    SELECT CAST('AB' AS VARCHAR(10)) FROM setoperators_2;
    
    Output:
    'A'
    ----------
    A
    AB
    

  
  
• UNION ALL: This operator returns all the records from the multiple tables. It will return duplicate records if the same record in present in the multiple tables.

  
  SELECT id,name from setoperators_1
  UNION ALL
  SELECT id,name from setoperators_2;
  
  Output:
  id   name
  ---  -------------
   3   Oracle
   1   Teradata
   1   Teradata
   4   Vertica
   4   Vertica
   2   Database
   5   DB2
   1   Teradata
   1   Teradata
   6   MYSQL
   2   Database
  

  
  
• INTERSECT: This operator returns only common rows present in both the tables. Duplicates rows will be filtered and it will return only unique records.

  
  SELECT id,name from setoperators_1
  INTERSECT
  SELECT id,name from setoperators_2;
  
  Output:
  id   name
  ---  -------------
   1    Teradata
   2    Database
  

  
  
• INTERSECT ALL: This operator returns only common rows present in both the tables. Duplicates rows will not be filtered if duplicates are present in both the tables.

  
  SELECT id,name from setoperators_1
  INTERSECT ALL
  SELECT id,name from setoperators_2;
  
  Output:
  id   name
  ---  -------------
   1    Teradata
   1    Teradata
   2    Database
  

  
  
• MINUS: This operator returns the records which are present in 1st table and not in the 2nd table. Duplicates rows will be filtered and it will return only unique records.

  
  Example 1:
  SELECT id,name from setoperators_1
  MINUS
  SELECT id,name from setoperators_2;
  
  Output:
  id   name
  ---  -------------
   3    Oracle
   4    Vertica
  

  
  

  
  Example 2:
  SELECT id,name from setoperators_2
  MINUS
  SELECT id,name from setoperators_1;
  
  Output:
  id   name
  ---  -------------
   5    DB2
   6    MYSQL
  

• MINUS ALL: This operator returns the records which are present in 1st table and not in the 2nd table. Duplicates rows will not be filtered.

  
  Example 1:
  SELECT id,name from setoperators_1
  MINUS ALL
  SELECT id,name from setoperators_2;
  
  Output:
  id   name
  ---  -------------
   3    Oracle
   4    Vertica
   4    Vertica