BigQuery, like many other GCP services, has been widely used within Google for several years. That usage initially relied on a non-standard variant of SQL, which is now called legacy SQL. Legacy SQL is pretty powerful and pretty easy to use in some specific cases, but it has a big downside: it is not standard!
To remedy that, BigQuery has added support for standard SQL 2011, with some extensions that have to do with nested and repeated fields. The query examples shown next are in standard SQL.
How can you tell at a glance whether a query is written in legacy SQL or standard SQL? Just look at the syntax used to specify tables or project names.
- In legacy SQL: Use square brackets to start and end the table name, and use a colon (:) to delimit dataset and table names:
[bigquery-public-data:samples:natality]
- In standard SQL: Use the backtick character...