概要

JOIN を使用すると、フィールドを追加してデータセットを拡充できます（水平方向）。UNION を使用するとテーブルにデータを追加できます（垂直方向）。テーブル間の関係を把握したら、UNION を使用してレコードを統合テーブルに追加し、JOIN を使用して複数のソースのデータで結果を拡充することができます。

このラボでは、SQL の JOIN と UNION を使用して新しいレポート テーブルを作成する方法について詳しく学習します。

目標

このラボでは、次のタスクの実施方法について学びます。

• UNION と JOIN によるデータセットの結合。
• テーブルの結合。
• NULL の使用。

設定と要件

各ラボでは、新しい Google Cloud プロジェクトとリソースセットを一定時間無料で利用できます。

1. Qwiklabs にシークレット ウィンドウでログインします。

2. ラボのアクセス時間（例: 1:15:00）に注意し、時間内に完了できるようにしてください。
   一時停止機能はありません。必要な場合はやり直せますが、最初からになります。

3. 準備ができたら、[ラボを開始] をクリックします。

4. ラボの認証情報（ユーザー名とパスワード）をメモしておきます。この情報は、Google Cloud Console にログインする際に使用します。

5. [Google Console を開く] をクリックします。

6. [別のアカウントを使用] をクリックし、このラボの認証情報をコピーしてプロンプトに貼り付けます。
   他の認証情報を使用すると、エラーが発生したり、料金の請求が発生したりします。

7. 利用規約に同意し、再設定用のリソースページをスキップします。

タスク 1. UNION と JOIN によるデータセットの結合の演習

BigQuery コンソールを開く

1. Google Cloud コンソールで、ナビゲーション メニュー > [BigQuery] を選択します。

[Cloud Console の BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスにはクイックスタート ガイドへのリンクと、UI の更新情報が表示されます。

2. [完了] をクリックします。

3. BigQuery エディタでクエリを作成します。

4. コードの 1 行目に必ず #standardSQL と記述されていることを確認します。

5. すべての IRS フォーム 990 申告に対して、暦年ごとに税務申告数をカウントするクエリを作成します。

6. 以下の一部作成済みのクエリを参考にしてください。

ヒント: _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

7. 以下の解答と比較します。

#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

8. クエリを実行し、以下の結果を確認します。

結果:

9. 作成したクエリを変更して、irs_990_YYYY という形式の IRS テーブルのみが含まれるようにします（pf、ez、ein を除外します）。部分的に作成済みの以下のクエリを使用します。

#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

10. 以下の解答と比較します。

#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

11. クエリを実行し、結果を確認します。

12. 最後に、2013 年以降のテーブルにある税務申告のみを含めるようにクエリを変更します。また追加の指標として、平均 totrevenue と平均 totfuncexpns も含めます。

ヒント: フィルタで _TABLE_SUFFIX を使用することを検討します。

13. 以下の解答と比較します。

#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

14. クエリを実行し、結果を確認します。

タスク 2. テーブルの結合の演習

2015 年に収益または費用が発生した、すべての EIN の組織名を探します。税務申告テーブルのデータを組織情報テーブルと結合する必要があります。

1. 以下のクエリに、テーブル、結合条件、必要とするフィルタを追加して完成させます。

#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;

2. クエリを以下の解答と比較します。

#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;

3. クエリを実行します。

4. 結果に 100 件のレコード、組織の名前、少なくともいくらかの費用または収益が示されていることを確認します。

5. BigQuery エディタをクリアします。

タスク 3. NULL の使用演習

2015 年の税務レコードのうち、対応する組織名がない税務申告を探すクエリを作成します。

1. 以下の一部作成済みのスターター クエリを完成させます。

#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)

2. 自分の解答を以下と比較します。

#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

3. クエリを実行します。

質問: 2015 年に発生した税務申告のうち、組織情報テーブルに対応するレコードがないものはいくつありますか？

回答: 14,123（一般公開のベーステーブルには新しい EIN 番号が追加されるため、これより多くなる場合もあります）

お疲れさまでした

これで、UNION と JOIN によるデータセットの結合ラボは終了です。

復習

• UNION ワイルドカードを使用して、複数のテーブルを 1 つのグループとして扱います。
• _TABLE_SUFFIX を使用して、ワイルドカード テーブルをフィルタリングし、テーブル名を使用して計算フィールドを作成します。
• FULL JOIN（FULL OUTER JOIN とも呼ばれます）は、結合キーに一致するかどうかにかかわらず、結合したそれぞれのテーブルにあるすべてのレコードを含みます。
• 一意でない結合キーがあると、意図しない CROSS JOIN（入力行よりも出力行が多くなる結果）が発生する可能性があるため、使用しないでください。
• COUNT() と GROUP BY を使用すると、キーフィールドが本当に一意かどうかを判断できます。

ラボを終了する

ラボが完了したら、[ラボを終了] をクリックします。ラボで使用したリソースが Google Cloud Skills Boost から削除され、アカウントの情報も消去されます。

ラボの評価を求めるダイアログが表示されたら、星の数を選択してコメントを入力し、[送信] をクリックします。

星の数は、それぞれ次の評価を表します。

• 星 1 つ = 非常に不満
• 星 2 つ = 不満
• 星 3 つ = どちらともいえない
• 星 4 つ = 満足
• 星 5 つ = 非常に満足

フィードバックを送信しない場合は、ダイアログ ボックスを閉じてください。

フィードバックやご提案の送信、修正が必要な箇所をご報告いただく際は、[サポート] タブをご利用ください。

Copyright 2026 Google LLC All rights reserved. Google および Google のロゴは、Google LLC の商標です。その他すべての社名および製品名は、それぞれ該当する企業の商標である可能性があります。