Expression subqueries

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
EXISTS	`NOT EXISTS(SELECT x,y,z FROM table WHERE y=z)`	BOOL
IN	`x IN (SELECT y FROM table WHERE z)`	BOOL
IN	`x NOT IN (SELECT y FROM table WHERE z)`	BOOL