Expression subqueries in Standard SQL

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  
Var denne side nyttig? Giv os en anmeldelse af den:

Send feedback om...

Har du brug for hjælp? Besøg vores supportside.