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:

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.

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