Changing from the default dialect

The interface you use to query your data determines which query dialect is the
default:

In the GCP Console and the client libraries, standard SQL is the
default.

In the classic BigQuery web UI, the bq command-line tool, and the REST API, legacy
SQL is the default.

To switch to a different dialect:

Console

The default dialect for the GCP Console is standard SQL. To
change the dialect to legacy SQL:

If necessary, click Compose new query to open the query editor
window.

Below the query editor, click More > Query settings.

Click Advanced options.

In the Additional settings section, for SQL dialect, click
Legacy. This sets the legacy SQL option for this query. When you click
Compose new query to create a new query, you must select the legacy
sql option again.

Classic UI

The default dialect for the classic BigQuery web UI is legacy SQL. To change
the dialect to standard SQL:

In the classic web UI, click Compose Query.

Click Show Options.

For SQL Dialect, uncheck the Use Legacy SQL box. This sets the
standard SQL option while you have the query editor open. If you close the
query editor and reopen it, you must deselect the legacy sql option again.

CLI

The default query dialect in the bq command-line tool is legacy SQL. To
switch to the standard SQL dialect, add the --use_legacy_sql=false or
--nouse_legacy_sql flag to your command line statement.

Using a query prefix in the UI

You can also set the SQL dialect in the GCP Console or the classic
web UI by including a SQL dialect prefix as part of your query.

The following prefixes allow you to set the SQL dialect:

Prefix

Description

#legacySQL

Runs the query using legacy SQL

#standardSQL

Runs the query using standard SQL

In the GCP Console, when you use a query prefix, the SQL dialect
option is disabled in the Query settings.

In the classic web UI, when you use a query prefix, the prefix must
match the SQL dialect option. For example, in the classic web UI,
if you uncheck the Use Legacy SQL option, you cannot use the
#legacySQL prefix in your query.

For example, if you copy and paste the following query into the
GCP Console, BigQuery runs the query using legacy SQL
and ignores the default setting. The default setting for the
GCP Console is standard SQL.

Open .bigqueryrc in a text editor. By default, .bigqueryrc should be in
your user directory, for example, $HOME/.bigqueryrc.

Add the following text to the file. This example sets standard SQL as the
default syntax for queries and for the mk command (used when you create a
view). If you have already configured default values for query or mk command
flags, you do not need to add [query] or [mk] again.

[query]
--use_legacy_sql=false
[mk]
--use_legacy_sql=false

Save and close the file.

If you are using the interactive shell, you must exit and restart for the
changes to be applied.