There are four types of expression subqueries, i.e. subqueries that are used as
expressions. Expression subqueries return NULL or a single value, as opposed to
a column or table, and must be surrounded by parentheses. For a fuller
discussion of subqueries, see
Subqueries.
| Type of Subquery | Result Data Type | Description |
|---|---|---|
| Scalar | Any type T | A subquery in parentheses inside an expression (e.g. in the
SELECT list or WHERE clause) is interpreted as a
scalar subquery. The SELECT list in a scalar subquery must have
exactly one field. If the subquery returns exactly one row, that single value is
the scalar subquery result. If the subquery returns zero rows, the scalar
subquery value is NULL. If the subquery returns more than one row, the query
fails with a runtime error. When the subquery is written with SELECT AS
STRUCT , it can include multiple
columns, and the returned value is the constructed STRUCT. Selecting
multiple columns without using SELECT AS is an error. |
| ARRAY | ARRAY | Can use SELECT AS STRUCT to
build arrays of structs, and conversely, selecting multiple columns
without using SELECT AS is an error. Returns an empty ARRAY if the
subquery returns zero rows. Never returns a NULL ARRAY. |
| IN | BOOL | Occurs in an expression following the IN operator. The subquery must produce
a single column whose type is equality-compatible with the expression on the
left side of the IN operator. Returns FALSE if the subquery returns zero rows.
x IN () is equivalent to x IN (value, value, ...)
See the IN operator in
Comparison Operators
for full semantics. |
| EXISTS | BOOL | Returns TRUE if the subquery produced one or more rows. Returns FALSE if the
subquery produces zero rows. Never returns NULL. Unlike all other expression
subqueries, there are no rules about the column list. Any number of columns may
be selected and it will not affect the query result. |
Examples
The following examples of expression subqueries assume that t.int_array has
type ARRAY<INT64>.
| Type | Subquery | Result Data Type | Notes |
|---|---|---|---|
| Scalar | (SELECT COUNT(*) FROM t.int_array) |
INT64 | |
(SELECT DISTINCT i FROM t.int_array i) |
INT64, possibly runtime error | ||
(SELECT i FROM t.int_array i WHERE i=5) |
INT64, possibly runtime error | ||
(SELECT ARRAY_AGG(i) FROM t.int_array i) |
ARRAY | Uses the ARRAY_AGG aggregation function to return an ARRAY. | |
(SELECT 'xxx' a) |
STRING | ||
(SELECT 'xxx' a, 123 b) |
Error | Returns an error because there is more than one column | |
(SELECT AS STRUCT 'xxx' a, 123 b) |
STRUCT | ||
(SELECT AS STRUCT 'xxx' a) |
STRUCT | ||
| ARRAY | ARRAY(SELECT COUNT(*) FROM t.int_array) |
ARRAY of size 1 | |
ARRAY(SELECT x FROM t) |
ARRAY | ||
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) |
Error | Returns an error because there is more than one column | |
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) |
ARRAY | ||
ARRAY(SELECT AS STRUCT i FROM t.int_array i) |
ARRAY | Makes an ARRAY of one-field STRUCTs | |
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) |
ARRAY | Returns an ARRAY of STRUCTs with anonymous or duplicate fields. | |
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) |
array<TypeName> | Selecting into a named type. Assume TypeName is a STRUCT type with fields a,b,c. | |
| STRUCT | (SELECT AS STRUCT 1 x, 2, 3 x) |
STRUCT | Constructs a STRUCT with anonymous or duplicate fields. |
| EXISTS | EXISTS(SELECT x,y,z FROM table WHERE y=z) |
BOOL | |
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) |
BOOL | ||
| IN | x IN (SELECT y FROM table WHERE z) |
BOOL | |
x NOT IN (SELECT y FROM table WHERE z) |
BOOL |