Skip to main content

SQL tips & tricks

Union all by name

This section describes handy SQL features and functions in BigQuery. This article covers the UNION ALL BY NAME feature, which makes it easier to write queries with set operations.

Editor's note: this post is written by Tamas Ujhelyi—follow him for handy SQL tips on Snowflake and BigQuery.

BigQuery just did something really clever.

Remember how to 𝐮𝐧𝐢𝐨𝐧 𝐚𝐥𝐥 two query results?

You have to meticulously position columns in the exact same order (with matching data types) in both queries:

SELECT name, country, age FROM table_1
UNION ALL
SELECT name, age, country FROM table_1

Aaand... it fails!

("country" can't be matched with "age" and vice versa.)

Here's how BigQuery just made our lives easier with something called BY NAME.

Using by name, you can "union all" your query results based on column names instead of positions.

So the query above that failed us with regular "union all" would now work perfectly:

SELECT name, country, age FROM table_1
UNION ALL BY NAME
SELECT name, age, country FROM table_1

It works because both select statements contain the same columns with matching names, and "by name" doesn't care about order – it cares about our well-being.

SQL query example with UNION ALL BY NAME
SQL query output using UNION ALL BY NAME

Now it's your turn!

I hope you've enjoyed this snippet and feel a bit more confident to utilise your own Google Analytics data in BigQuery. Drop a line in the comments if you have any questions, feedback or suggestions related to this article.