BigQuery SQL syntax<\/a> before reading this article.<\/p>\n[\/et_pb_text][et_pb_text module_id=”guide” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
A guide to SQL Joins in BigQuery<\/h2>\n
Before we start writing queries using BigQuery joins, we need to understand how these four types of joins work and how you can use them to merge data in BigQuery.<\/p>\n
INNER JOIN<\/b>: the inner join matches rows between two tables and returns only the matching rows. Therefore, when you combine data, it will leave out all the unmatched rows.<\/p>\n
LEFT JOIN<\/b>: a left join matches all rows from the left table even if no matching row exists in the right table. Here, BigQuery will return all the rows from the left table and the matched rows from the right table.<\/p>\n
RIGHT JOIN<\/b>: it is the opposite of a left join. It matches all rows from the right table even if no matching row exists in the left table.<\/p>\n
FULL JOIN<\/b>: a full join combines left and right joins. All rows from both tables are returned regardless of whether they match.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”types” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Join types explained<\/h2>\n
Here’s a visual representation of these four join types.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-1.png” alt=”BigQuery Joins 1″ title_text=”BigQuery Joins 1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, the inner join will return only the matching rows, a left join will return all the rows from the left table, a right join will return all the rows from the right table, and a full join will return all the rows from both tables.<\/p>\n
Here’s an example:<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-2.png” alt=”BigQuery Joins 2″ title_text=”BigQuery Joins 2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Let’s say I have these two tables containing information about NCAA basketball teams. While these tables share the “Name” column, they also contain some different items. I’ll combine the data from these two tables, joining the shared “Name” column.<\/p>\n
By using INNER JOIN, BigQuery would return only the matching rows. Here’s what the result would look like.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-3.png” alt=”BigQuery Joins 3″ title_text=”BigQuery Joins 3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, BigQuery returned the matching rows but left out all the unmatched rows.<\/p>\n
If I use LEFT join, BigQuery will return all the rows from the left table. As a result, it will leave out all the unmatched rows from the right table.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-4.png” alt=”BigQuery Joins 4″ title_text=”BigQuery Joins 4″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
By using RIGHT JOIN, BigQuery will return all the rows from the right table instead, leaving out the unmatched rows from the left table.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-5.png” alt=”BigQuery Joins 5″ title_text=”BigQuery Joins 5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Lastly, FULL JOIN will return all rows from both tables, including unmatched rows on either side.\u00a0<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-6.png” alt=”BigQuery Joins 6″ title_text=”BigQuery Joins 6″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In all these cases, BigQuery would return the word “null” to fill in for all the missing matches.<\/p>\n
Now that you know the differences between these types of join, we’ll learn how to combine data using these BigQuery joins.<\/p>\n
[\/et_pb_text][et_pb_text module_id=”inner” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
INNER JOIN<\/h3>\n
First, I’ll use the INNER JOIN keyword because I want BigQuery to return only matching rows from two tables. Here’s the syntax I’ll use:<\/p>\n
INNER JOIN Syntax<\/b><\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT column_name(s)<\/p>\n
FROM table1<\/p>\n
INNER JOIN table2<\/p>\n
ON table1.column_name = table2.column_name;<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
I\u2019ll show you how to use BigQuery joins to combine data from these NCAA basketball datasets. While one of the tables contains information about NCAA teams (mbb_teams), the other table contains data about these teams’ mascots. <\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT *\u00a0<\/p>\n
FROM `bigquery-public-data.ncaa_basketball.mascots` AS mascots<\/p>\n
INNER JOIN `bigquery-public-data.ncaa_basketball.mbb_teams` AS teams<\/p>\n
ON mascots.name = teams.name;<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Let’s break it down:<\/p>\n
1) First, I want to select all columns from the results. I’ll use the SELECT * clause.<\/p>\n
2) The FROM clause specifies the first table in the join. I’ve used an alias (AS) to give this table a temporary name (“mascots”).This enables me to use the alias later rather than write the full table name.<\/p>\n
3) The INNER JOIN clause specifies the second table. I’ve also used an alias to name this table.<\/p>\n
4) The ON statement is the condition matching the columns between the two tables. Instead of writing the table name, I used the aliases followed by the column: “name”.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-7.png” alt=”BigQuery Joins 7″ title_text=”BigQuery Joins 7″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”left” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
LEFT JOIN<\/h3>\n
After learning how to use the INNER JOIN statement, you can easily write a LEFT JOIN query by following the same pattern.<\/p>\n
LEFT JOIN Syntax<\/b><\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT column_name(s)<\/p>\n
FROM table1<\/p>\n
LEFT JOIN table2<\/p>\n
ON table1.column_name = table2.column_name;<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Here’s the complete formula.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT *\u00a0<\/p>\n
FROM `bigquery-public-data.ncaa_basketball.mascots` AS mascots<\/p>\n
LEFT JOIN `bigquery-public-data.ncaa_basketball.mbb_teams` AS teams<\/p>\n
ON mascots.name = teams.name;<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-8.png” alt=”BigQuery Joins 8″ title_text=”BigQuery Joins 8″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”right” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
RIGHT JOIN<\/h3>\n
Now let’s write a SQL query to return only the matching rows from the right table.<\/p>\n
RIGHT JOIN Syntax<\/b><\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT column_name(s)<\/p>\n
FROM table1<\/p>\n
RIGHT JOIN table2<\/p>\n
ON table1.column_name = table2.column_name;<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Here’s what the query would look like.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT *\u00a0<\/p>\n
FROM `bigquery-public-data.ncaa_basketball.mascots` AS mascots<\/p>\n
RIGHT JOIN `bigquery-public-data.ncaa_basketball.mbb_teams` AS teams<\/p>\n
ON mascots.name = teams.name;<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-9.png” alt=”BigQuery Joins 9″ title_text=”BigQuery Joins 9″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”full” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
FULL JOIN<\/h3>\n
Lastly, we can use FULL JOIN to write a query to return all rows from both tables, regardless of whether they match. Here’s the FULL JOIN syntax.<\/p>\n
FULL JOIN Syntax<\/b><\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT column_name(s)<\/p>\n
FROM table1<\/p>\n
FULL JOIN table2<\/p>\n
ON table1.column_name = table2.column_name;<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
That’s what the full query would look like.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT *\u00a0<\/p>\n
FROM `bigquery-public-data.ncaa_basketball.mascots` AS mascots<\/p>\n
FULL JOIN `bigquery-public-data.ncaa_basketball.mbb_teams` AS teams<\/p>\n
ON mascots.name = teams.name;<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-10.png” alt=”BigQuery Joins 10″ title_text=”BigQuery Joins 10″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Keep in mind that you can combine JOIN clauses with other statements. For example, I could combine these two tables, and use WHERE to filter specific data. Let’s say I want BigQuery to return only mascots starting with the letter “A”.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
SELECT *\u00a0<\/p>\n
FROM `bigquery-public-data.ncaa_basketball.mascots` AS mascots<\/p>\n
FULL JOIN `bigquery-public-data.ncaa_basketball.mbb_teams` AS teams<\/p>\n
ON mascots.name = teams.name<\/p>\n
WHERE mascot LIKE ‘A%’;<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/BigQuery-Joins-11.png” title_text=”BigQuery Joins 11″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
BigQuery joins: a step-by-step guide<\/h1>\n
There you have it! That’s how you can use BigQuery joins to combine data from multiple tables. If you want to learn more about BigQuery, check out this article on how to use BigQuery SQL syntax<\/a> to write your first queries.<\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
When you use BigQuery joins, you can combine data from multiple tables. By joining tables together, you can query data based on relationships and correlations across datasets. However, it’s important to mention that there are a few types of joins in BigQuery SQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Each join type […]<\/p>\n","protected":false},"author":42,"featured_media":43846,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[31],"tags":[],"class_list":["post-43680","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-science"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/43680","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/42"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=43680"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/43680\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/43846"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=43680"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=43680"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=43680"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}