Subqueries

DataFusion supports EXISTS, NOT EXISTS, IN, NOT IN and Scalar Subqueries.

The examples below are based on the following table.

❯ select * from x;
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1        | 2        |
+----------+----------+

EXISTS

The EXISTS syntax can be used to find all rows in a relation where a correlated subquery produces one or more matches for that row. Only correlated subqueries are supported.

❯ select * from x y where exists (select * from x where x.column_1 = y.column_1);
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1        | 2        |
+----------+----------+
1 row in set.

NOT EXISTS

The NOT EXISTS syntax can be used to find all rows in a relation where a correlated subquery produces zero matches for that row. Only correlated subqueries are supported.

❯ select * from x y where not exists (select * from x where x.column_1 = y.column_1);
0 rows in set.

IN

The IN syntax can be used to find all rows in a relation where a given expression's value can be found in the results of a correlated subquery.

❯ select * from x where column_1 in (select column_1 from x);
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1        | 2        |
+----------+----------+
1 row in set.

NOT IN

The NOT IN syntax can be used to find all rows in a relation where a given expression's value can not be found in the results of a correlated subquery.

❯ select * from x where column_1 not in (select column_1 from x);
0 rows in set.

Scalar Subquery

A scalar subquery can be used to produce a single value that can be used in many different contexts in a query. Here is an example of a filter using a scalar subquery. Only correlated subqueries are supported.

❯ select * from x y where column_1 < (select sum(column_2) from x where x.column_1 = y.column_1);
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1        | 2        |
+----------+----------+
1 row in set.