Set Operations
Syntax
query_expression
set_operator query_expression
[set_operator query_expression ...]
[QUERY WITH (query_property = value [, ...])];where set_operator is one of:
UNION [ALL]
INTERSECT [ALL]
EXCEPT [ALL]and query_expression is either:
a
SELECTstatement, ora parenthesized set operation
Description
Set operations combine the results of two or more SELECT statements into a single result. The UNION, INTERSECT, and EXCEPT operations are supported, each with an optional ALL quantifier. Set operations can be used anywhere a SELECT statement is accepted as a streaming query, including interactive SELECT statements, CREATE STREAM AS SELECT, CREATE CHANGELOG AS SELECT, and INSERT INTO. The result column names are taken from the first (leftmost) SELECT statement. Column names do not need to match across the SELECT statements; columns are matched by position.
Arguments
set_operator
UNION ALL
Returns all records from all SELECT statements, including duplicates.
UNION
Returns the records from all SELECT statements with duplicates removed.
INTERSECT ALL
Returns the records that appear in all SELECT statements. A record that appears n times in one input and m times in the other is returned min(n, m) times.
INTERSECT
Returns the distinct records that appear in all SELECT statements.
EXCEPT ALL
Returns the records from the first SELECT statement that do not appear in the following ones. A record that appears n times in the first input and m times in the other is returned max(n - m, 0) times.
EXCEPT
Returns the distinct records from the first SELECT statement that do not appear in the following ones.
NOTE: UNION, INTERSECT, EXCEPT, and their ALL variants other than UNION ALL are stateful operations. The query keeps the records it has seen in its state to compute the result, so the state size grows with the number of distinct records. See the flink.sql.state.ttl source property for controlling the state retention time of a source.
Chaining and Grouping
More than two SELECT statements can be combined by chaining set operators:
When no parentheses are used, the operators are passed as-is to the query execution, which applies its own default operator precedence. To control the order of evaluation explicitly, wrap a set operation in parentheses. Parenthesized set operations can be nested to any depth. For example:
QUERY WITH
Optionally, this clause specifies query properties for the whole set operation. It can only be specified once, at the end of the outermost set operation, and not in between statements or inside parenthesized sub-expressions.
Rules for the SELECT Statements
All SELECT statements combined by a set operation must satisfy the following:
All
SELECTstatements must produce the same type of Relation. For example, aSELECTstatement with aGROUP BYclause produces a Changelog, so it cannot be combined with aSELECTstatement that produces a Stream. The result of the set operation is of the same type as its inputs.All
SELECTstatements must produce the same number of columns.The columns at the same position must have compatible data types. Compatible types, such as
INTEGERandBIGINT, are implicitly converted to a common type.If all
SELECTstatements produce Changelogs, they must all have the samePRIMARY KEYcolumns, and they must either all be in the upsert mode or none of them.
Examples
Assume orders_us and orders_eu Streams are created by the following DDL statements:
UNION
The following combines all the orders from both regions into a single Stream. Records that appear in both inputs are returned more than once.
The following returns the distinct customers who placed an order in either region.
INTERSECT
The following returns the distinct customers who placed an order in both regions.
EXCEPT
The following returns the distinct customers who placed an order in the US but never in the EU.
Chaining multiple set operations
Assume orders_apac has the same columns as orders_us and orders_eu. The following combines three Streams.
Set operation on Changelogs and Query properties
The following combines two aggregation results. Since a SELECT statement with a GROUP BY clause produces a Changelog whose PRIMARY KEY is the list of GROUP BY columns, both SELECT statements produce a Changelog with the same PRIMARY KEY column, customerid. The result is a Changelog. The query sets a property to clean up state that has been stale for 5 minutes.
Nested set operations
Assume orders_apac is a Stream with the same columns as orders_us and orders_eu. Moreover, the following additional Stream exists:
The following returns the distinct customers who placed an order in the US and also in either the EU or APAC, excluding the customers who are blocked. The parentheses control the order of evaluation: the innermost UNION is evaluated first, then the INTERSECT with the US orders, and finally the EXCEPT with the blocked customers.
Last updated

