Loading...
No results found.

Apply your skills in Google Cloud console

Lab setup instructions and requirements
Protect your account and progress. Always use a private browser window and lab credentials to run this lab.

Data to Insights: Unioning and Joining Datasets v1.1

Lab 1 hour universal_currency_alt 5 Credits show_chart Introductory
info This lab may incorporate AI tools to support your learning.
This content is not yet optimized for mobile devices.
For the best experience, please visit us on a desktop computer using a link sent by email.

Overview

JOINs enrich your dataset by potentially adding fields (horizontally). UNIONs append more data to your table (vertically). When you understand the relationships between your tables, use UNIONS to append records to a consolidated table, and JOINs to enrich your results with data from multiple sources.

This lab focuses on how to create new reporting tables using SQL JOINS and UNIONs.

Objectives

In this lab you learn how to perform the following tasks:

  • Describe Unioning and Joining Datasets.
  • Describe Joining Tables.
  • Describe Working with NULLs.

Setup and requirements

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

  1. Sign in to Google Skills using an incognito window.

  2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time. There is no pause feature. You can restart if needed, but you have to start at the beginning.

  3. When ready, click Start lab.

  4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

  5. Click Open Google Console.

  6. Click Use another account and copy/paste credentials for this lab into the prompts. If you use other credentials, you'll receive errors or incur charges.

  7. Accept the terms and skip the recovery resource page.

Task 1. Practice unioning and joining datasets

Open BigQuery Console

  1. In the Google Cloud Console, select Navigation menu > BigQuery.

The Welcome to BigQuery in the Cloud Console message box opens. This message box provides a link to the quickstart guide and lists UI updates.

  1. Click Done.

  1. Compose the query in BigQuery EDITOR.

  2. Ensure #standardSQL is set as your first line of code.

  3. Write a Query that will count the number of tax filings by calendar year for all IRS Form 990 filings.

  4. Use the below partially-written query as a guide.

Hint: You will need to use Table Wildcards * with _TABLE_SUFFIX.

#standardSQL # UNION Wildcard and returning a table suffix SELECT COUNT(*) as number_of_filings, AS year_filed FROM `bigquery-public-data.irs_990.irs_990` GROUP BY year_filed ORDER BY year_filed DESC
  1. Compare with the below solution:
#standardSQL # UNION Wildcard and returning a table suffix SELECT COUNT(*) as number_of_filings, _TABLE_SUFFIX AS year_filed FROM `bigquery-public-data.irs_990.irs_990_*` GROUP BY year_filed ORDER BY year_filed DESC
  1. Run the query and confirm against the results below.

Result:

wildcard query results

  1. Modify the query you just wrote to only include the IRS tables with the following format: irs_990_YYYY (i.e. filter out pf, ez, ein). Start with the partially completed query below:
#standardSQL # UNION Wildcard and returning a table suffix SELECT COUNT(*) as number_of_filings, CONCAT(,_TABLE_SUFFIX) AS year_filed FROM `bigquery-public-data.irs_990.irs_990_*` GROUP BY year_filed ORDER BY year_filed DESC
  1. Compare with the below solution:
#standardSQL # UNION Wildcard and returning a table suffix SELECT COUNT(*) as number_of_filings, CONCAT("2",_TABLE_SUFFIX) AS year_filed FROM `bigquery-public-data.irs_990.irs_990_2*` GROUP BY year_filed ORDER BY year_filed DESC
  1. Run the query and confirm the result:

Query results

  1. Lastly, modify your query to only include tax filings from tables on or after 2013. Also include average totrevenue and average totfuncexpns as additional metrics.

Hint: Consider using _TABLE_SUFFIX in a filter.

  1. Compare with the below solution:
#standardSQL # count of filings, revenue, expenses since 2013 SELECT CONCAT("20",_TABLE_SUFFIX) AS year_filed, COUNT(ein) AS nonprofit_count, AVG(totrevenue) AS avg_revenue, AVG(totfuncexpns) AS avg_expenses FROM `bigquery-public-data.irs_990.irs_990_20*` WHERE _TABLE_SUFFIX >= '13' GROUP BY year_filed ORDER BY year_filed DESC
  1. Run the query and confirm the result:

Query results

Task 2. Practice joining tables

Find the Org Names of all EINs for 2015 with some revenue or expenses. You will need to join tax filing table data with the organization details table.

  1. Start with the below query and fill in the tables, join condition, and any filter you will need:
#standard SQL # Find the Org Names of all EINs for 2015 with some revenue or expenses, limit 100 SELECT tax.ein AS tax_ein, org.ein AS org_ein, org.name, tax.totrevenue, tax.totfuncexpns FROM AS tax JOIN AS org ON tax.ein = WHERE > 0 LIMIT 100;
  1. Compare your query to the below solution:
#standardSQL # Find the Org Names of all EINs for 2015 with some revenue or expenses, limit 100 SELECT tax.ein AS tax_ein, org.ein AS org_ein, org.name, tax.totrevenue, tax.totfuncexpns FROM `bigquery-public-data.irs_990.irs_990_2015` AS tax JOIN `bigquery-public-data.irs_990.irs_990_ein` AS org ON tax.ein = org.ein WHERE tax.totrevenue + tax.totfuncexpns > 0 LIMIT 100;

  1. Run the Query.

  2. Confirm the results show 100 records, the names of the Organization, and at least some expenses or revenues.

  3. Clear the BigQuery EDITOR.

Task 3. Practicing working with NULLs

Write a query to find where tax records exist for 2015 but no corresponding Org Name.

  1. Fill out the partially written starter query below:
#standardSQL # Find where tax records exist for 2015 but no corresponding Org Name SELECT tax.ein AS tax_ein, org.ein AS org_ein, org.name, tax.totrevenue, tax.totfuncexpns FROM `bigquery-public-data.irs_990.irs_990_2015` tax FULL # Complete the JOIN `bigquery-public-data.irs_990.irs_990_ein` org ON WHERE IS NULL # put tax.ein or org.ein to check here (one is correct)
  1. Compare your solution to the below:
#standardSQL # Find where tax records exist for 2015 but no corresponding Org Name SELECT tax.ein AS tax_ein, org.ein AS org_ein, org.name, tax.totrevenue, tax.totfuncexpns FROM `bigquery-public-data.irs_990.irs_990_2015` tax FULL JOIN `bigquery-public-data.irs_990.irs_990_ein` org ON tax.ein = org.ein WHERE org.ein IS NULL
  1. Run the Query.

Question: How many tax filings occurred in 2015 but have no corresponding record in the Organization Details table?

Answer: 14,123 (your answer may be higher as new EIN numbers get added to the public base table)

Congratulations!

You have completed the UNIONING and JOINING Datasets lab.

Learning review

  • Use Union Wildcards to treat multiple tables as a single group
  • Use _TABLE_SUFFIX to filter wildcard tables and create calculated fields with the table name
  • FULL JOINs (also called FULL OUTER JOINs) include all records from each joined table regardless of whether there are matches on the join key
  • Having non-unique join keys can result in an unintentional CROSS JOIN (more output rows than input rows) which should be avoided
  • Use COUNT() and GROUP BY to determine if a key field is indeed unique

End your lab

When you have completed your lab, click End Lab. Google Skills removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

  • 1 star = Very dissatisfied
  • 2 stars = Dissatisfied
  • 3 stars = Neutral
  • 4 stars = Satisfied
  • 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2026 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.

Before you begin

  1. Labs create a Google Cloud project and resources for a fixed time
  2. Labs have a time limit and no pause feature. If you end the lab, you'll have to restart from the beginning.
  3. On the top left of your screen, click Start lab to begin

Use private browsing

  1. Copy the provided Username and Password for the lab
  2. Click Open console in private mode

Sign in to the Console

  1. Sign in using your lab credentials. Using other credentials might cause errors or incur charges.
  2. Accept the terms, and skip the recovery resource page
  3. Don't click End lab unless you've finished the lab or want to restart it, as it will clear your work and remove the project

This content is not currently available

We will notify you via email when it becomes available

Great!

We will contact you via email if it becomes available

One lab at a time

Confirm to end all existing labs and start this one

Use private browsing to run the lab

Using an Incognito or private browser window is the best way to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.