Result Data Types

This section describes the syntax rules for, and the resulting data types of, UNION or UNION ALL operations specified in a query-expression (see SELECT) and CASE expressions, see CASE Expression.

The data type of all specified expressions must be comparable.

If any of the specified expressions is a variable-length (national) character string, then the data type of the result will be variable-length (national) character with maximum length equal to the largest of the specified expressions.

If all specified expressions are fixed-length (national) character strings, then the data type of the result will be a fixed-length (national) character string with a length equal to the maximum length of the largest of the specified fixed-length character string values.

If any of the specified expressions is variable-length binary, then the data type of the result will be variable-length binary with maximum length equal to the maximum length of the largest of the specified expressions.

If all specified expressions are fixed-length binary, then the data type of the result will be fixed-length binary with the same length.

If all specified expressions are exact numeric, then the data type of the result will be exact numeric with precision and scale equal to the maximum precision and scale of the specified expressions.

If any of the specified expressions is approximate numeric, then the data type of the result will be approximate numeric with precision equal to the maximum precision of the specified expressions.

If two numeric data types are specified, the precision and scale of the result is determined by the rules in the table below and which are described in the points that follow:


	`FLOAT(p")`	`INTEGER(p")`	`DECIMAL(p",s")`
`FLOAT(p')`	`FLOAT(p)` ¹	`FLOAT(p)` a	`FLOAT(p)` a
`INTEGER(p')`	`FLOAT(p)` a	`INTEGER(p)` a	`DECIMAL(p,s)` ²
`DECIMAL(p',s')`	`FLOAT(p)` a	`DECIMAL(p,s)` b	`DECIMAL(p,s)` b

1p = max(p',p")

2p = min(45, max(p'-s',p"-s")+max(s',s"))
s = max(s',s")

If either of the specified expressions is floating point, the result is floating point. The precision of the result is the highest operand precision.

Thus:

FLOAT UNION REAL gives FLOAT

FLOAT UNION INTEGER gives FLOAT

REAL UNION SMALLINT gives REAL.

If both the specified expressions are integer, the result is integer. The precision of the result is the highest operand precision.

Thus:

INTEGER UNION SMALLINT gives INTEGER

INTEGER UNION BIGINT gives BIGINT.

If both the specified expressions are decimal, or one is decimal and the other is integer, the result is decimal. For expressions mixing decimal and integer operands, INTEGER(p) is treated as DECIMAL(p,0).

The number of positions to the left of the decimal point (i.e. the difference between precision and scale) in the result is the greatest number of positions in either operand. The scale of the result is the greatest scale of the operands. The precision may not exceed 45.

Thus:

SMALLINT UNION DECIMAL(10,4) gives DECIMAL(10,4)

INTEGER UNION DECIMAL(10,4) gives DECIMAL(14,4)

DECIMAL(9,2) UNION DECIMAL(6,4) gives DECIMAL(9,4).

For INTERVAL operands, see Interval, the interval precision of the result is the combined interval precision of the two operands, the scale (seconds precision) is the greatest of the two operands and the leading precision of the result is the greatest of the two operands, expressed in terms of the most significant field of the result.

Thus:

DAY TO HOUR UNION MINUTE TO SECOND gives DAY TO SECOND

HOUR TO SECOND(2,2) UNION MINUTE TO SECOND(1,6)gives HOUR TO SECOND(2,6)

DAY(2) TO HOUR UNION HOUR(6) TO MINUTE gives DAY(5) TO MINUTE.

Standard Compliance


Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.

Result Data Types

Standard Compliance

Standard

Compliance

Comments