BigQuery Union – Syntax and Usage Examples in 2023
More often than not, databases will include several tables of data, and unifying the various tables with the use of query expressions enables us to carry out operations on the data. Tables or datasets from a number of sources may be combined into a single dataset or table that can then be queried using BigQuery UNION.
Let’s examine the many instances and case studies of the UNION operator in BigQuery, as well as address some of the most often asked questions.
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 | Duplicates Values |
| Gives slower result | Gives faster result |
| Returns specific records from queries and tables | All queries and tables data are returned. |
| Performance is slower | 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.
BigQuery UNION Use Cases
Let’s look at the different example use cases and instances on how we can use BigQuery UNION
Create union table automatically in BigQuery
There are a few different ways to create a UNION table automatically in BigQuery. The simplest method is to use Coupler.io to import tables from Gsheets, Airtable, Quickbooks, or other sources to BigQuery on a scheduled basis. Coupler.io is a very effective approach since it automates data import from many sources into Excel, Google Sheets, or BigQuery.
Check out all the available BigQuery integrations.
In the example below, we have two seperate tables we want to import from Gsheets to BigQuery using Coupler.io.
The first table is the Customers table
The second table is the Employees table
We will import the tables one after the other. To do this, here are the steps to follow:
- Sign in to Coupler.io.
- Connect to your source (which is Google Sheets)
- Select the spreadsheet that contains the dataset that we want to import
- Select the sheets that contain the table that we want to import
Once you’re set up the source, proceed to the destination setup.
- Select BigQuery.
- Click Connect to configure your destination account 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.
Note: The dataset and an empty table should be created in BigQuery first.
That’s it. We will also follow these steps to import the second table. It looks like this after the import:
The Customers table:
The Employees table:
Note: We would reference these tables as we go through the various examples and use cases.
BigQuery UNION different columns
All the examples done to this point unifies or combines only one column, however, we 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
And with that, we have gone through the most common ways to utilize the UNION operator in BigQuery, as well as syntax and different examples to show how it is used. To speed up importing data from different sources into BigQuery for the purpose of data analysis, feel free to use Coupler.io. Best of luck!
