UNION Operator

Combines the result of two or more SELECT clauses into a stream or window.

Syntax

{select_clause} UNION {select_clause} [ UNION ...]

Components

select_clause

A SELECT clause.

Usage

The union operation may produce a stream, delta stream, or a window.

If the input to a union that produces a window is a stream, you must perform an aggregation operation.
When a union joins two SELECT clauses, the schema of the columns selected in the two SELECT clauses must match.
Ensure that a record with a particular key value is not produced by more than one input node. Otherwise, you may see duplicate rows or invalid updates.
To be compatible, the schema for all the nodes subject to the union must have the same datatypes. However, the column names in the schemas may be different. In this case, the column names from the first SELECT clause are used in the schema deduction.
If the SELECT statement is not a direct copy from the source, intermediate nodes are created. The compiler attempts to create delta streams or streams, but must generate windows in cases when aggregation or a KEEP clause.
DECLARE blocks are not allowed for union operations.
A node created by a union operation can have a KEEP clause and an AGING clause if the target is a window.

Restrictions

The inputs to a union can be any combination of streams, delta streams, and windows.
The inputs to a union delta stream can be a delta stream or a window, but not a stream.
The inputs to a union window can be any combination of streams, delta streams, and windows (provided the querying involving a stream has a GROUP BY clause).
A union stream or delta stream cannot have a GROUP BY clause specified in any of the underlying queries.

Examples

This example uses a union operation to produce an output stream:

CREATE SCHEMA MySchema (a0 integer, a1 STRING, a2 string);
CREATE SCHEMA MySchema2 (a0 integer, a1 STRING, a2 string);

CREATE INPUT STREAM InputStream1 SCHEMA MySchema;
CREATE INPUT STREAM InputStream2 SCHEMA MySchema2;
CREATE INPUT STREAM InputStream3 SCHEMA MySchema2;

CREATE OUTPUT STREAM UnionStream1 AS SELECT * FROM InputStream1 UNION 
SELECT * FROM InputStream2;

Using a union operation to produce an output window:

CREATE OUTPUT WINDOW UnionWindow1
PRIMARY KEY DEDUCED 
AS
	SELECT in1.a0, min(in1.a1) a1, min(in1.a2) a2 
	FROM InputStream1 in1 GROUP BY in1.a0
	UNION
	SELECT in2.a0, min(in2.a1) a1, min(in2.a2) a2 
	FROM InputStream2 in2 GROUP BY in2.a0;

Note: Since the source is a stream and target is a window, an aggregation is specified, as is required.

This example uses a union operation to produce a delta stream:

CREATE DELTA STREAM Union1 PRIMARY KEY DEDUCED
AS
	SELECT * FROM Stream1
	UNION
	SELECT a.col1, a.col2, a.col3 FROM DeltaStream1 a WHERE a.col1 > 10
	UNION
	SELECT a.a, sum(a.b), max(a.c) FROM Window2 GROUP BY a.a