Subqueries¶

A subquery is a query nested inside another query. Subqueries can appear in various parts of a SQL statement and can be correlated (referencing columns from outer queries) or non-correlated (independent of outer queries).

Subquery Types¶

EXISTS Subqueries¶

Tests whether a subquery returns any rows:

SELECT columns FROM table WHERE EXISTS (subquery)

Subqueries in FROM Clause¶

A subquery can appear in the FROM clause as a derived table:

SELECT columns FROM (subquery) AS alias

Correlated Subqueries¶

A subquery that references columns from the outer query:

SELECT columns FROM table1 AS t1
WHERE EXISTS (SELECT * FROM table2 WHERE table2.col = t1.col)

Array Unnesting¶

FDB supports PartiQL-style array unnesting:

SELECT columns FROM table, table.array_column AS alias

Examples¶

Setup¶

For these examples, assume we have the following tables:

CREATE TABLE a(ida INTEGER, x INTEGER, PRIMARY KEY(ida))
CREATE TABLE x(idx INTEGER, y INTEGER, PRIMARY KEY(idx))
CREATE TABLE b(idb INTEGER, q INTEGER, r INTEGER, PRIMARY KEY(idb))

CREATE TYPE AS STRUCT s(f INTEGER)
CREATE TABLE r(idr INTEGER, nr s ARRAY, PRIMARY KEY(idr))

INSERT INTO a VALUES (1, 1), (2, 2), (3, 3)
INSERT INTO x VALUES (4, 10), (5, 20), (6, 30)
INSERT INTO b VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300)
INSERT INTO r VALUES
    (1, [(11), (12), (13)]),
    (2, [(21), (22), (23)]),
    (3, [(31), (32), (33)])

Non-Correlated EXISTS Subquery¶

Check if a condition exists in a table, independent of the outer query:

SELECT ida FROM a WHERE EXISTS (SELECT ida FROM a WHERE ida = 1)

`ida`
`1`
`2`
`3`

Since the subquery SELECT ida FROM a WHERE ida = 1 returns at least one row (where ida = 1), the EXISTS condition is true for all rows in the outer query.

Correlated Subquery in FROM Clause¶

Use a correlated subquery as a derived table:

SELECT x, sq.idr, sq.nr
FROM a, (SELECT * FROM r WHERE r.idr = a.x) sq

`x`	`idr`	`nr`
`1`	`1`	[{"f": 11}, {"f": 12}, {"f": 13}]
`2`	`2`	[{"f": 21}, {"f": 22}, {"f": 23}]
`3`	`3`	[{"f": 31}, {"f": 32}, {"f": 33}]

The subquery (SELECT * FROM r WHERE r.idr = a.x) is correlated because it references a.x from the outer query.

Array Unnesting with PartiQL¶

Unnest an array column using PartiQL syntax:

SELECT idr FROM r, r.nr AS nest WHERE nest.f = 23

`idr`
`2`

This query iterates over the nr array in each row of r and returns rows where the nested struct’s f field equals 23.

You can also use a subquery for array unnesting:

SELECT idr FROM r, (SELECT * FROM r.nr) AS nest WHERE nest.f = 23

`idr`
`2`

Correlated Subquery with GROUP BY¶

Use a correlated subquery with aggregation:

SELECT x FROM a
WHERE EXISTS (
    SELECT a.x, MAX(idb) FROM b
    WHERE q > a.x
    GROUP BY q
)

`x`
`1`
`2`
`3`

The subquery references a.x from the outer query in both the SELECT list and WHERE clause. For each row in a, the subquery finds rows in b where q is greater than the current a.x value and groups them by q.

Correlation in Aggregation Functions¶

Correlations can also appear inside aggregate functions:

SELECT x FROM a
WHERE EXISTS (
    SELECT MAX(a.x), MAX(idb) FROM b
    WHERE q > x
    GROUP BY q
)

`x`
`1`
`2`
`3`

Here, MAX(a.x) aggregates the correlated column a.x, which has the same value for all rows processed by each instance of the subquery.

Important Notes¶

Subqueries in the FROM clause create a new query scope. Columns from the subquery are accessed via the subquery alias:

SELECT sq.column FROM (SELECT column FROM table) AS sq

When a column reference is not qualified with a table alias, the semantic analyzer first tries to resolve it in the current query block. If not found, it looks in the outer query blocks:

-- In the subquery, 'x' refers to column x from table a, not table x
SELECT idx FROM x WHERE EXISTS (SELECT x FROM a WHERE ida = 1)