UNION Statement

< Previous		Next >

Syntax

stream_expression_1 UNION stream_expression_2 [UNION ...]
  [ERROR INTO stream_identifier];

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 can be used directly. If it is necessary to rename, reorder, or change the type of a field, then the SELECT statement must be used.

ERROR INTO Clause

You can append an ERROR INTO clause just before the closing semicolon. The StreamSQL ERROR INTO clause is analogous to the Enable Error Output Port check box for operators and adapters in EventFlow applications.

Use ERROR INTO with the name of a stream, which must already exist. This sets up an Error Port for this operator, which is much like a local catch mechanism for errors from this operator.

See Using Error Ports and Error Streams for a discussion of StreamBase error handling mechanisms.

Discussion

UNION takes two or more input streams with compatible schemas and produces one output stream with all the tuples from the original streams. The union operation is not order sensitive (that is, tuples do not have to be in any order). If you want to order on a field by using a criterion such as time, consider using a MERGE operation.

To be compatible, schemas can be different as long as there are no fields with the same name but different types. Schemas do not have to have the same field order. If schemas are different, when an input tuple arrives on one stream and is missing fields that exist in another stream, the union fills the missing fields in with null fields. Union is not guaranteed to interleave the input tuples in the output stream.

Note

The StreamSQL UNION statement cannot enforce a strict union, where schemas must match exactly. However, the Union operator in EventFlow applications does have the option, Force strictly matching schemas.

UNION generates a stream and can be used anywhere a stream expression is acceptable. As an alternative, 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;