How BigQuery SQL differs from other SQL

BigQuery SQL (GoogleSQL) uses backticks for names, has no implicit string-to-number casts, only one integer type, and requires ALL or DISTINCT after UNION. The differences that trip up people coming from Postgres, MySQL or SQL Server, each with a runnable example.

BigQuery’s SQL dialect is called GoogleSQL. Most of a SELECT reads like Postgres or MySQL, but a handful of rules differ and each one produces an error or a surprising result the first time you hit it: names are quoted with backticks, strings are never converted to numbers for you, integers are one 64-bit type, UNION needs ALL or DISTINCT, and / on two integers returns a FLOAT64. BigQuery also has shortcuts that Postgres and MySQL lack: SELECT * EXCEPT, QUALIFY, GROUP BY ALL and the SAFE. prefix.

Table names, backticks and dashes

A full table name is project.dataset.table. Project IDs often contain dashes (bigquery-public-data), and a dash is not a valid character in an unquoted name, so the usual advice is to wrap the path in backticks:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`

There is one exception. In a FROM clause the first part of the path may contain dashes without quotes, so this reads the same table:

SELECT word
FROM bigquery-public-data.samples.shakespeare

Only the first part gets that. A table whose own name contains a dash must be backticked, and a dataset name can never contain one:

SELECT word
FROM bigquery-public-data.samples.my-table

BigQuery stops at the dash in my-table with a syntax error, before it looks for the table. With backticks the same name parses, and BigQuery then reports that the table doesn’t exist. Backticks are also how you use a reserved word as a name: `group` is a column, group is a syntax error. Details on project, dataset and table names are in projects, datasets and tables.

Double quotes are not for names. In BigQuery "name" is a string literal, the same as 'name':

WITH users AS (SELECT 'Ada' AS name)
SELECT "name" AS quoted, name AS column_value
FROM users

SELECT * EXCEPT and REPLACE

Wide tables are normal in BigQuery, and a billed byte is a read byte, so selecting all columns but one is a common need. EXCEPT drops columns from *, and REPLACE swaps an expression in for a column while keeping its position:

WITH customers AS (
  SELECT 1 AS id, 'Ada' AS name, '[email protected]' AS email, 120.5 AS balance UNION ALL
  SELECT 2, 'Lin', '[email protected]', 80
)
SELECT * EXCEPT (email) REPLACE (ROUND(balance) AS balance)
FROM customers

email is gone from the output and balance is rounded in place. Dropping a column this way saves bytes: on bigquery-public-data.samples.shakespeare, SELECT * is estimated at 6.1 MB and SELECT * EXCEPT (word) at 4.9 MB, because BigQuery reads only the columns the query uses. See how queries are billed.

QUALIFY filters on window functions

WHERE runs before window functions, so in most databases “latest row per customer” needs a subquery. BigQuery has QUALIFY, which filters after the window function is computed:

WITH orders AS (
  SELECT 'Ada' AS customer, DATE '2026-01-03' AS order_date, 40 AS total UNION ALL
  SELECT 'Ada', DATE '2026-02-10', 95 UNION ALL
  SELECT 'Lin', DATE '2026-01-20', 15 UNION ALL
  SELECT 'Lin', DATE '2026-03-01', 60
)
SELECT customer, order_date, total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC) = 1
ORDER BY customer

Each customer has two orders and only the most recent one is returned. A window function has to appear in the QUALIFY expression or in the SELECT list. QUALIFY runs after GROUP BY, HAVING and WINDOW, and before DISTINCT, ORDER BY and LIMIT. More in QUALIFY.

The SAFE. prefix returns NULL instead of an error

Some functions fail the whole query on bad input. LOG(-1) is one of them:

SELECT LOG(-1) AS result

Put SAFE. in front of the function name and the failing call returns NULL:

SELECT SAFE.LOG(-1) AS result

The prefix only works on functions. It doesn’t work on operators such as + or =, on IN, ARRAY or UNNEST, or on CAST and EXTRACT. It also doesn’t cover an error raised while evaluating the arguments. The replacements are SAFE_CAST for casts and SAFE_DIVIDE(x, y) for division. SAFE.DIVIDE does not exist: BigQuery answers “Function not found: DIVIDE”.

No implicit casts between strings and numbers

MySQL and SQL Server quietly convert '5' to 5 when you compare or add them. BigQuery doesn’t:

WITH carts AS (SELECT '5' AS quantity)
SELECT quantity
FROM carts
WHERE quantity = 5

quantity is a STRING column, 5 is an INT64, and BigQuery reports “No matching signature”. quantity + 1 fails the same way, and so does n IN ('1') for an integer n, SUBSTR('abc', '1'), and a UNION ALL of an integer column with a string column. Convert explicitly:

SELECT CAST('5' AS INT64) + 1 AS total,
       SAFE_CAST('five' AS INT64) AS bad_value

SAFE_CAST returns NULL when the string isn’t a number, where CAST would stop the query. Numbers do convert among themselves, so 1 = 1.0 is fine.

One more rule goes the other way. A string literal (not a column) is converted to a date or timestamp when it is compared with one:

WITH shipments AS (
  SELECT DATE '2026-01-15' AS shipped UNION ALL
  SELECT DATE '2025-11-02'
)
SELECT shipped
FROM shipments
WHERE shipped > '2026-01-01'

That is why WHERE shipped > '2026-01-01' works. A STRING column gets no such treatment, and a DATE never compares with a TIMESTAMP without a cast.

Integers are INT64, and / returns FLOAT64

BigQuery has one integer type. INT, SMALLINT, INTEGER, BIGINT, TINYINT and BYTEINT are all aliases for INT64, so a column declared TINYINT holds the full 64-bit range. Overflow is an error, not a wrap-around:

SELECT 9223372036854775807 + 1 AS result

Division of two integers returns FLOAT64, which is different from Postgres and SQL Server, where 10 / 4 is 2. For integer division use DIV:

SELECT 10 / 4 AS slash, DIV(10, 4) AS div, MOD(10, 4) AS mod, 7 / 7 AS exact

10 / 4 is 2.5 and 7 / 7 is 1.0, not 1. DIV(10, 4) is 2. Dividing by zero with / is an error, so use SAFE_DIVIDE when the divisor can be zero. For exact decimals use NUMERIC instead of FLOAT64; see data types.

UNION needs ALL or DISTINCT

A bare UNION is an error. BigQuery wants you to say whether duplicates are kept:

SELECT 1 AS n
UNION
SELECT 1

UNION ALL keeps every row and UNION DISTINCT removes duplicates. INTERSECT and EXCEPT also need the keyword, and they accept only DISTINCT:

SELECT 'all' AS kind, COUNT(*) AS row_count
FROM (SELECT 1 AS n UNION ALL SELECT 1)
UNION ALL
SELECT 'distinct', COUNT(*)
FROM (SELECT 1 AS n UNION DISTINCT SELECT 1)

The two inputs each have two rows with the value 1. ALL returns 2 rows and DISTINCT returns 1. See set operations for CORRESPONDING and the rest.

What is case-sensitive

Column names, field names, aliases, keywords and built-in function names are case-insensitive:

WITH users AS (SELECT 1 AS UserId, 'Ada' AS Name)
SELECT userid, NAME
FROM users
WHERE USERID = 1

The columns were defined as UserId and Name, and the query wrote userid and NAME. The result column carries the spelling used in the SELECT, not the spelling from the table. A STRING value and string comparisons are case-sensitive, unless you use a collation:

SELECT 'Ada' = 'ada' AS equal, LOWER('Ada') = 'ada' AS equal_lowered

Two things are case-sensitive that people expect not to be: dataset and table names, and the names of persistent user-defined functions (temporary functions are not). samples.Shakespeare is not samples.shakespeare, and BigQuery reports the table as not found. A dataset can be made case-insensitive with the is_case_insensitive option when it is created.

-- Not run: these two names are different tables unless the dataset is case-insensitive
SELECT * FROM mydataset.Orders;
SELECT * FROM mydataset.orders;

GROUP BY ALL and column numbers

GROUP BY ALL groups by every SELECT item that isn’t an aggregate or a window function, so you don’t list the columns twice:

WITH sales AS (
  SELECT 'EU' AS region, 'books' AS category, 10 AS amount UNION ALL
  SELECT 'EU', 'books', 5 UNION ALL
  SELECT 'EU', 'games', 7 UNION ALL
  SELECT 'US', 'books', 20
)
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ALL
ORDER BY region, category

You can also refer to select-list positions in GROUP BY and ORDER BY:

WITH sales AS (
  SELECT 'EU' AS region, 'books' AS category, 10 AS amount UNION ALL
  SELECT 'EU', 'books', 5 UNION ALL
  SELECT 'EU', 'games', 7 UNION ALL
  SELECT 'US', 'books', 20
)
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY 1
ORDER BY 2 DESC

GROUP BY 1 groups by the first select item, and ORDER BY 2 DESC sorts by the total. A position that is out of range is an error (“ORDER BY is out of SELECT column number range: 2”), and a position that points at an aggregate can’t be grouped on. Positions break silently when someone reorders the select list, so they suit ad hoc queries more than saved ones.

Trailing commas

A trailing comma is allowed at the end of the SELECT list, which makes it easy to comment out the last column:

SELECT
  1 AS id,
  'Ada' AS name,
FROM (SELECT 1)

That is all it is allowed for. A trailing comma in an array literal, a function call, a STRUCT or an IN list is a syntax error, and so is one after the last CTE in a WITH clause:

SELECT [1, 2, 3,] AS scores

Where Querylab.io helps

The editor underlines a bare UNION as you type, before BigQuery rejects the query. It also flags = NULL comparisons, and offers to turn a subquery that filters on ROW_NUMBER() into a QUALIFY clause and a GROUP BY that repeats the non-aggregated columns into GROUP BY ALL. See code quality.