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 / |
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.