# 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. Source: https://docs.querylab.io/bigquery/legacy-sql/ Updated: 2026-10-07 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; `#standardSQL` forces GoogleSQL. - In the API or `bq`: `useLegacySql: true` or `--use_legacy_sql=true`. The `bq` command line still defaults to legacy SQL unless your `.bigqueryrc` says 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](https://docs.querylab.io/bigquery/wildcard-tables/). **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](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/googlesql/). ## 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, and they flag the comma trap: a comma between two tables in `FROM` is reported as Cross Join Detected, with a quick fix that writes it out as an explicit `CROSS JOIN` ([code quality](https://docs.querylab.io/writing-sql/code-quality/#performance)). Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Migrating to GoogleSQL](https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql) - [Introduction to SQL in BigQuery](https://cloud.google.com/bigquery/docs/introduction-sql)