What does UNION mean in BigQuery?
Combining the results of two or more queries in a vertical manner by consolidating or unifying the columns from the result sets of each query is what UNION in BigQuery is all about. If you’ve ever dealt with UNION queries in SQL, you’ll be familiar with BigQuery UNION queries. The only requirement is that BigQuery UNION is used with the “ALL” or “DISTINCT” keywords. When executing a UNION query, all the data received from each query is combined, and repeated values are either removed or returned depending on the keyword applied.
A graphical description of the UNION operator:
Union in BigQuery syntax
SELECT column name, column name...FROM table1 UNION (Distinct / All) SELECT column name, column name….. FROM table2;
- Column name: The columns you want to get records from.
- Tables: The tables containing the records you want to query. At least one table must be specified.
Example of UNION in BigQuery
As an example, let’s consider the following tables and how they can be combined using UNION in BigQuery.
The first table we have here is the Customers table
The second table we have here is the Sales table
BigQuery UNION ALL
When you execute a UNION ALL query on BigQuery, all input values are returned; however, when you perform a UNION DISTINCT query, only unique records are returned. This leads to the UNION ALL being superior to the UNION DISTINCT in terms of performance.
Now, to further explain this, if we want to get all the Names from the two tables above using UNION ALL, our query will be:
SELECT Name FROM `original-glyph-321514.table1.Customers` UNION ALL SELECT Name FROM `original-glyph-321514.table1.Sales`
From the query above, we ended up with the final table containing a combined result set of Names from the Sales and Customers tables. The UNION ALL also returns duplicate values.
BigQuery UNION DISTINCT
The UNION DISTINCT unites and discards duplicate items from the result sets of two or more input queries.
Note: The UNION DISTINCT in BigQuery is equivalent to UNION in SQL.
To get all the Names from the two tables above using UNION DISTINCT, our query will be:
SELECT Name FROM `original-glyph-321514.table1.Customers` UNION DISTINCT SELECT Name FROM `original-glyph-321514.table1.Sales`
From the query above, we ended up with the final table containing a combined result set of Names with duplicates ignored from the Sales and Customers tables. The UNION DISTINCT discards all repeated values.
BigQuery UNION vs UNION ALL
| UNION DISTINCT | UNION ALL |
|---|---|
| – Does not duplicate values – Gives slower result – Returns specific records from queries and tables – Performance is slower | – Duplicates Values – Gives faster result – All queries and tables data are returned – Performance is higher |
How to do UNION queries in BigQuery
There are two main ways to unite results with UNION in BigQuery which is:
Comma-Delimited UNIONS in Legacy SQL and Standard SQL.
- Comma-Delimited UNIONS in Legacy SQL: With this method, merging data is very easy. All you need is a comma-separated list of the many tables included inside the FROM clause. Using data from the table above, we have an example query on how to run the query in Legacy SQL presented below.
SELECT Name FROM [original-glyph-321514.table1.Customers], [original-glyph-321514.table1.Sales] ORDER BY Name
- Standard SQL: The new method takes longer, but is more familiar and, therefore, more user-friendly when using Google BigQuery. You must add the following UNION keywords that follow the UNION operator which is: ALL or DISTINCT. Using data from the table above, we have an example query on how to run the query in Standard SQL presented below.
SELECT Name FROM `original-glyph-321514.table1.Customers` UNION ALL SELECT Name FROM `original-glyph-321514.table1.Sales`
To learn how to switch between the two query formats you can read this blog post.
How to import data and union queries in BigQuery without SQL
The UNION operator is meant for combining data that is already loaded to BigQuery. And if I tell you that you can blend your data on the go before it lands in your BigQuery tables? And you won’t even need SQL for this!
You can easily do this with Coupler.io as follows.
Step 1. Collect data
Select your data source in the form below and click Proceed. You can also select BigQuery as a source to union data that is already in BigQuery.
You’ll need to create a Coupler.io account – you can do this with your Google account and sign up for free.
Then connect to your source app account and select the data to import. Depending on the source app, you can select data categories, metrics, reports, tables, and so on.
Once the first data source is connected, click Add one more source and complete the same connection flow for the second source. You can add multiple sources: different apps, accounts, spreadsheets, and even queries from BigQuery.
Step 2. Union and transform data
After you’ve connected all the sources from which you want to union data, click Transform data.
You’ll be offered to combine the data from the selected sources in two ways:
- Union data: add data elements from one of the added sources to another. To merge data, columns in both sources must be named equally.
- Join data: combine two data sources side by side. One column in each data set must be the same.
In this article, we’re talking about UNION, so click Unite sources to preview the results of the data from your sources.
At this step, you can also:
- Edit columns
- Hide unnecessary columns
- Add custom columns
- Filter and sort data
Step 3. Load data and automate data refresh
Once the data is ready, proceed to the destination settings.
- Click Connect and insert the credentials file to connect to BigQuery. Learn how to get the credentials.
- Specify the names of the Dataset and the Table to import data to. You can create new dataset and table by typing in new names.
By default, Coupler.io automatically detects the table schema. But if you wish, you can toggle this off and enter the custom table schema.
That’s it. All you need to do is click Run importer. At the same time, if you want to automate dataflow from your sources to BigQuery, enable the automatic data refresh and configure the desired schedule.
Data union will be done automatically for every data refresh.
BigQuery UNION Use Cases
Let’s look at the different use cases and instances on how you can use BigQuery UNION. As an example, we’ll be using two tables:
- The Customers table
- The Employees table
BigQuery UNION different columns
In the UNION example at the beginning of the article, we only combined one column. However, you can combine different columns using UNION. In the example below, we will combine different columns (First_Name, Last_Name, City, Country, and Type) from the tables above using this query:
SELECT First_Name, Last_Name, City, Country,Type FROM original-glyph-321514.table1.customers UNION all SELECT First_Name, Last_Name, City, Country, Type FROM original-glyph-321514.table1.employees
BigQuery UNION multiple tables
By combining several tables using UNION, we can create a single data table. In the example below, we imported a third table that we will use to unify with the two tables above.
Now we have a total of three tables, and we will be merging the City and Country data together from these tables using this query:
SELECT City, Country FROM original-glyph-321514.table1.customers UNION ALL SELECT City, Country FROM original-glyph-321514.table1.employees UNION ALL SELECT City, Country FROM original-glyph-321514.table1.sales
The result is a combined set of the three tables. You can do this for any number of tables that you need to combine.
BigQuery UNION NULL
The NULL values are automatically included in the result when we use UNION. If NULL values are present in the data, they may create a variety of logical and mathematical mistakes. Therefore, we must exercise extreme caution if NULL values are present.
In the example below, we edited one of our tables to have null values.
By running the UNION query to merge the data City, Country and First_Name data below we get a table with the NULL values.
SELECT City, Country, First_NameFROM original-glyph-321514.table1.customers UNION ALL SELECT City, Country, First_Name FROM original-glyph-321514.table1.customers1
Rules on how to use UNION in BigQuery
When using UNION operators, there are certain conditions that must be observed, such as:
- There must be uniformity in length and layout of columns
- The flow of statements in queries must be uniform
- Data types of the relevant columns should be the same
We introduced you to the syntax and different examples to show how you can use the UNION operator in BigQuery. At the same time, you can speed up data preparation using Coupler.io. It allows you to automate data import from different sources into BigQuery and combine data, including uniting it, without any complex SQL queries. Try it out yourself.
Automate data export with Coupler.io
Get started for free
