Teradata Set Operator
SET operators combine results from multiple SELECT statements. This may look similar to Joins, but joins combines columns from various tables, whereas SET operators combine rows from multiple rows.
Rules for Set Operator
Here are the following rules to specify the Set operator, such as:
- The number of columns from each SELECT statement should be the same.
- The data types from each SELECT must be compatible.
- ORDER BY should be included only in the final SELECT statement.
Teradata SQL Set Operators
Teradata SQL supports the following set operators:
Set Operator | Function |
---|---|
INTERSECT | It returns result in rows that appear in all answer sets generated by the individual SELECT statements. |
MINUS / EXCEPT | The result is those rows returned by the first SELECT except for those also selected by the second SELECT. |
UNION | It combines the results of two or more SELECT statements. |
1. UNION
The UNION statement is used to combine results from multiple SELECT statements. It ignores duplicates.
Syntax
Following is the basic syntax of the UNION statement.
Example
Consider the following student table as T1 and attendance table as T2.
RollNo | FirstName | LastName | BirthDate |
---|---|---|---|
1001 | Mike | Richard | 1/2/1996 |
1002 | Robert | Williams | 3/5/1995 |
1003 | Peter | Collin | 4/1/1994 |
1004 | Alexa | Stuart | 11/6/1995 |
1005 | Robert | Peterson | 12/1/1997 |
RollNo | present | Absent | % |
---|---|---|---|
1001 | 200 | 20 | 90% |
1002 | 160 | 60 | 72% |
1003 | 150 | 70 | 68% |
1004 | 210 | 10 | 95% |
The following UNION query combines the RollNo value from both T1 and T2 tables.
When the query is executed, it gives the following output, such as:
RollNo 1001 1002 1003 1004 1005
2. UNION ALL
UNION ALL statement is similar to the UNION statement. It combines results from multiple tables, including duplicate rows.
Syntax
Following is the basic syntax of the UNION ALL statement.
Example
Following is an example for UNION ALL statement.
When the above query is executed, it produces the following output. And it returns the duplicates also.
RollNo 1001 1002 1003 1004 1005 1001 1002 1003 1004
3. INTERSECT
INTERSECT command is also used to combine results from multiple SELECT statements.
It returns the rows from the first SELECT statement that has a corresponding match in the second SELECT statement.
Syntax
Following is the basic syntax of the INTERSECT statement.
Example
Following is an example of the INTERSECT statement. It returns the RollNo values that exist in both tables.
When the above query is executed, it returns the following records. RollNo 1005 is excluded since it doesn’t exist in the T2 table.
4. MINUS/EXCEPT
MINUS/EXCEPT commands combine rows from multiple tables and return the rows, which are in the first SELECT but not in the second SELECT. They both return the same results.
Syntax
Following is the basic syntax of the MINUS statement.
Example
Following is an example of a MINUS statement.
When this query is executed, it gives the following output.
RollNo 1005