Database Reference
In-Depth Information
Work is being done to make BigQuery SQL look more like Standard
SQL. Most of the changes are in expanded support for the features
described in the standard. For example, until recently, BigQuery
required that all fields in an
ORDER BY
be listed in the
SELECT
field
list; this is non-standard and inconvenient, so the limitation was quietly
eliminated.
Changes that are not backward compatible will eventually arise. However,
during the deprecation period following breaking changes, the new syntax
will be opt-in, and the old syntax will likely be supported for an additional
period after the default is changed.
Subqueries
A subquery is a query inside of another query. For instance, in
SELECT foo
FROM (SELECT field1 AS foo FROM table1)
, the
SELECT field1
AS foo FROM table1
query in parentheses is a subquery. The SQL-92
specification says that you can use a subquery almost anywhere—in a
FROM
clause, in a
SELECT
field list, or in a
WHERE
clause.
In many SQL environments, such as MySQL, subqueries are somewhat rare
and can have poor performance. (The two statements are often related; if
subqueries are slow, people won't use them often.) In BigQuery, however,
subqueries are used extensively in certain clauses and generally run quite
fast.
Preventing Duplicate Computation
One of the reasons to use subqueries is that BigQuery doesn't do much in
the way of query optimization. If you need to use a value multiple places in
the query, you may be better off using a subquery and computing the value
only once. Performance issues aside, this can often lead to a more readable
query. For example, if you want to translate a timestamp to a particular time
zone and use the same value in both the
SELECT
clause and a
WHERE
clause,
you could write:
SELECT DATE_ADD(ts, -8, "HOUR") AS pst
FROM [bigquery-e2e:ch10.sample_data]
WHERE DATE_ADD(ts, -8, "HOUR") >
