BigQuery standard SQL vs legacy SQL: what changed and how to switch

How to tell which dialect a query is in

The table name gives it away:

Square brackets and a colon mean legacy SQL. Backticks and dots mean GoogleSQL.

Which dialect runs by default

It depends on where the query is sent. From the same Google page:

The bq default is the one that catches people: a query that works in the console fails from a script. Pass --use_legacy_sql=false, or put it in .bigqueryrc under a [query] section so it applies every time.

What the #standardSQL directive does

A query can state its own dialect on its first line:

#standardSQL
SELECT COUNT(*) FROM `bigquery-public-data.samples.shakespeare`;

#standardSQL and #legacySQL are not case-sensitive and must be followed by a new line. The directive tells BigQuery which dialect to parse the query with, whatever the tool’s default is. It was most useful when legacy SQL was the default everywhere. Today it is harmless in the console and still useful in bq scripts.

It does not override an explicit setting. We sent a query that began with #legacySQL through a client configured for GoogleSQL, and BigQuery refused it:

Query text specifies use_legacy_sql:true, while API options specify:false.

If you see that error, the directive in the query and the dialect option in the tool disagree. Remove one of them.

Differences that change results

Some differences are only syntax. These four change the numbers or the rows, which is what makes a careless conversion dangerous. They are documented in Google’s legacy SQL migration guide.

1. COUNT(DISTINCT) is exact in GoogleSQL and approximate in legacy SQL. A converted query can return a slightly different count, and the new one is the correct one. To see the size of the effect, we counted 200,000 distinct values both ways in GoogleSQL:

SELECT
  COUNT(DISTINCT x) AS exact_distinct,
  APPROX_COUNT_DISTINCT(x) AS approx_distinct
FROM UNNEST(GENERATE_ARRAY(1, 200000)) AS x;

If an old dashboard and its converted version differ by a fraction of a percent on user counts, this is usually why.

2. The comma between tables. In legacy SQL, FROM table1, table2 stacks the rows of both tables, like UNION ALL. In GoogleSQL a comma is a cross join. Converted without care, a query that added two tables together starts multiplying them. Write UNION ALL explicitly; see JOIN and UNION.

3. Nested data. Legacy SQL flattened repeated fields automatically, or with FLATTEN. GoogleSQL keeps arrays as arrays until you UNNEST them, so row counts differ until the unnest is written. See the array functions guide.

4. Many tables at once. Legacy SQL used functions such as TABLE_DATE_RANGE. GoogleSQL uses a wildcard and a filter on _TABLE_SUFFIX:

SELECT COUNT(*) AS events
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107';

More in wildcard tables.

What only GoogleSQL can do

• Change data and tables: INSERT, UPDATE, DELETE, MERGE, and CREATE or ALTER TABLE.
• WITH clauses, including recursive ones, and correlated subqueries.
• Arrays and structs as real types, with functions to build and read them.
• User-defined functions written in SQL.
• Newer additions such as pipe syntax and transactions.

Type names differ too: a legacy RECORD is a STRUCT in GoogleSQL, and the functions for dates and strings have different names. The BigQuery functions map lists the current families.

How to convert a legacy query

1. Rewrite the table references from [project:dataset.table] to backticks and dots.
2. Replace commas between tables with UNION ALL.
3. Add UNNEST wherever the query touched a repeated field.
4. Replace table range functions with a wildcard and _TABLE_SUFFIX.
5. Rename functions and types that no longer exist; the migration guide has the mapping.
6. Run both versions on the same dates and compare. Expect small differences in distinct counts. Investigate anything larger.

Saved queries, scheduled queries and views each keep their own dialect, so check them one by one. A view written in legacy SQL cannot be queried from GoogleSQL, and the reverse is also true, which is often what forces the conversion.

One dialect, one definition

Converting dialects is a good moment to notice how many copies of the same query exist. If “active users” lives in a scheduled query, two views and a dashboard’s custom SQL, it needs converting four times, and it will come out four slightly different ways.

In OWOX Data Marts the query is published once as a Data Mart and every report reads that. OWOX Data Marts runs your SQL as written, in your own warehouse, and does not transform the data. Where to go next with the language itself: the BigQuery SQL guide.

Google BigQueryComprehensive Guide to BigQuery Aggregate Functions for 2025April 18, 2024→Google BigQueryBigQuery array functions: syntax and tested examplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: convert data types without errorsOctober 6, 2026→

See all articles →