Combining query expressions with EXCEPT

---

query_expression ::= { select_expression | TABLE table | join_expression | ( query_expression ) }

                   [EXCEPT [DISTINCT] query_expression ]

---

select_expression

SELECT expression (see section "SELECT expression")

TABLE table

Table query, see section "TABLE - Table query".

join_expression

Join expression (see section "Join expression")

(query_expression)

Subquery, see section "Subquery".

EXCEPT

The EXCEPT operation is similar to the difference between two sets in set theory. The derived table contains all rows from the first table which do not exist in the second table.

If you want to combine query expressions with EXCEPT, the following conditions must be met:

• • The derived tables of both EXCEPT operands must have the same number of columns.

  • The data types of the corresponding columns must be compatible (see section "Compatibility between data types").
    The data type of a derived column is determined by applying the rules described in the "Data type of the derived column for EXCEPT".

DISTINCT

Duplicate rows are removed from the derived table. DISTINCT is the default value.

Data type of the derived column for EXCEPT

If two query expressions are combined with EXCEPT, the data type of the derived column is determined by applying the following rules (as with UNION).

• Both source columns are of the type NCHAR:
  The derived column is of the type NCHAR with the longer of the two lengths.

• One source column is of the type VARCHAR and the other source column is of the type CHAR or VARCHAR:
  The derived column is of the type NVARCHAR with the greater length or greater maximum length.

• Both source columns are of the type NCHAR:
  The derived column is of the type NCHAR with the longer of the two lengths.

• One source column is of the type NVARCHAR and the other source column is of the type NCHAR or NVARCHAR:
  The derived column is of the type NVARCHAR with the greater length or greater maximum length.

• Both source columns are an integer or fixed-point type (INT, SMALLINT, NUMERIC, DEC):
  The derived column is of type integer or fixed-point.

  • The number of digits to the right of the decimal point is the greater of the two values of the source columns.

  • The total number of significant digits is the greater of the two values plus the greater of the two values for the number of digits after the decimal point of the source column with a maximum number of 31 digits.

• One source column is of a floating-point type (REAL, DOUBLE, FLOAT), the other is of any numeric data type:
  The derived column is of the type DOUBLE PRECISION.

• Both source columns have a date and time data type:
  Both columns must have the same date and time data type and the derived column also has this data type.

Example

Determine all customer numbers from which orders are currently planned or agreed contractually.

SELECT cust_num FROM customers
   EXCEPT DISTINCT
   SELECT cust_num FROM orders WHERE order_stat < 3