Home > Documentation > UNION Statement

UNION Statement

Syntax

stream_expression_1 UNION stream_expression_2 [UNION ...]

Substitutable Fields

stream_expression_n:

Either the unique identifier (name) of a stream or a SELECT statement. If a stream has the desired output schema (including field names, types, and order), the stream identifier may be used directly. If it is necessary to rename, reorder, or change the type of a field, then the SELECT statement must be used.

... in UNION clause:

Additional input streams of the form: UNION stream_expression_n.

Discussion

UNION takes two or more input streams with identical schemas and produces one output stream with all the tuples from the original streams. Union is not guaranteed to interleave the input tuples in the output stream.

The stream_expression_n entries may be a SELECT statement or the name of a stream. The target list in each SELECT statement or the names of the fields in the stream must be identically named and typed entries. However a target list expression can be used to alter the type and the AS clause can be used to change an entry's name. For example, if a stream_expression_1 tuple includes the fields number_1 int, symbol_1 string(4), while a stream_expression_2 tuple has number_2 int, symbol_2 string(4), n double, these streams cannot be combined with a UNION as the names and number of their fields differ. By managing the target list and using the AS clause, these streams can be made compatible. For example,

SELECT number_1, symbol_1 FROM stream_expression_1
  UNION
    SELECT number_2 AS number_1, symbol_2 AS symbol_1 FROM stream_expression_2

UNION generates a stream and may be used anywhere a stream expression is acceptable. Alternatively, the output could be "captured" in a stream, as illustrated in the following code fragments.

CREATE [OUTPUT] STREAM stream_identifier;
stream_expression_1 UNION stream_expression_2 INTO stream_identifier;

CREATE [OUTPUT] STREAM stream_identifier AS
  stream_expression_1 UNION stream_expression_2;

Or, for an OUTPUT STREAM

stream_expression_1 UNION stream_expression_2
   => CREATE OUTPUT STREAM stream_identifier;