# Set Logic in Confluent Cloud for Apache Flink Confluent Cloud for Apache Flink® enables set logic operations on tables in SQL statements. - [EXCEPT](#flink-sql-set-logic-except) - [EXISTS](#flink-sql-set-logic-exists) - [IN](#flink-sql-set-logic-in) - [INTERSECT](#flink-sql-set-logic-intersect) - [UNION](#flink-sql-set-logic-union) ## Example data The following examples use these tables to show how the different logical operators work. ```sql -- Create tables for the set logic operations. CREATE TABLE t1(chr CHAR); INSERT INTO t1 VALUES('c'), ('a'), ('b'), ('b'), ('c'); CREATE TABLE t2(chr CHAR); INSERT INTO t2 VALUES('d'), ('e'), ('a'), ('b'), ('b'); ``` ## EXCEPT `EXCEPT` and `EXCEPT ALL` return the rows that are found in one table but not the other. - `EXCEPT` returns only distinct rows. - `EXCEPT ALL` doesn’t remove duplicates from the result rows. The following code example shows output from the `EXCEPT` function on tables `t1` and `t2`. ```sql (SELECT chr FROM t1) EXCEPT (SELECT chr FROM t2); ``` Your output should resemble: ```none chr c ``` The following code example shows output from the `EXCEPT ALL` function on tables `t1` and `t2`. ```sql (SELECT chr FROM t1) EXCEPT ALL (SELECT chr FROM t2); ``` Your output should resemble: ```none +----+ | chr| +----+ | c| | c| +----+ ``` **Table types.** `EXCEPT` accepts an [append-only or updating table](../../concepts/dynamic-tables.md#flink-sql-dynamic-tables-updating-table) on either side. The optimizer rewrites `EXCEPT` into a join and a group aggregation, so, like a group aggregation, it always produces an updating table. ## EXISTS ```sql SELECT user, amount FROM orders WHERE product EXISTS ( SELECT product FROM NewProducts ) ``` Returns TRUE if the sub-query returns at least one row. Only supported if the operation can be rewritten in a join and group operation. The optimizer rewrites the `EXISTS` operation into a join and group operation. For streaming queries, the required state for computing the query result might grow infinitely depending on the number of distinct input rows. **Table types.** `EXISTS` accepts an [append-only or updating table](../../concepts/dynamic-tables.md#flink-sql-dynamic-tables-updating-table) on either side, and it always produces an updating table. ## IN Returns TRUE if an expression exists in a table sub-query. The sub-query table must consist of one column. This column must have the same data type as the expression. ```sql SELECT user, amount FROM orders WHERE product IN ( SELECT product FROM NewProducts ) ``` The optimizer rewrites the IN condition into a join and group operation. For streaming queries, the required state for computing the query result might grow infinitely depending on the number of distinct input rows. **Table types.** `IN` accepts an [append-only or updating table](../../concepts/dynamic-tables.md#flink-sql-dynamic-tables-updating-table) on either side, and it always produces an updating table. ## INTERSECT `INTERSECT` and `INTERSECT ALL` return the rows that are found in both tables. - `INTERSECT` returns only distinct rows. - `INTERSECT ALL` doesn’t remove duplicates from the result rows. The following code example shows output from the `INTERSECT` function on tables `t1` and `t2`. ```sql (SELECT chr FROM t1) INTERSECT (SELECT chr FROM t2); ``` Your output should resemble: ```none chr a b ``` The following code example shows output from the `INTERSECT ALL` function on tables `t1` and `t2`. ```sql (SELECT chr FROM t1) INTERSECT ALL (SELECT chr FROM t2); ``` Your output should resemble: ```none chr a b b ``` **Table types.** `INTERSECT` accepts an [append-only or updating table](../../concepts/dynamic-tables.md#flink-sql-dynamic-tables-updating-table) on either side. Like `EXCEPT`, the optimizer rewrites it into a join and a group aggregation, so it always produces an updating table. ## UNION `UNION` and `UNION ALL` return the rows that are found in either table. - `UNION` returns only distinct rows. - `UNION ALL` doesn’t remove duplicates from the result rows. **Table types.** `UNION ALL` accepts an [append-only or updating table](../../concepts/dynamic-tables.md#flink-sql-dynamic-tables-updating-table) on either side and passes each side’s rows through unchanged, so it produces an updating table if either side is updating, or an [append-only table](../../concepts/dynamic-tables.md#flink-sql-dynamic-tables-append-only-table) if both sides are append-only. `UNION` additionally removes duplicates, which, like `DISTINCT`, always produces an updating table. The following code example shows output from the `UNION` function on tables `t1` and `t2`. ```sql (SELECT chr FROM view1) UNION (SELECT chr FROM view2); ``` Your output should resemble: ```none chr c a b d e ``` The following code example shows output from the `UNION ALL` function on tables `t1` and `t2`. ```sql (SELECT chr FROM t1) UNION ALL (SELECT chr FROM t2); ``` Your output should resemble: ```none chr c a b b c d e a b b ``` ## Related content - [Flink SQL Queries](overview.md#flink-sql-queries) - [Flink SQL Functions](../functions/overview.md#flink-sql-functions-overview) - [Statements](../statements/overview.md#flink-sql-statements-overview) #### NOTE This website includes content developed at the [Apache Software Foundation](https://www.apache.org/) under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).