# DISTINCT in BigQuery > SELECT DISTINCT treats NULLs as equal, accepts arrays and structs but refuses JSON and GEOGRAPHY, and bills the same bytes as a plain SELECT. BigQuery has no DISTINCT ON and no COUNT(DISTINCT a, b). Source: https://docs.querylab.io/bigquery/distinct/ Updated: 2026-11-10 `SELECT DISTINCT` returns each different combination of the selected columns once. In BigQuery two NULLs count as the same value, arrays and structs can be compared but JSON and GEOGRAPHY can't, and the query reads the same bytes as it would without `DISTINCT`. Two forms from other databases don't exist here: `DISTINCT ON (...)` and `COUNT(DISTINCT a, b)`. ## NULLs are one value `ben` has two rows with a NULL country, and they collapse into one: **DISTINCT on two columns** ```sql WITH visits AS ( SELECT * FROM UNNEST([ STRUCT('ana' AS customer, 'de' AS country), ('ana', 'de'), ('ben', CAST(NULL AS STRING)), ('ben', NULL), ('cy', 'fr') ]) ) SELECT DISTINCT customer, country FROM visits ORDER BY customer ``` Result in BigQuery (3 rows): | customer | country | | --- | --- | | ana | de | | ben | NULL | | cy | fr | `COUNT(DISTINCT x)` behaves differently, because aggregates skip NULLs. The same data gives two different answers: **COUNT(DISTINCT) against counting DISTINCT rows** ```sql WITH visits AS ( SELECT * FROM UNNEST(['de', 'de', 'fr', NULL]) AS country ) SELECT COUNT(DISTINCT country) AS count_distinct, (SELECT COUNT(*) FROM (SELECT DISTINCT country FROM visits)) AS distinct_rows FROM visits ``` Result in BigQuery (1 row): | count_distinct | distinct_rows | | --- | --- | | 2 | 3 | ## DISTINCT doesn't reduce the bill BigQuery bills the columns it reads. `DISTINCT` only changes what happens after the read, so both of these process 1,332,943 bytes: **plain SELECT** ```sql SELECT word FROM `bigquery-public-data.samples.shakespeare` ``` BigQuery dry run: this query processes 1.3 MiB. **SELECT DISTINCT** ```sql SELECT DISTINCT word FROM `bigquery-public-data.samples.shakespeare` ``` BigQuery dry run: this query processes 1.3 MiB. `DISTINCT` does add a shuffle, which uses slot time. On a large table with many different values, [`APPROX_COUNT_DISTINCT`](https://docs.querylab.io/bigquery/approximate-aggregation/) is cheaper in slots than `COUNT(DISTINCT x)` if you can accept a small error. ## Which types can be DISTINCT Arrays and structs work in `SELECT DISTINCT` and `GROUP BY`. Arrays are compared in order, so `['a', 'b']` and `['b', 'a']` stay separate rows: **DISTINCT over an array column** ```sql WITH orders AS ( SELECT 1 AS id, ['a', 'b'] AS tags UNION ALL SELECT 1, ['a', 'b'] UNION ALL SELECT 1, ['b', 'a'] ) SELECT DISTINCT id, tags FROM orders ORDER BY id, tags[OFFSET(0)] ``` Result in BigQuery (2 rows): | id | tags | | --- | --- | | 1 | [a, b] | | 1 | [b, a] | **DISTINCT over a struct column** ```sql WITH orders AS ( SELECT STRUCT('de' AS country, 10115 AS zip) AS address UNION ALL SELECT STRUCT('de', 10115) ) SELECT DISTINCT address FROM orders ``` Result in BigQuery (1 row): | address | | --- | | {country: de, zip: 10115} | `GROUP BY` accepts them too: **GROUP BY an array** ```sql WITH orders AS (SELECT ['a', 'b'] AS tags UNION ALL SELECT ['a', 'b']) SELECT tags, COUNT(*) AS orders FROM orders GROUP BY tags ``` Result in BigQuery (1 row): | tags | orders | | --- | --- | | [a, b] | 2 | Aggregate functions are stricter. `COUNT(DISTINCT ...)` refuses both: **COUNT(DISTINCT) on an array** ```sql WITH orders AS (SELECT ['a', 'b'] AS tags UNION ALL SELECT ['a', 'b']) SELECT COUNT(DISTINCT tags) AS different_tag_lists FROM orders ``` BigQuery rejects this query: ```text Aggregate functions with DISTINCT cannot be used with arguments of type ARRAY ``` **COUNT(DISTINCT) on a struct** ```sql WITH orders AS (SELECT STRUCT('de' AS country) AS address) SELECT COUNT(DISTINCT address) AS addresses FROM orders ``` BigQuery rejects this query: ```text Aggregate functions with DISTINCT cannot be used with arguments of type STRUCT ``` JSON and GEOGRAPHY can't be compared at all: **DISTINCT on JSON** ```sql WITH events AS (SELECT PARSE_JSON('{"a": 1}') AS payload) SELECT DISTINCT payload FROM events ``` BigQuery rejects this query: ```text Column payload of type JSON cannot be used in SELECT DISTINCT at [2:17] ``` **DISTINCT on GEOGRAPHY** ```sql WITH stores AS (SELECT ST_GEOGPOINT(13.4, 52.5) AS location) SELECT DISTINCT location FROM stores ``` BigQuery rejects this query: ```text Column location of type GEOGRAPHY cannot be used in SELECT DISTINCT at [2:17] ``` Convert them first: `TO_JSON_STRING(payload)` for JSON, `ST_ASTEXT(location)` for GEOGRAPHY. A `SELECT DISTINCT *` fails the same way if any column in the table has one of these types. The same goes for an array of JSON. See [Column of type ARRAY cannot be used in SELECT DISTINCT](https://docs.querylab.io/bigquery-errors/column-of-type-array-cannot-be-used-in-select-distinct/). ## What counts as equal Strings compare byte by byte, so case matters unless you lower it or use a [collation](https://docs.querylab.io/bigquery/strings-and-collation/): **three spellings of one name** ```sql WITH visits AS (SELECT * FROM UNNEST(['Ana', 'ana', 'ANA']) AS name) SELECT COUNT(DISTINCT name) AS as_stored, COUNT(DISTINCT LOWER(name)) AS lowered, COUNT(DISTINCT COLLATE(name, 'und:ci')) AS collated FROM visits ``` Result in BigQuery (1 row): | as_stored | lowered | collated | | --- | --- | --- | | 3 | 1 | 1 | For floats, `0.0` and `-0.0` are the same value and all NaNs are one value, so these four inputs give two: **signed zero and NaN** ```sql WITH readings AS ( SELECT * FROM UNNEST([0.0, -0.0, CAST('NaN' AS FLOAT64), CAST('NaN' AS FLOAT64)]) AS value ) SELECT COUNT(DISTINCT value) AS distinct_values FROM readings ``` Result in BigQuery (1 row): | distinct_values | | --- | | 2 | ## Counting pairs of columns `COUNT(DISTINCT a, b)` works in MySQL. BigQuery's `COUNT` takes one argument: **COUNT(DISTINCT) on two columns** ```sql WITH visits AS (SELECT 'ana' AS customer, 'de' AS country) SELECT COUNT(DISTINCT customer, country) AS pairs FROM visits ``` BigQuery rejects this query: ```text No matching signature for aggregate function COUNT Argument types: STRING, STRING Signature: COUNT(T1) Signature accepts at most 1 argument, found 2 arguments at [2:8] ``` The common fix, `CONCAT`, is wrong twice. `'ab' + 'c'` and `'a' + 'bc'` become the same string, and a NULL in either column makes the whole result NULL, which the count then skips. `TO_JSON_STRING` over a struct keeps the columns apart and keeps the NULL: **CONCAT against TO_JSON_STRING** ```sql WITH visits AS ( SELECT * FROM UNNEST([ STRUCT('ab' AS first_part, 'c' AS second_part), ('a', 'bc'), ('z', NULL) ]) ) SELECT COUNT(DISTINCT CONCAT(first_part, second_part)) AS with_concat, COUNT(DISTINCT TO_JSON_STRING(STRUCT(first_part, second_part))) AS with_json FROM visits ``` Result in BigQuery (1 row): | with_concat | with_json | | --- | --- | | 1 | 3 | The three pairs count as 1 with `CONCAT` and 3 with `TO_JSON_STRING`. Counting rows from a `SELECT DISTINCT a, b` subquery gives the same answer as the JSON form. ## No DISTINCT ON Postgres has `DISTINCT ON (customer)` for "one row per customer". BigQuery doesn't: **DISTINCT ON** ```sql WITH orders AS (SELECT 'ana' AS customer, 30 AS amount) SELECT DISTINCT ON (customer) customer, amount FROM orders ``` BigQuery rejects this query: ```text Syntax error: Unexpected keyword ON at [2:17] ``` Use [`QUALIFY`](https://docs.querylab.io/bigquery/qualify/) with `ROW_NUMBER`. It says which row wins, which `DISTINCT` can't: **latest order per customer** ```sql WITH orders AS ( SELECT * FROM UNNEST([ STRUCT('ana' AS customer, DATE '2026-01-05' AS placed, 30 AS amount), ('ana', DATE '2026-02-01', 25), ('ben', DATE '2026-01-20', 15) ]) ) SELECT customer, placed, amount FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer ORDER BY placed DESC) = 1 ORDER BY customer ``` Result in BigQuery (2 rows): | customer | placed | amount | | --- | --- | --- | | ana | 2026-02-01 | 25 | | ben | 2026-01-20 | 15 | ## DISTINCT with ORDER BY After `SELECT DISTINCT`, only the selected columns exist, so you can't sort by another one: **ORDER BY a column that isn't selected** ```sql WITH orders AS (SELECT 'ana' AS customer, 30 AS amount) SELECT DISTINCT customer FROM orders ORDER BY amount ``` BigQuery rejects this query: ```text ORDER BY clause expression references column amount which is not visible after SELECT DISTINCT at [2:47] ``` Inside an aggregate the rule is similar. With `DISTINCT`, `ORDER BY` can only use the aggregated value itself: **STRING_AGG(DISTINCT ... ORDER BY)** ```sql WITH visits AS ( SELECT * FROM UNNEST([STRUCT('fr' AS country, 2 AS rank), ('de', 1)]) ) SELECT STRING_AGG(DISTINCT country ORDER BY rank) AS countries FROM visits ``` BigQuery rejects this query: ```text An aggregate function that has both DISTINCT and ORDER BY arguments can only ORDER BY expressions that are arguments to the function at [4:45] ``` `ARRAY_AGG(DISTINCT x)` fails on a NULL input, because BigQuery can't return an array that holds a NULL: **ARRAY_AGG(DISTINCT) with a NULL** ```sql WITH visits AS (SELECT * FROM UNNEST(['de', 'de', NULL]) AS country) SELECT ARRAY_AGG(DISTINCT country) AS countries FROM visits ``` BigQuery rejects this query: ```text Array cannot have a null element; error in writing field countries ``` `IGNORE NULLS` fixes that, and `ORDER BY x` makes the result stable: **IGNORE NULLS and ORDER BY in the aggregate** ```sql WITH visits AS (SELECT * FROM UNNEST(['fr', 'de', 'de', NULL]) AS country) SELECT ARRAY_AGG(DISTINCT country IGNORE NULLS ORDER BY country) AS countries FROM visits ``` Result in BigQuery (1 row): | countries | | --- | | [de, fr] | ## DISTINCT in a window `COUNT(DISTINCT x) OVER (PARTITION BY ...)` is allowed: **distinct customers per country** ```sql WITH visits AS ( SELECT * FROM UNNEST([ STRUCT('de' AS country, 'ana' AS customer), ('de', 'ana'), ('de', 'ben'), ('fr', 'cy') ]) ) SELECT DISTINCT country, COUNT(DISTINCT customer) OVER (PARTITION BY country) AS customers FROM visits ORDER BY country ``` Result in BigQuery (2 rows): | country | customers | | --- | --- | | de | 2 | | fr | 1 | A running distinct count isn't, because the window can't have an `ORDER BY`: **running distinct count** ```sql WITH visits AS ( SELECT * FROM UNNEST([STRUCT(1 AS day, 'ana' AS customer), (2, 'ben')]) ) SELECT day, COUNT(DISTINCT customer) OVER (ORDER BY day) AS customers_so_far FROM visits ``` BigQuery rejects this query: ```text Window ORDER BY is not allowed if DISTINCT is specified at [4:44] ``` For a running count, mark each value's first appearance and sum those marks over the window. [Window functions](https://docs.querylab.io/bigquery/window-functions/) covers the frames. `UNION` needs an explicit `DISTINCT` or `ALL` in BigQuery. See [set operations](https://docs.querylab.io/bigquery/set-operations/). 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: - [Query syntax, SELECT DISTINCT](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_distinct) - [Data types, groupable data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#groupable_data_types) - [Aggregate function calls](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls)