expression_subqueries.md

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 or SELECT AS ProtoName, it can include multiple columns, and the returned value is the constructed STRUCT or PROTO. Selecting multiple columns without using SELECT AS is an error.
ARRAY	ARRAY	Can use SELECT AS STRUCT or SELECT AS ProtoName to build arrays of structs or PROTOs, 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