Why you should use UNION DISTINCT sparingly

Let's help BigQuery do less unneeded work!

If you're UNIONING two sources known to have distinct values (and they don't have duplicates), go for UNION ALL instead of UNION DISTINCT (UNION for some other sql dialects) to avoid redundant de-duplication.

In the example below, I've unioned two Google Trends tables - one that is only for US terms and another one for the rest of the world. Since one table only contains US and the other everything except the US, we know the union of the two tables to be distinct from the start, thus not needing the UNION DISTINCT.

SELECT
  dma_name AS region_name,
  term,
  week,
  score,
  rank,
  refresh_date,
  'US' AS country_code
FROM `bigquery-public-data.google_trends.top_terms` --US terms

UNION DISTINCT

SELECT
  region_name,
  term,
  week,
  score,
  rank,
  refresh_date,
  country_code
FROM `bigquery-public-data.google_trends.international_top_terms` --international terms excl US

SELECT
  dma_name AS region_name,
  term,
  week,
  score,
  rank,
  refresh_date,
  'US' AS country_code
FROM `bigquery-public-data.google_trends.top_terms` --US terms

UNION ALL

SELECT
  region_name,
  term,
  week,
  score,
  rank,
  refresh_date,
  country_code
FROM `bigquery-public-data.google_trends.international_top_terms` --international terms excl US

There's no difference indeed for on-demand pricing (same amount of data scanned), but quite a difference for capacity pricing users ( 1/2 of slot usage).

So use UNION DISTINCT (and any other DISTINCT) sparingly and when you actually need it.

---

Enjoyed this? Here are some related articles you might find useful:

• 9 tips on writing cleaner SQL
• Order of precedence in SQL: WHERE vs HAVING
• Easy with that SELECT DISTINCT!
• Why you should use parentheses with AND & OR in SQL