Query Expression (Set Operator) Function
UNION Combines the results of two or more SELECT statements.
INTERSECT Returns result rows that appear in all answer sets generated by the individual SELECT statements. INTERSECT is flagged as not entry level ANSI, when the SQL flagger is enabled.
EXCEPT (MINUS) Result is those rows returned by the first SELECT except for those also selected by the second SELECT. MINUS is flagged as non-ANSI when the SQL flagger is enabled. MINUS is the same as EXCEPT, and is an extension to ANSI.

Query term:

Syntax ------,- SELECT— statement

----(query_expr)-----

HH01A060

Query factor:

----1------query_term-------------------------------------,--

I—query_factor— INTERSECT-,------------r-query_term —I

I—ALL—I

HH01A061

Teradata RDBMS for UNIX SQL Reference

7-43
Queries: The SELECT Statement

Query Expressions (Set Operators)

Query expr:

—|- query_factor------

I— query_expr —

UNION-

— MINUS — —EXCEPT—

- query_factor -

l—(query_expr) — ORDER BY-L expr -

—ASC--DESC-

HH01A062

where:

Syntax Element ... Is . . .
Query term
SELECT statement Refer to “SELECT” in this chapter.
query_expr an optional expression which may or may not include set operators, other expressions, and an ORDER BY clause.
Query factor
INTERSECT a set operator returning the result row appearing in all answer sets.
ALL an optional keyword, allowing duplicate rows to be returned.

Query expr

UNION MINUS EXCEPT an optional set operators specifying how the two or more queries are to interfunction and determined by what result rows are required to be returned.
ALL an optional keyword, allowing duplicate rows to be returned.
ORDER BY the ORDER BY clause to order the result rows returned. Refer to “ORDER BY Clause” in this chapter.
expr an expression used in the ORDER BY clause to determine the sort order of returned rows in the result.
ASC DESC The sort order for the returned result rows. ASC is the default.

7-44

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

Query Expressions (Set Operators)

By default, duplicate rows are not returned. Use the ALL option to Usage Notes allow duplicate rows to be returned.

The query expressions (set operators) are only used with the SELECT statement. The SELECT statements connected by set operators can include the normal options for SELECT, except the WITH clause. A further restriction, is that a query expression can include only one ORDER BY specification, at the end.

A query expression including the UNION operator, for example, may contain as the left operand of the UNION, another query expression. Examples of query expressions involving several Set Operators are shown below.

The precedence for processing set operators is INTERSECT first, Precedence 7 then UNION, then MINUS from left to right

The Teradata RDBMS first performs the INTERSECT of SELECT stmnt_3 and SELECT stmnt_4, then the UNION of SELECT stmnt_1 and SELECT stmnt_2. From the UNION result, the INTERSECT result is deleted.

Precedence can be overridden by using parentheses. The operations Using Parentheses 7 in the parentheses are performed first.

For example, when the following form is used:

((SELECT stmnt_1 UNION SELECT stmnt_2)

MINUS

(SELECT stmnt_3 UNION SELECT stmnt_4))

MINUS

SELECT stmnt_5 INTERSECT SELECT stmnt 6

Teradata RDBMS for UNIX SQL Reference

7-45
Queries: The SELECT Statement

Query Expressions (Set Operators)

Retaining Duplicate Rows Using ALL

Example 1

Stage Process
1 UNION of SELECT stmnt_1 and SELECT stmnt_2
2 UNION of SELECT stmnt_3 and SELECT stmnt-4
3 result of the second UNION is subtracted from the result of the first UNION
4 INTERSECT of SELECT stmnt_5 and SELECT stmnt_6 in performed
5 INTERSECT result is subtracted from the remainder of the UNION operations.

Unless the ALL option is used, duplicate rows are eliminated from the final result. The ALL option retains duplicate rows for the result set to which it is applied.

the ALL option is flagged as non-ANSI when the SQL flagger is enabled.

The following query returns duplicate rows for each result set, including the final ELECT stmnt_1: