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).
Practice this in a free lesson on learn.querylab.io: DISTINCT.
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:
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
| Row | customer | country |
|---|---|---|
| 1 | ana | de |
| 2 | ben | null |
| 3 | cy | fr |
COUNT(DISTINCT x) behaves differently, because aggregates skip NULLs. The same data gives two different answers:
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
| Row | count_distinct | distinct_rows |
|---|---|---|
| 1 | 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:
SELECT word FROM `bigquery-public-data.samples.shakespeare`This query will process 1.3 MiB when run.
SELECT DISTINCT word FROM `bigquery-public-data.samples.shakespeare`This query will process 1.3 MiB when run.
DISTINCT does add a shuffle, which uses slot time. On a large table with many different values, APPROX_COUNT_DISTINCT 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:
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)]
| Row | id | tags |
|---|---|---|
| 1 | 1 | [a, b] |
| 2 | 1 | [b, a] |
WITH orders AS ( SELECT STRUCT('de' AS country, 10115 AS zip) AS address UNION ALL SELECT STRUCT('de', 10115) ) SELECT DISTINCT address FROM orders
| Row | address |
|---|---|
| 1 | {country: de, zip: 10115} |
GROUP BY accepts them too:
WITH orders AS (SELECT ['a', 'b'] AS tags UNION ALL SELECT ['a', 'b']) SELECT tags, COUNT(*) AS orders FROM orders GROUP BY tags
| Row | tags | orders |
|---|---|---|
| 1 | [a, b] | 2 |
Aggregate functions are stricter. COUNT(DISTINCT ...) refuses both:
WITH orders AS (SELECT ['a', 'b'] AS tags UNION ALL SELECT ['a', 'b']) SELECT COUNT(DISTINCT tags) AS different_tag_lists FROM orders
Aggregate functions with DISTINCT cannot be used with arguments of type ARRAY
WITH orders AS (SELECT STRUCT('de' AS country) AS address) SELECT COUNT(DISTINCT address) AS addresses FROM orders
Aggregate functions with DISTINCT cannot be used with arguments of type STRUCT
JSON and GEOGRAPHY can’t be compared at all:
WITH events AS (SELECT PARSE_JSON('{"a": 1}') AS payload) SELECT DISTINCT payload FROM events
Column payload of type JSON cannot be used in SELECT DISTINCT at [2:17]
WITH stores AS (SELECT ST_GEOGPOINT(13.4, 52.5) AS location) SELECT DISTINCT location FROM stores
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.
What counts as equal
Strings compare byte by byte, so case matters unless you lower it or use a collation:
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
| Row | as_stored | lowered | collated |
|---|---|---|---|
| 1 | 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:
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
| Row | distinct_values |
|---|---|
| 1 | 2 |
Counting pairs of columns
COUNT(DISTINCT a, b) works in MySQL. BigQuery’s COUNT takes one argument:
WITH visits AS (SELECT 'ana' AS customer, 'de' AS country) SELECT COUNT(DISTINCT customer, country) AS pairs FROM visits
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:
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
| Row | with_concat | with_json |
|---|---|---|
| 1 | 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:
WITH orders AS (SELECT 'ana' AS customer, 30 AS amount) SELECT DISTINCT ON (customer) customer, amount FROM orders
Syntax error: Unexpected keyword ON at [2:17]
Use QUALIFY with ROW_NUMBER. It says which row wins, which DISTINCT can’t:
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
| Row | customer | placed | amount |
|---|---|---|---|
| 1 | ana | 2026-02-01 | 25 |
| 2 | 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:
WITH orders AS (SELECT 'ana' AS customer, 30 AS amount) SELECT DISTINCT customer FROM orders ORDER BY amount
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:
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
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:
WITH visits AS (SELECT * FROM UNNEST(['de', 'de', NULL]) AS country) SELECT ARRAY_AGG(DISTINCT country) AS countries FROM visits
Array cannot have a null element; error in writing field countries
IGNORE NULLS fixes that, and ORDER BY x makes the result stable:
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
| Row | countries |
|---|---|
| 1 | [de, fr] |
DISTINCT in a window
COUNT(DISTINCT x) OVER (PARTITION BY ...) is allowed:
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
| Row | country | customers |
|---|---|---|
| 1 | de | 2 |
| 2 | fr | 1 |
A running distinct count isn’t, because the window can’t have an ORDER BY:
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
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 covers the frames.
UNION needs an explicit DISTINCT or ALL in BigQuery. See set operations.