15.00 - Overview of Set Operators - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Overview of Set Operators

The SQL set operators manipulate the results sets of two or more queries by combining the results of each individual query into a single results set.

Teradata SQL Set Operators

Teradata SQL supports the following set operators:

 

Set Operator

Function

INTERSECT

Returns result rows that appear in all answer sets generated by the individual SELECT statements.

MINUS /
EXCEPT

Result is those rows returned by the first SELECT except for those also selected by the second SELECT.

MINUS is the same as EXCEPT.

UNION

Combines the results of two or more SELECT statements.

Set operators appear in query expressions. A query expression is a set of queries combined by the set operators INTERSECT, MINUS/EXCEPT, and UNION.

Syntax for query_term

Syntax for query_factor

Syntax for query_expression

where:

 

Syntax Element …

Specifies …

query_term

SELECT statement

a SELECT statement.

For details, see SQL Data Manipulation Language.

query_expression

an optional expression that might or might not include set operators, other expressions, and an ORDER BY clause.

query_factor

INTERSECT

a set operator returning the result rows appearing in all answer sets.

ALL

an optional keyword, allowing duplicate rows to be returned.

query_expression

UNION

optional set operators specifying how the two or more queries or subqueries are to combine and determine what result rows are required to be returned.

MINUS

EXCEPT

ALL

an optional keyword, allowing duplicate rows to be returned.

ORDER BY

the ORDER BY clause to order the result rows returned.

For details, see SQL Data Manipulation Language.

expression

an expression used in the ORDER BY clause to determine the sort order of returned rows in the result.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

that NULL results are to be listed first.

NULLS LAST

that NULL results are to be listed last.

ANSI Compliance

INTERSECT, EXCEPT, and UNION are ANSI SQL:2011 compliant.

MINUS and the ALL option are Teradata extensions to the ANSI standard.