> For the complete documentation index, see [llms.txt](https://docs.deltastream.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.deltastream.io/reference/sql-syntax/query/select/set-operations.md).

# Set Operations

Set operations combine or filter the results of `SELECT` statements:

* [#union-intersect-and-except](#union-intersect-and-except "mention") combine the results of two or more `SELECT` statements into a single result.
* [#in-and-exists](#in-and-exists "mention") filter the records of a `SELECT` statement based on the result of a subquery.

## UNION, INTERSECT, and EXCEPT

### Syntax

```sql
query_expression
    set_operator query_expression
    [set_operator query_expression ...]
[QUERY WITH (query_property = value [, ...])];
```

where `set_operator` is one of:

```sql
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](https://docs.deltastream.io/reference/sql-syntax/query/select/from.md#source_properties) for controlling the state retention time of a source.

#### Chaining and Grouping

More than two `SELECT` statements can be combined by chaining set operators:

```sql
SELECT ... UNION ALL SELECT ... UNION ALL SELECT ...
```

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:

```sql
(SELECT ... FROM stream_a)
INTERSECT
((SELECT ... FROM stream_b) UNION ALL (SELECT ... FROM stream_c))
```

#### 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](https://docs.deltastream.io/overview/core-concepts/databases.md#stream) or a [Changelog](https://docs.deltastream.io/overview/core-concepts/databases.md#changelog).
* All `SELECT` statements must produce the same type of [Relation](https://docs.deltastream.io/overview/core-concepts/databases.md#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](https://docs.deltastream.io/reference/sql-syntax/ddl/create-changelog.md#changelog_parameters) or none of them.

## IN and EXISTS

### Syntax

```sql
SELECT ...
FROM relation_reference
WHERE column_name [NOT] IN (subquery)
```

```sql
SELECT ...
FROM relation_reference
WHERE [NOT] EXISTS (subquery)
```

### Description

`IN` and `EXISTS` predicates filter the records of a streaming query based on the result of a subquery in the `WHERE` clause:

| Predicate    | Description                                                                                                                                                                                                                            |
| ------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `IN`         | Returns the records whose column value appears in the result of the subquery. The subquery must return a single column.                                                                                                                |
| `NOT IN`     | Returns the records whose column value does not appear in the result of the subquery. If the column value is `NULL`, or the subquery result contains a `NULL` value, the predicate evaluates to `NULL` and the record is filtered out. |
| `EXISTS`     | Returns the records for which the subquery returns at least one record.                                                                                                                                                                |
| `NOT EXISTS` | Returns the records for which the subquery returns no records.                                                                                                                                                                         |

The subquery can reference columns of the enclosing query (a correlated subquery). For example, `WHERE b.userid = u.userid` inside the subquery compares a column of the subquery's source with a column of the outer query's source. `IN` and `EXISTS` predicates can be combined with other conditions in the `WHERE` clause using `AND` and `OR`, and can be nested inside other subqueries.

`IN` and `EXISTS` predicates don't change the Relation type of the query's input. For a query that only filters and projects the source in its `FROM` clause, the result has the same Relation type as that source:

* If the source is a [Stream](https://docs.deltastream.io/overview/core-concepts/databases.md#stream), the result is a Stream. It can't be written into a Changelog.
* If the source is a [Changelog](https://docs.deltastream.io/overview/core-concepts/databases.md#changelog), the result is a Changelog with the same `PRIMARY KEY` columns as the source, renamed if they're aliased in the `SELECT` list. All the source's `PRIMARY KEY` columns must be in the `SELECT` list. The result can't be written into a Stream.

Other operations in the same query can still change the result type. For example, a `GROUP BY` aggregation over a Stream produces a Changelog.

{% hint style="warning" %}
Unlike `NOT EXISTS`, `NOT IN` doesn't return a record when the subquery result contains a `NULL` value, even if the record's column value doesn't appear in the result. For example, if the subquery returns `User_1` and `NULL`, a record with `User_3` is filtered out. To exclude only the matching records, use `NOT EXISTS` with a correlated condition, or filter out `NULL` values in the subquery. See `NOT IN` in [Built-in Functions](https://docs.deltastream.io/reference/sql-syntax/query/functions/built-in-functions.md).
{% endhint %}

**NOTE:** `IN` and `EXISTS` predicates are stateful operations. The query keeps the records it has seen from both the source and the subquery in its state to compute the result. See the `flink.sql.state.ttl` [source property](https://docs.deltastream.io/reference/sql-syntax/query/select/from.md#source_properties) for controlling the state retention time of a source.

## Examples

Assume `orders_us` and `orders_eu` Streams are created by the following DDL statements:

```sql
CREATE STREAM orders_us (
  orderid VARCHAR,
  customerid VARCHAR,
  amount BIGINT
) WITH (
  'store' = 'kafka_store',
  'topic' = 'orders_us',
  'value.format' = 'json'
);

CREATE STREAM orders_eu (
  orderid VARCHAR,
  customerid VARCHAR,
  amount BIGINT
) WITH (
  'store' = 'kafka_store',
  'topic' = 'orders_eu',
  'value.format' = 'json'
);
```

### 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.

```sql
CREATE STREAM orders_unified AS
 SELECT orderid, customerid, amount FROM orders_us
  UNION ALL
 SELECT * FROM orders_eu;
```

The following returns the distinct customers who placed an order in either region.

```sql
SELECT customerid FROM orders_us
 UNION
SELECT customerid FROM orders_eu;
```

### INTERSECT

The following returns the distinct customers who placed an order in both regions.

```sql
SELECT customerid FROM orders_us
 INTERSECT
SELECT customerid FROM orders_eu;
```

### EXCEPT

The following returns the distinct customers who placed an order in the US but never in the EU.

```sql
SELECT customerid FROM orders_us
 EXCEPT
SELECT customerid FROM orders_eu;
```

### Chaining multiple set operations

Assume `orders_apac` has the same columns as `orders_us` and `orders_eu`. The following combines three Streams.

```sql
SELECT orderid, customerid, amount FROM orders_us
 UNION ALL
SELECT orderid, customerid, amount FROM orders_eu
 UNION ALL
SELECT orderid, customerid, amount FROM orders_apac;
```

### 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.

```sql
CREATE CHANGELOG customer_totals AS
  SELECT customerid, SUM(amount) AS total FROM orders_us GROUP BY customerid
   UNION
  SELECT customerid, SUM(amount) AS total FROM orders_eu GROUP BY customerid
  QUERY WITH ('state.ttl.millis' = '300000');
```

### 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:

```sql
CREATE STREAM blocked_customers (
  customerid VARCHAR,
  reason VARCHAR
) WITH (
 'store' = 'kafka_store',
 'topic' = 'blocked_customers',
 'value.format' = 'json'
);
```

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.

```sql
CREATE STREAM eligible_customers AS
  (
    SELECT customerid FROM orders_us
     INTERSECT
    (
      SELECT customerid FROM orders_eu
       UNION
      SELECT customerid FROM orders_apac
    )
  )
   EXCEPT
  SELECT customerid FROM blocked_customers;
```

### IN

Assume `pageviews` and `users` Streams are created by the following DDL statements:

```sql
CREATE STREAM pageviews (
  viewtime BIGINT,
  userid VARCHAR,
  pageid VARCHAR
) WITH (
  'store' = 'kafka_store',
  'topic' = 'pageviews',
  'value.format' = 'json'
);

CREATE STREAM users (
  userid VARCHAR
) WITH (
  'store' = 'kafka_store',
  'topic' = 'users',
  'value.format' = 'json'
);
```

The following returns the pageviews of the known users. A pageview record is returned only if its `userid` appears in the `users` Stream. The subquery is correlated with the outer query through the `u.userid = p.userid` condition. The result is a Stream.

```sql
CREATE STREAM known_user_pageviews AS
  SELECT p.userid AS userid, p.pageid AS pageid
  FROM pageviews p
  WHERE p.userid IN (
    SELECT u.userid FROM users u
    WHERE u.userid = p.userid);
```

For example, if `users` contains the records for `User_1` and `User_2`, the pageviews of `User_1` and `User_2` are returned, and the pageviews of `User_3` are filtered out.

### NOT EXISTS

Assume `users_log` is a Changelog and `blocked_users` is a Stream, created by the following DDL statements:

```sql
CREATE CHANGELOG users_log (
  userid VARCHAR,
  gender VARCHAR,
  PRIMARY KEY (userid)
) WITH (
  'store' = 'kafka_store',
  'topic' = 'users_log',
  'value.format' = 'json'
);

CREATE STREAM blocked_users (
  userid VARCHAR
) WITH (
  'store' = 'kafka_store',
  'topic' = 'blocked_users',
  'value.format' = 'json'
);
```

The following returns the users that are not blocked. A `users_log` record is returned only if no record in `blocked_users` has the same `userid`. Since the source is a Changelog, the result is a Changelog whose `PRIMARY KEY` is `uid`, the alias of the source's `PRIMARY KEY` column `userid`. If a matching record arrives in `blocked_users` after a user has been returned, that user is removed from the result.

```sql
CREATE CHANGELOG active_users AS
  SELECT u.userid AS uid, u.gender
  FROM users_log u
  WHERE NOT EXISTS (
    SELECT 1 FROM blocked_users b
    WHERE b.userid = u.userid);
```
