November 9, 2022

How to Write BigQuery LEFT JOIN Functions in Standard SQL

With <3 by Acuto

BigQuery is an enterprise data warehouse that can be leveraged by companies of any size to store and process data. Owned by Google, the service is fully managed and offers a scalable infrastructure. It’s also equipped with a query engine capable of querying billions of data rows in seconds.

Companies benefit from implementing a powerful data warehouse service that comes with integrated business intelligence, geospatial analysis, and machine learning features. If your team includes developers and data scientists, they can unlock the potential of your data using programming languages like Javascript, Python, Java, and BigQuery’s REST API, among others.

In this article, we’ll discuss the LEFT JOIN function, which is used to combine data from tables. This function is one of the multiple JOIN function types used in BigQuery, and it makes it possible to join the left table with common rows from the right table. Let’s get started.

Your Agency’s Data in Google BigQuery

BigQuery also comes with the capability to efficiently analyse data that isn’t stored within the data warehouse. Even though the best option is to upload your data into BigQuery and have more flexibility, storage, and computing power, you’re still able to run federated queries, read data from external databases and analyse it with the BI Engine.

When it comes to agencies, they have to work with loads of data on a daily basis. BigQuery allows them not only to store and process their data at lightning speeds but also to easily connect it with other tools from Google Workspace, such as Google Looker Studio, Google Analytics, and Google Sheets. BigQuery therefore allows users to query data seamlessly and without much effort.

Agencies can connect their data sources into BigQuery, query it and visualise it through Google Looker Studio to present it to clients. Now, it’s much easier to update clients with real-time information about their campaigns using the Standard SQL language and BigQuery.

Google BigQuery LEFT JOIN Function Definition

The BigQuery LEFT JOIN function is used to unify relevant data into one table by combining data from multiple tables, keeping all the rows from the left table unchanged, and extracting only the matching rows from the second table. In cases of no matching values from the second table, the null value is returned.

BigQuery LEFT JOIN Syntax

Similarly to the EXCEPT function, the LEFT JOIN syntax is fairly simple.

Here’s what it looks like:

SELECT table_1.column_1, table_2.column_2...
FROM table_1 
LEFT JOIN table2 
ON table_1.common_field = table2_common_field;

In case the left table contains more columns than you want to display, you can decide to SELECT only the columns you want to show by specifying this at the beginning of the query. However, in our example, we decided to SELECT all the columns from the left table for illustration purposes.

Tip: In some cases, the queries won’t work without an alias for the table names. That’s because the ON clause works best when using aliases. Aliases make the queries shorter, cleaner and more readable, and they can save you from running into errors.

How to Use the LEFT JOIN Function in Google BigQuery

The LEFT JOIN function can be used when you want to combine matching data from different tables.

For illustration purposes, we’re going to create two tables and run queries with the LEFT JOIN function. One table contains information about clients, and the other about their location.

table showing Details about clients budget and solution

First table (new_clients_22): Details about clients budget and solution

table showung Details about clients’ location

Second table (new_clients_22_location): Details about clients’ location

Example 1: Using LEFT JOIN in BigQuery SQL to Merge One Column

In this example, we will be using an alias to make the query less complex and easier to read. In order to create an alias and call the table columns using the alias, you should use the following syntax (find more detailed information on aliases here):

SELECT alias_name.column_name,
FROM table_name AS alias_name;

We want to generate a table that includes all the information from the first (left) table and one column from the second table, which contains only country names. So, the query looks like this:

SELECT
  table1.Name,
  table1.Solution,
  table1.Budget, 
  table2.Location
FROM
  `acuto-bigquery.clients.new_clients_22 ` AS table1
LEFT JOIN
  `acuto-bigquery.clients.new_clients_22_location` AS table2
ON
  (table1.Name = table2.Name);

Now, let’s run the query and see what results it yields.

Running a LEFT JOIN function in BigQuery

The output includes the first table with an additional column, as we predicted.

Note: You’ll find resources that use the term LEFT OUTER JOIN interchangeably with LEFT JOIN. Don’t get confused because they perform the same action.

Example 2: Using LEFT JOIN in BigQuery in Conjunction with the WHERE Clause

When working with large data sets, you will find yourself having to use complex filters to make use of the data. The LEFT JOIN function can help you output very specific parts of your data with ease

In this example, we’ll output the list of clients and their projects (along with their location), as well as filter them by project type. That’s because we want to know which clients require automation solutions.

And to do this, we’re using the WHERE clause along with the STARTS_WITH function to write a sub-condition that allows us to find the data entries that start with the keyword “Automation.”

The structure of this string function is:

STARTS_WITH(column_name, starting_value)

So, here’s how the query will look:

SELECT
  table1.Name,
  table1.Solution,
  table1.Budget, 
  table2.Location
FROM
  `acuto-bigquery.clients.new_clients_22` AS table1
LEFT JOIN
  `acuto-bigquery.clients.new_clients_22_location` AS table2
ON
  (table1.Name = table2.Name) 
WHERE STARTS_WITH(table1.Solution, "Automation") ;

Now, let’s see the output:

Using LEFT JOIN function together with WHERE and STRING_WITH

The output is as expected. As you can see, the LEFT JOIN function is one example of how easy it is to extract data from a BigQuery data warehouse. Even though the queries might get complex when working with larger data sets, they are still efficient.

Example 3: Using LEFT JOIN in BigQuery with Multiple Tables

So far, we’ve managed to extract data that resides in the left table and data from a second table that fulfils a certain condition. But what if we were to extract data from a third table, too?

First, let’s introduce a third table containing deadlines.

table showing information about clients’ deadlines

Third table (new_clients_22_status): information about clients’ deadlines

Now, let’s say that we want to have all the client information from the first table along with their location from the second table. But we want to extract only the results for the clients we have not delivered the work to yet. In that case, the query will look like this:

SELECT
  table1.Name,
  table1.Solution,
  table1.Budget, 
  table2.Location,
  table3.Delivered,
FROM
  `acuto-bigquery.clients.new_clients_22 ` AS table1
LEFT JOIN
  `acuto-bigquery.clients.new_clients_22_location` AS table2
ON
  (table1.Name = table2.Name) 
LEFT JOIN 
`acuto-bigquery.clients.new_clients_22_status` AS table3
ON
  (table1.Name = table3.Name) 
WHERE STARTS_WITH (table3.Delivered, "No");

After running the query, it results in information about one client for whom there is uncompleted work. If we want to extract the deadline as well, we could add it subsequently after the column names that come after the SELECT statement.

BigQuery Data Warehouse by Acuto

Adopting a data warehouse solution is not quite affordable if you go with mainstream solutions. On top of that, you might also need to hire a specialised data scientist who can implement the data warehouse solution.

Our team offers a seamless data warehouse solution. Composed of specialists who have mastered data and marketing, our team designs and creates data warehousing solutions that can be used to store and manage your data with minimal input required from your side.

Acuto helps you bring all of your essential data into a Single Source of Truth (SSOT). That means that you don’t have to constantly move through disparate channels when analysing your operations and advertising campaigns.

We’ll build and fully manage a custom data warehouse for you, whether you’re trying to analyse your Facebook Ads data (like Social Tap was doing) or are interested in managing data for another process. Check for a free spot on our calendar and book a call today for more detailed information.

Conclusion

The BigQuery LEFT JOIN function is one of the functions that is executed on a daily basis by a data worker in order to extract useful data for reports. Using this function, as long as you have a strong foundation in the BigQuery basics, you can extract results from any table and match data entries from other tables.
Data warehouses like BigQuery can literally set you up for scalable success. If you’re serious about growing your company, the first thing to do is reduce the time you spend on tasks that can be automated. Acuto is here to help you save more time on your data management infrastructure. Check out how we do it.