Besides simple queries, MINUS or EXCEPT can be used within the following operations:
- Derived tables 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 runs to produce a set of rows. The exception must include the same number of columns from each table in each SELECT statement and the data types of these columns must be compatible. The result sets are 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: Effect of Explicit NULLs on Data Type of a UNION.
However, column data defined as NULL has neither value nor data type and evaluates like any other null in a scalar expression.
MINUS/EXCEPT Duplicate Row Handling
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 multiple-statement query.