# 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.

Source: https://docs.querylab.io/bigquery/googlesql/

Updated: 2026-10-27

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:

**a backticked table path**

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

BigQuery dry run: this query processes 1.3 MB.

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:

**the dashed project part, unquoted**

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

BigQuery dry run: this query processes 1.3 MB.

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:

**a dash in the table part, unquoted**

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

BigQuery rejects this query:

```text
Syntax error: Expected end of input but got "-" at [2:37]
```

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](https://docs.querylab.io/bigquery/projects-datasets-tables/).

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

**double quotes make a string, not a column**

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

Result in BigQuery (1 row):

| quoted | column_value |
| --- | --- |
| name | Ada |

## 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:

**drop email, round balance**

```sql
WITH customers AS (
  SELECT 1 AS id, 'Ada' AS name, 'ada@example.com' AS email, 120.5 AS balance UNION ALL
  SELECT 2, 'Lin', 'lin@example.com', 80
)
SELECT * EXCEPT (email) REPLACE (ROUND(balance) AS balance)
FROM customers
```

Result in BigQuery (2 rows):

| id | name | balance |
| --- | --- | --- |
| 1 | Ada | 121.0 |
| 2 | Lin | 80.0 |

`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](https://docs.querylab.io/bigquery/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:

**the latest order per customer**

```sql
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
```

Result in BigQuery (2 rows):

| customer | order_date | total |
| --- | --- | --- |
| Ada | 2026-02-10 | 95 |
| Lin | 2026-03-01 | 60 |

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](https://docs.querylab.io/bigquery/qualify/).

## SAFE. prefix: NULL instead of an error

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

**LOG of a negative number**

```sql
SELECT LOG(-1) AS result
```

BigQuery rejects this query:

```text
Floating point error in function: LN(-1)
```

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

**SAFE.LOG(-1)**

```sql
SELECT SAFE.LOG(-1) AS result
```

Result in BigQuery (1 row):

| result |
| --- |
| NULL |

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 string-number casts

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

**a STRING column compared with an integer**

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

BigQuery rejects this query:

```text
No matching signature for operator = for argument types: STRING, INT64
  Signature: T1 = T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {INT64, STRING} at [4:7]
```

`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:

**CAST and SAFE_CAST**

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

Result in BigQuery (1 row):

| total | bad_value |
| --- | --- |
| 6 | NULL |

`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:

**comparing a DATE column with a string literal**

```sql
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'
```

Result in BigQuery (1 row):

| shipped |
| --- |
| 2026-01-15 |

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.

## INT64 only, 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:

**the largest INT64 plus one**

```sql
SELECT 9223372036854775807 + 1 AS result
```

BigQuery rejects this query:

```text
Integer Overflow
```

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

**/, DIV and MOD on integers**

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

Result in BigQuery (1 row):

| slash | div | mod | exact |
| --- | --- | --- | --- |
| 2.5 | 2 | 2 | 1.0 |

`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](https://docs.querylab.io/bigquery/data-types/).

## UNION needs ALL or DISTINCT

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

**UNION without a quantifier**

```sql
SELECT 1 AS n
UNION
SELECT 1
```

BigQuery rejects this query:

```text
Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [3:1]
```

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

**UNION ALL vs UNION DISTINCT**

```sql
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)
```

Result in BigQuery (2 rows):

| kind | row_count |
| --- | --- |
| all | 2 |
| distinct | 1 |

The two inputs each have two rows with the value 1. `ALL` returns 2 rows and `DISTINCT` returns 1. See [set operations](https://docs.querylab.io/bigquery/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:

**mixed-case column references**

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

Result in BigQuery (1 row):

| userid | NAME |
| --- | --- |
| 1 | Ada |

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:

**string equality is case-sensitive**

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

Result in BigQuery (1 row):

| equal | equal_lowered |
| --- | --- |
| false | true |

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.

```sql
-- 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:

**GROUP BY ALL**

```sql
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
```

Result in BigQuery (3 rows):

| region | category | total |
| --- | --- | --- |
| EU | books | 15 |
| EU | games | 7 |
| US | books | 20 |

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

**GROUP BY 1 and ORDER BY 2 DESC**

```sql
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
```

Result in BigQuery (2 rows):

| region | total |
| --- | --- |
| EU | 22 |
| US | 20 |

`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.

## Chained calls and WITH expressions

Most functions can be written as method calls on their first argument. The value goes in parentheses, and each call after it takes the previous result. This reads left to right, where nested calls read from the inside out:

**nested calls and the chained form**

```sql
SELECT
  REPLACE(LOWER(TRIM('  Hello World ')), ' ', '_') AS nested,
  ('  Hello World ').TRIM().LOWER().REPLACE(' ', '_') AS chained
```

Result in BigQuery (1 row):

| nested | chained |
| --- | --- |
| hello_world | hello_world |

Both columns return `hello_world`. A function from a namespace goes in parentheses after the dot, so `SAFE.` works in a chain:

**(x).(SAFE.SQRT)()**

```sql
SELECT
  (16.0).(SAFE.SQRT)() AS root_of_16,
  (-1.0).(SAFE.SQRT)() AS root_of_minus_1
```

Result in BigQuery (1 row):

| root_of_16 | root_of_minus_1 |
| --- | --- |
| 4.0 | NULL |

`SQRT(-1)` is an error, so `SAFE.SQRT` returns NULL for it.

A `WITH(...)` expression names intermediate values inside one expression. Each name is defined as `name AS expression`, can use the names before it, and the last item is the result:

**WITH(subtotal AS ..., tax AS ..., result)**

```sql
SELECT
  order_id,
  WITH(
    subtotal AS price * qty,
    tax AS subtotal * 0.2,
    ROUND(subtotal + tax, 2)
  ) AS total
FROM UNNEST([
  STRUCT(1 AS order_id, 19.99 AS price, 3 AS qty),
  STRUCT(2, 5.5, 4)
])
ORDER BY order_id
```

Result in BigQuery (2 rows):

| order_id | total |
| --- | --- |
| 1 | 71.96 |
| 2 | 26.4 |

The `price * qty` calculation is written once and used twice. Without `WITH`, you repeat the expression or add a subquery or CTE for it. The last item must be an expression with no `AS`: `WITH(a AS 1, b AS 2)` is a syntax error, and repeating a name (`WITH(a AS 1, a AS 2, a)`) fails with "Column name a is ambiguous".

## Trailing commas

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

**a comma after the last column**

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

Result in BigQuery (1 row):

| id | name |
| --- | --- |
| 1 | Ada |

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:

**a trailing comma inside an array**

```sql
SELECT [1, 2, 3,] AS scores
```

BigQuery rejects this query:

```text
Syntax error: Unexpected "]" at [1:17]
```

## 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](https://docs.querylab.io/writing-sql/code-quality/).

Group by every non-aggregate column with GROUP BY ALL. 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:

- [Lexical structure and syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical)

- [Query syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)

- [Functions reference (SAFE. prefix)](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference)

- [Conversion rules](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules)

- [Data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)