# 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
4   Vertica
4   Vertica
2   Database
5   DB2
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
---  -------------
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
---  -------------
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.