Combine Data in Tables with Joins in Google BigQuery

You can combine the data in two tables by creating a join between the tables.

One table contains City and Revenue columns. A second table contains City and Profit columns. Relate the data in both tables by creating a join between the City columns.

To combine data in three or more tables, create a join between two of the tables, then create a join between one of those two tables and a third table, etc. until all of the tables are joined.

The syntax of the JOIN clause that you write depends on the size of the tables you are joining. It is helpful to know before creating a join whether the tables contain more than 8 MB of compressed data, which is Google BigQuery's maximum for tables joined with the default JOIN clause. Lookup tables typically do not contain more than 8 MB of compressed data. Fact tables are more likely to contain more than 8 MB of compressed date.

To Create a Join that Combines Data in Two Tables

You can create a join that combines data in two tables on the Import from Tables dialog while building a query for importing your data.
If one table contains more than 8 MB of compressed data and the other does not, drag the column name from the larger table to the corresponding column name in the smaller table. This creates an inner join. A line representing the join appears in the Query Builder pane, running from the first column to the second. Go to step 6.
If both tables contain more than 8 MB of compressed data or both tables contain less than 8 MB of compressed data, drag the column name from one table onto the column from another table. This creates an inner join. A line representing the join appears in the Query Build pane, running from the first column, to the second. If the tables you joined contain more than 8 MB of compressed data, go to step 8. Otherwise, go to step 6.
If you do not know the size of the tables you are joining, drag the column name from one table onto the name of the corresponding column in the second table.
Click Execute SQL to test whether the join is valid.

If a preview of your data appears in the Sample Preview pane, then the join is valid and was successfully created. The second table that you joined contains less than 8 MB of compressed data. Go to step 6.

If an error appears stating that the JOIN operator's right-side table is small, then the second table that you joined is too large to create the join. Click the join and choose Delete. Create a new join by dragging the column name from the second table to the corresponding column name in the first table. Click Execute SQL.

If a preview of your data appears in the Sample Preview pane, the join was successfully created. Go to step 6.

If the error message appears a second time, then both tables contains over 8 MB of compressed data. Go to step 8 to edit the SQL query for importing your data.
Once you create a join, you can change its type or delete it. Click the line representing the join.

Google BigQuery does not support other join types, such as a full outer join or right outer join. In addition, Google BigQuery uses the default equals (=) operator to compare columns and does not support other operators.
Select Inner Join to only include records in which the joined columns from both tables satisfy the join condition.
Select Left Outer Join (default) to include all records from the column in the first table and only those records from the column in the second table in which the join condition is satisfied.
Select Delete to delete the join.
You are creating a join for tables with more than 8 MB of compressed data. Therefore, you will need to modify the query. Before doing so, add any additional columns you want to import. Create any filters, aggregations, or expressions based on the columns you are exporting.
Click Edit SQL. The SQL for importing your data appears in the Query Builder pane.
After the word, JOIN, type EACH.
Continue importing your data or customizing your query.

Combine Data in Tables with Joins in Google BigQuery

To Create a Join that Combines Data in Two Tables

Related Topics