Query statements scan one or more tables or expressions and return the computed result rows. This topic describes the syntax for SQL queries in Google Standard SQL for BigQuery. Show
SQL syntaxquery_statement: query_expr query_expr: [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ] { select | ( query_expr ) | set_operation } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [ { ALL | DISTINCT } ] [ AS { STRUCT | VALUE } ] select_list [ FROM from_clause[, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ QUALIFY bool_expression ] [ WINDOW window_clause ] Notation rules
Sample tablesThe following tables are used to illustrate the behavior of different query clauses in this reference. Roster tableThe
You can use this
PlayerStats tableThe
You can use this
TeamMascot tableThe
You can use this
SELECT statementSELECT [ { ALL | DISTINCT } ] [ AS { STRUCT | VALUE } ] select_list select_list: { select_all | select_expression } [, ...] select_all: [ expression. ]* [ EXCEPT ( column_name [, ...] ) ] [ REPLACE ( expression [ AS ] column_name [, ...] ) ] select_expression: expression [ [ AS ] alias ] The Each item in the
SELECT *
SELECT expressionItems in a If the expression does not have an explicit alias, it receives an implicit alias according to the rules for implicit aliases, if possible. Otherwise, the column is anonymous and you cannot refer to it by name elsewhere in the query. SELECT expression.*An item in a The following query produces one output column for each column in the table
More examples:
SELECT * EXCEPTA
SELECT * REPLACEA A
SELECT DISTINCTA
SELECT ALLA SELECT AS STRUCT
This produces a value table with a STRUCT row type, where the STRUCT field names and types match the column names and types produced in the Example:
SELECT AS VALUE
Example:
The query above produces a table with row type FROM clauseFROM from_clause[, ...] from_clause: from_item [ { pivot_operator | unpivot_operator } ] [ tablesample_operator ] from_item: { table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] | { join_operation | ( join_operation ) } | ( query_expr ) [ as_alias ] | field_path | unnest_operator | cte_name [ as_alias ] } as_alias: [ AS ] alias The pivot_operatorSee PIVOT operator. unpivot_operatorSee UNPIVOT operator. tablesample_operatorSee TABLESAMPLE operator. table_nameThe name (optionally qualified) of an existing table. SELECT * FROM Roster; SELECT * FROM dataset.Roster; SELECT * FROM project.dataset.Roster; FOR SYSTEM_TIME AS OF
Limitations: The source table in the
The value of
A single query statement cannot reference a single table at more than one point in time, including the current time. That is, a query can reference a table multiple times at the same timestamp, but not the current version and a historical version, or two different historical versions. Examples: The following query returns a historical version of the table from one hour ago.
The following query returns a historical version of the table at an absolute point in time.
The following query returns an error because the
The following operations show accessing a historical version of the table before table is replaced.
The following operations show accessing a historical version of the table before a DML job.
The following query returns an error because the DML operates on the current version of the table, and a historical version of the table from one day ago.
join_operationSee JOIN operation. query_expr
field_pathIn the Some examples of valid
Field
paths in the unnest_operatorSee UNNEST operator. cte_nameCommon table expressions (CTEs) in a Example:
The
UNNEST operatorunnest_operator: { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ as_alias ] [ WITH OFFSET [ as_alias ] ] as_alias: [AS] alias The For input
For several ways to use UNNEST and STRUCTsFor an input Example:
Because the Example:
Explicit and implicit UNNEST
Example:
In implicit unnesting, Example:
In this scenario, UNNEST and NULLs
UNNEST and WITH OFFSETThe optional
Example:
PIVOT operatorFROM from_item[, ...] pivot_operator pivot_operator: PIVOT( aggregate_function_call [as_alias][, ...] FOR input_column IN ( pivot_column [as_alias][, ...] ) ) [AS alias] as_alias: [AS] alias The
Conceptual example:
Definitions Top-level definitions:
Rules Rules for a
Rules for
Rules for
Rules for
Examples The following examples reference a table called
With the
If you do not include
You can select a subset of values in the
You can include multiple aggregation functions in the
UNPIVOT operatorFROM from_item[, ...] unpivot_operator unpivot_operator: UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] ( { single_column_unpivot | multi_column_unpivot } ) [unpivot_alias] single_column_unpivot: values_column FOR name_column IN (columns_to_unpivot) multi_column_unpivot: values_column_set FOR name_column IN (column_sets_to_unpivot) values_column_set: (values_column[, ...]) columns_to_unpivot: unpivot_column [row_value_alias][, ...] column_sets_to_unpivot: (unpivot_column [row_value_alias][, ...]) unpivot_alias and row_value_alias: [AS] alias The
Conceptual example:
Definitions Top-level definitions:
Rules Rules for a
Rules for
Rules for
Rules for
Rules for
Rules for
Examples The following examples reference a table called
With the
In this example, we
TABLESAMPLE operator
Description You can use the Sampling returns a variety of records while avoiding the costs associated with scanning and processing an entire table. Each execution of the query might
return different results because each execution processes an independently computed sample. Google Standard SQL does not cache the results of queries that include a Replace For more information, see Table sampling. Example The following query selects approximately 10% of a table's data:
JOIN operationjoin_operation: { cross_join_operation | condition_join_operation } cross_join_operation: from_item cross_join_operator from_item condition_join_operation: from_item condition_join_operator from_item join_condition cross_join_operator: { CROSS JOIN | , } condition_join_operator: { [INNER] JOIN | FULL [OUTER] JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN } join_condition: { on_clause | using_clause } on_clause: ON bool_expression using_clause: USING ( join_column [, ...] ) The [INNER] JOINAn
Example This query performs an
CROSS JOIN
If the rows of the two In a
You can use a
correlated cross join to convert or flatten an Examples This query performs an
Comma cross join (,)
A comma cross join looks like this in a
You cannot write comma cross joins inside parentheses. To learn more, see Join operations in a sequence.
You can use a
correlated comma cross join to convert or flatten an Examples This query performs a comma cross join on the
FULL [OUTER] JOINA
Example This query performs a
LEFT [OUTER] JOINThe result of a
Example This query performs a
RIGHT [OUTER] JOINThe result of a
Example This query performs a
ON clauseA combined row (the result of joining two rows) meets the
Example This query performs an
USING clauseThe
Example This query performs an This statement returns the rows from
ON and USING equivalencyThe
Although
Join operations in a sequenceThe
You can also insert parentheses to group
With parentheses, you can group
A
If your clause contains comma cross joins, you must use parentheses:
When comma cross joins are present in a query with a sequence of JOINs, they group from left to right like
other
There cannot be a
A join operation is correlated when the right In a correlated join operation, rows from the right All correlated join operations must reference an array in the right This is a conceptual example of a correlated join operation that includes a correlated subquery:
This is another conceptual example of a correlated join operation.
The
In a correlated
join operation, the right
Caveats
Examples This is an example of a correlated join, using the Roster and PlayerStats tables:
A common pattern for a correlated
In the case of a correlated
WHERE clause
The Only rows whose The evaluation of a query with a
Evaluation order does not always match syntax order. The Examples This query returns returns all rows from the
The
Expressions in an
GROUP BY clauseGROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } The Example:
The Example:
The query above is equivalent to:
Example:
uses the rollup list
This allows the computation of aggregates for the grouping sets defined by the expressions in the Example:
The query above outputs a row for each day in addition to the rolled up total across all days, as
indicated by a
Example:
The query above returns rows grouped by the following grouping sets:
The sums for these grouping sets correspond to the total for each distinct sku-day combination, the total for each sku across all days, and the grand total:
HAVING clause
The
Only rows whose The evaluation of a query with a
Evaluation order does not always match syntax order. The
If a query contains aliases in
the
Mandatory aggregationAggregation does not have to be present in the Aggregation function in the |
Column Name | Data Type |
---|---|
star_rating | INT64
|
up_down_rating | BOOL
|
Use a set operation on a value table
You can't combine tables and value tables in a SET
operation.
Appendix A: examples with sample data
These examples include statements which perform queries on the
Roster
and TeamMascot
, and PlayerStats
tables.
GROUP BY clause
Example:
SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;
LastName | SUM |
---|---|
Adams | 7 |
Buchanan | 13 |
Coolidge | 1 |
UNION
The UNION
operator combines the result sets of two or more SELECT
statements by pairing columns from the result set of each SELECT
statement and vertically concatenating them.
Example:
SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;
Results:
X | Y |
---|---|
Jaguars | 50 |
Knights | 51 |
Lakers | 52 |
Mustangs | 53 |
Adams | 3 |
Buchanan | 0 |
Coolidge | 1 |
Adams | 4 |
Buchanan | 13 |
INTERSECT
This query returns the last names that are present in both Roster and PlayerStats.
SELECT LastName
FROM Roster
INTERSECT DISTINCT
SELECT LastName
FROM PlayerStats;
Results:
LastName |
---|
Adams |
Coolidge |
Buchanan |
EXCEPT
The query below returns last names in Roster that are not present in PlayerStats.
SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;
Results:
LastName |
---|
Eisenhower |
Davis |
Reversing the order of the SELECT
statements will return last names in PlayerStats that are not present in Roster:
SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;
Results:
(empty)