For the complete documentation index, see llms.txt. This page is also available as Markdown.

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 SELECT statement, or

  • a 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

Operator
Description

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:

  • Each SELECT statement must produce a Stream or a Changelog.

  • All SELECT statements must produce the same type of Relation. For example, a SELECT statement with a GROUP BY clause produces a Changelog, so it cannot be combined with a SELECT statement that produces a Stream. The result of the set operation is of the same type as its inputs.

  • All SELECT statements must produce the same number of columns.

  • The columns at the same position must have compatible data types. Compatible types, such as INTEGER and BIGINT, are implicitly converted to a common type.

  • If all SELECT statements produce Changelogs, they must all have the same PRIMARY KEY columns, 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