ReadVitamin

What are SET operators?

Written by admin in Monday, August 13th 2007 under IT

SET operators are used to manipulate the results sets of two or more queries by combining the results of each individual query into a single results set. In Teradata we have ANSI standard SET operators: INTERSECT, UNION, and EXCEPT, as well as the Teradata extensions to ANSI as “MINUS”. One good thing to note about these operators is that the separate SELECT statements run in parallel, prior to being combined. Additionally, they all run as a single transaction. Here is the way the query is written.

SELECT one_statement

UNION [ALL]

SELECT another_statement

MINUS [ALL]

SELECT one_more_statement

INTERSECT [ALL]

SELECT last_statement;

Rules for SET Operations.

1. Both the SELECT statements must have the same number of columns with the same attributes.

2. Casting and formatting has to be done in the 1^st SELECT statement.

3. Note that there is only one semi colon used at the end of the last SELECT statement.

4. While using multiple SET Operators, follow the order of precedence.

a. INTERSECT operation is performed first

b. Then it is the turn of the UNION operator(s)

c. Then EXCEPT and at the last MINUS operation is performed.

5. By default the duplicate rows are eliminated from the answer sets but if at all you want you can get the same by the use of another operator ‘ALL‘.

Popularity: 9% [?]