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

Some of the available operators in Teradata.
Creating Tables: Creating 2 tables with similar structure to understand the logic in details.
``````
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;
``````

Populating Tables: Inserting relevant data in newly created tables for understanding logic.
``````
Table 1:
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(2,'Database');
INSERT INTO setoperators_2 VALUES(5,'DB2');
INSERT INTO setoperators_2 VALUES(6,'MYSQL');
``````

Detail explanation of each operator

• 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
---  -------------
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 ```
``` ➠ UNION vs UNION ALL: If there are duplicate records after combining datasets then "UNION" will filter duplicates but "UNION ALL" will return all duplicates in output result. ➠ INTERSECT vs INTERSECT ALL: If there are duplicate records after intersecting datasets then "INTERSECT" will filter duplicates but "INTERSECT ALL" will return all duplicates in output result. ➠ MINUS vs MINUS ALL: If there are duplicate records after subtracting 1 datasets then "MINUS" will filter duplicates but "MINUS ALL" will return all duplicates in output result. ```
``` (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); ```
``` window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-90832280-1'); (adsbygoogle = window.adsbygoogle || []).push({ google_ad_client: "ca-pub-7288760298589739", enable_page_level_ads: true }); addingHeaderToPage('teradata-set-operators'); window.onload = function(){ updateGoogleCustomSearchAttributes(); }; ```