Combining query expressions with UNION

---

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

                   [UNION [ALL | 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".

UNION

The UNION clause combines two query expressions. The derived table contains all the rows that occur in the first or second derived table. You can combine more than two derived tables if you use the UNION clause several times.

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

• • The derived tables of both UNION operands must have the same number of columns and the data types of 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 UNION".

  • If the corresponding columns in both source tables have the same names, the derived column is given this name. Otherwise, the name of the derived column is undefined.

  • Only atomic columns may be selected.

Query expressions combined with the UNION clause cannot be updated.

ALL

Duplicate rows in the derived table are retained.

DISTINCT

Duplicate rows are removed. If you do not specify ALL or DISTINCT, the default value is DISTINCT.

In contrast to the SELECT expression, the default value for UNION is DISTINCT. As it can be complicated to remove duplicate rows, the setting ALL is recommended for UNION if the application can dispense with removing duplicate rows.

Data type of the derived column for UNION

If two query expressions are combined with UNION, the data type of the derived column is determined by applying the following rules:

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

Examples

Determine all order numbers whose associated order value is at least 10,000 euros or whose target date is before the specified date.

SELECT order_num FROM service GROUP BY order_num
   HAVING SUM(service_total * service_price * (1 + vat)) > = 10000.00
   UNION DISTINCT
   SELECT order_num FROM orders WHERE target <= DATE '<date>'

The names of those companies are to be determined for which order documentation has already been archived or services have already been provided prior to the specified date:

SELECT c.company FROM customers c, orders o WHERE c.cust_num = o.cust_num
   AND o.order_num IN 
   (SELECT o.order_num FROM orders o WHERE o.order_status > 4
                 UNION
                 SELECT DISTINCT s.order_num FROM service s 
                        WHERE s.service_date < DATE'<date>')

The UNION expression in the subquery produces a derived table containing the order numbers 200 and 211.

The derived table is thus:

company

JIKO GmbH

Foreign Ltd