Legacy SQL vs GoogleSQL in BigQuery
How to recognize BigQuery legacy SQL and what changes when you move a query to GoogleSQL. In legacy SQL a comma between tables means UNION ALL, COUNT(DISTINCT) is approximate, tables are written [project:dataset.table], and views can't be queried across dialects.
BigQuery has two SQL dialects. GoogleSQL (once called standard SQL) is the current one and the default everywhere, and everything else on this site uses it. Legacy SQL is BigQuery’s original dialect. It still runs, mostly in old scheduled queries, views and scripts that set useLegacySql: true, and some of its rules mean the same query text gives a different answer. This page is for recognizing legacy SQL and moving away from it.
How to recognize it
- Table names in square brackets with a colon:
[bigquery-public-data:samples.shakespeare]. GoogleSQL uses backticks and dots:`bigquery-public-data.samples.shakespeare`. - Functions that don’t exist in GoogleSQL:
FLATTEN,TABLE_DATE_RANGE,EXACT_COUNT_DISTINCT,INTEGER(),NVL. GROUP EACH BY,JOIN EACH,OMIT RECORD IF.- A first line
#legacySQL, which forces the dialect;#standardSQLforces GoogleSQL. - In the API or
bq:useLegacySql: trueor--use_legacy_sql=true. Thebqcommand line still defaults to legacy SQL unless your.bigqueryrcsays otherwise.
Differences that change results
We ran these queries in both dialects against samples.shakespeare (164,656 rows):
| Query | Legacy SQL | GoogleSQL |
|---|---|---|
SELECT COUNT(*) FROM t, t | 329,312: the comma means UNION ALL | a cross join (27 billion rows) |
SELECT COUNT(DISTINCT word) FROM t | 31,719: approximate | 32,786: exact |
SELECT 7 / 2 | 3.5 | 3.5 |
The comma. Between tables in FROM, a comma in legacy SQL appends the tables, matching columns by name, like UNION ALL BY NAME. In GoogleSQL a comma is a CROSS JOIN. A legacy query that combines daily tables with commas becomes an enormous cross join if it is run as GoogleSQL. Rewrite it with UNION ALL or a wildcard table.
COUNT(DISTINCT). Legacy SQL counts distinct values approximately and needs EXACT_COUNT_DISTINCT for an exact count. GoogleSQL’s COUNT(DISTINCT x) is exact, and APPROX_COUNT_DISTINCT is the approximate one. Numbers in reports can change by a few percent when a query moves to GoogleSQL; the new number is the correct one.
Nested data. Legacy SQL flattens repeated fields in results automatically (the “Flatten results” option), so a query over a table with arrays can return more rows than the table has. GoogleSQL never flattens on its own; you UNNEST explicitly. See UNNEST.
NULL and types. GoogleSQL is stricter: no implicit string-to-number conversion, x = NULL is an error, and integer overflow fails instead of wrapping. See How BigQuery SQL differs.
Translating common constructs
| Legacy SQL | GoogleSQL |
|---|---|
[project:dataset.table] | `project.dataset.table` |
FROM t1, t2 (append) | FROM t1 UNION ALL BY NAME FROM t2, or UNION ALL |
TABLE_DATE_RANGE([ds.events_], TIMESTAMP('2026-03-01'), TIMESTAMP('2026-03-07')) | FROM `ds.events_*` WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260307' |
FLATTEN(t, field) | FROM t, UNNEST(t.field) AS field |
EXACT_COUNT_DISTINCT(x) | COUNT(DISTINCT x) |
COUNT(DISTINCT x) | APPROX_COUNT_DISTINCT(x) (to keep the old numbers) |
NVL(x, y) | IFNULL(x, y) |
INTEGER(x) | CAST(x AS INT64) |
GROUP EACH BY, JOIN EACH | GROUP BY, JOIN |
x CONTAINS 'abc' | STRPOS(x, 'abc') > 0, or x LIKE '%abc%' |
Views can’t cross dialects
A view defined in legacy SQL can’t be queried from GoogleSQL, and the other way round. That is what usually blocks a migration: a GoogleSQL query that reads an old legacy view fails. Recreate the views in GoogleSQL, under new names first if other legacy queries still depend on them, then switch the queries over and drop the old views.
Cost
Both dialects bill the same way: bytes from the columns a query reads. Our one-column query read 1.3 MB in either. Migrating doesn’t change the bill, except where a rewrite changes what the query reads, such as a comma append turned into a wildcard table with a _TABLE_SUFFIX filter that skips tables.
Querylab.io’s editor and engine speak GoogleSQL only.