MINUS/EXCEPT Operator - Teradata Database

MINUS/EXCEPT Operator

Purpose  

Returns the results rows that appear in query_expression_1 and not in query_expression_2.

Syntax  

where:

Syntax element …	Specifies …
query_expression_1	a complete SELECT statement whose results table is to be MINUSed with query_expression_2.
ALL	that duplicate rows are to be retained for the MINUS operation.
query_expression_2	a complete SELECT statement to be MINUSed from query_expression_1.

ANSI Compliance

EXCEPT is ANSI SQL:2011 compliant.

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

Usage Notes  

Besides simple queries, MINUS or EXCEPT can be used within the following operations:

Derived tables

Note: You cannot use the HASH BY or LOCAL ORDER BY clauses in derived tables with set operators.

Subqueries

INSERT … SELECT statements

View definitions

MINUS and EXCEPT cannot be used within the following operations:

SELECT AND CONSUME statements.

WITH RECURSIVE clause

CREATE RECURSIVE VIEW statements

Each query connected by MINUS or EXCEPT is executed to produce a result consisting of a set of rows. The exception must include the same number of columns from each table in each SELECT statement (more formally, they must be of the same degree), and the data types of these columns should be compatible. All the result sets are then combined into a single result set, which has the data types of the columns specified in the first SELECT statement in the exception.

MINUS/EXCEPT and NULL

When you specify an explicit NULL for any exception operation, its data type is INTEGER. For an example of this principle using the UNION operator, see “Example 5: Effect of Explicit NULLs on Data Type of a UNION” on page 1173.

On the other hand, column data defined as NULL has neither value nor data type and evaluates like any other null in a scalar expression.

Duplicate Rows

Unless the ALL option is used, duplicate rows are eliminated from the final result.

If the ALL option is specified, duplicate rows are retained. The ALL option can be specified for as many MINUS operators as are used in a multistatement query.