5. 結合質問（集合演算を含む）#
Show code cell source
import os
if not os.path.isfile('data/SSDSE.db'):
os.makedirs('data', exist_ok=True)
if not os.path.isfile('download_SSDSE.py'):
!wget https://raw.githubusercontent.com/hontolab-courses/database-lecturenote/main/content/sql/download_SSDSE.py
import download_SSDSE
Show code cell source
%load_ext sql
%config SqlMagic.feedback = 0
%config SqlMagic.displaylimit = 50
%sql sqlite:///data/SSDSE.db
前講で扱ったSQL文は，1つのテーブルに対する問い合わせ（単純質問）を対象としていた． しかし，関係データベースを用いたデータ分析の現場では，問い合わせ時に参照するテーブルが1つしかないことは稀である． 所望のデータを抽出するには，複数のテーブルを組み合わせることが大半である． 本章では，関係データベース内にある2つ以上のテーブルを組み合わせてデータを抽出する結合質問を扱う．
前講同様，SQLの説明を行うために，独立行政法人統計センターが公開している教育用標準データセット（SSDSE）の基本素材（SSDSE-E）および市区町村（SSDSE-A）から抜粋・加工したデータを用いる．
データはelderly_population_top10テーブル，university_student_population_top10テーブル，およびactivityテーブルの3種類ある．
各テーブル内のデータは2021年度調査によるものである．
elderly_population_top10テーブルには，65歳以上人口数が上位10位内であった都道府県の「地域コード」「都道府県名」「総人口」が格納されている．
SELECT * FROM elderly_population_top10;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R13000 | 東京都 | 14010000 |
| R27000 | 大阪府 | 8806000 |
| R14000 | 神奈川県 | 9236000 |
| R11000 | 埼玉県 | 7340000 |
| R23000 | 愛知県 | 7517000 |
| R12000 | 千葉県 | 6275000 |
| R01000 | 北海道 | 5183000 |
| R28000 | 兵庫県 | 5432000 |
| R40000 | 福岡県 | 5124000 |
| R22000 | 静岡県 | 3608000 |
university_student_population_top10テーブルには，大学学生数が上位10位内であった都道府県の「地域コード」「都道府県名」「総人口」が格納されている．
SELECT * FROM university_student_population_top10;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R13000 | 東京都 | 14010000 |
| R27000 | 大阪府 | 8806000 |
| R23000 | 愛知県 | 7517000 |
| R14000 | 神奈川県 | 9236000 |
| R26000 | 京都府 | 2561000 |
| R28000 | 兵庫県 | 5432000 |
| R40000 | 福岡県 | 5124000 |
| R11000 | 埼玉県 | 7340000 |
| R12000 | 千葉県 | 6275000 |
| R01000 | 北海道 | 5183000 |
activityテーブルには，「学習・自己啓発・訓練」「スポーツ」「趣味・娯楽」「ボランティア」「旅行・行楽」について過去1年以内に活動したことのある人の割合（％）について，都道府県別にまとめたものが格納されている．
SELECT * FROM activity;
| 地域コード | 都道府県 | 学習・自己啓発・訓練 | スポーツ | 趣味娯楽 | ボランティア | 旅行・行楽 |
|---|---|---|---|---|---|---|
| R01000 | 北海道 | 35.0 | 62.2 | 85.4 | 16.3 | 51.0 |
| R02000 | 青森県 | 25.4 | 52.1 | 78.6 | 14.0 | 36.6 |
| R03000 | 岩手県 | 28.9 | 59.1 | 82.9 | 24.4 | 45.3 |
| R04000 | 宮城県 | 37.5 | 64.4 | 87.0 | 20.7 | 52.2 |
| R05000 | 秋田県 | 29.2 | 57.1 | 82.1 | 20.9 | 44.8 |
| R06000 | 山形県 | 31.1 | 58.4 | 82.4 | 23.6 | 44.3 |
| R07000 | 福島県 | 31.5 | 59.5 | 81.5 | 19.7 | 44.4 |
| R08000 | 茨城県 | 36.5 | 65.8 | 85.3 | 17.1 | 45.0 |
| R09000 | 栃木県 | 32.8 | 62.6 | 83.9 | 16.5 | 47.3 |
| R10000 | 群馬県 | 35.2 | 67.0 | 85.2 | 19.2 | 50.8 |
| R11000 | 埼玉県 | 40.0 | 69.3 | 88.4 | 15.6 | 51.5 |
| R12000 | 千葉県 | 42.0 | 67.4 | 87.0 | 14.9 | 47.9 |
| R13000 | 東京都 | 52.8 | 74.5 | 91.4 | 15.5 | 55.5 |
| R14000 | 神奈川県 | 46.7 | 71.8 | 89.2 | 17.0 | 54.5 |
| R15000 | 新潟県 | 34.5 | 59.0 | 84.0 | 19.3 | 47.6 |
| R16000 | 富山県 | 33.4 | 59.7 | 82.6 | 19.5 | 41.1 |
| R17000 | 石川県 | 35.6 | 63.4 | 86.2 | 20.7 | 44.7 |
| R18000 | 福井県 | 35.8 | 61.5 | 85.2 | 21.7 | 44.3 |
| R19000 | 山梨県 | 36.5 | 62.2 | 83.1 | 21.1 | 46.7 |
| R20000 | 長野県 | 36.6 | 64.0 | 84.8 | 22.0 | 46.9 |
| R21000 | 岐阜県 | 36.1 | 64.1 | 84.9 | 21.7 | 48.6 |
| R22000 | 静岡県 | 36.5 | 66.3 | 85.1 | 18.9 | 41.3 |
| R23000 | 愛知県 | 39.0 | 68.8 | 89.3 | 16.6 | 57.6 |
| R24000 | 三重県 | 34.4 | 64.3 | 84.2 | 19.3 | 47.0 |
| R25000 | 滋賀県 | 39.7 | 67.2 | 86.4 | 24.2 | 52.2 |
| R27000 | 大阪府 | 41.3 | 66.1 | 86.7 | 14.5 | 51.9 |
| R28000 | 兵庫県 | 39.5 | 66.4 | 85.4 | 17.7 | 51.6 |
| R29000 | 奈良県 | 39.8 | 64.8 | 86.2 | 18.5 | 50.4 |
| R30000 | 和歌山県 | 31.3 | 59.8 | 83.3 | 18.2 | 44.1 |
| R31000 | 鳥取県 | 34.1 | 61.2 | 81.7 | 24.1 | 38.4 |
| R32000 | 島根県 | 33.3 | 61.4 | 82.5 | 25.6 | 39.8 |
| R33000 | 岡山県 | 37.1 | 64.8 | 85.0 | 23.2 | 43.0 |
| R34000 | 広島県 | 38.4 | 65.3 | 85.1 | 18.6 | 47.1 |
| R35000 | 山口県 | 34.8 | 63.2 | 83.9 | 20.1 | 43.5 |
| R36000 | 徳島県 | 34.1 | 60.9 | 80.6 | 17.4 | 32.2 |
| R37000 | 香川県 | 34.1 | 61.8 | 82.9 | 18.1 | 39.0 |
| R38000 | 愛媛県 | 34.5 | 64.0 | 84.3 | 19.6 | 37.4 |
| R39000 | 高知県 | 30.3 | 59.4 | 81.7 | 18.9 | 39.2 |
| R40000 | 福岡県 | 37.7 | 67.0 | 85.8 | 19.1 | 52.3 |
| R41000 | 佐賀県 | 37.2 | 62.8 | 82.1 | 24.8 | 47.5 |
| R42000 | 長崎県 | 31.2 | 57.8 | 79.7 | 21.0 | 36.1 |
| R43000 | 熊本県 | 35.7 | 66.6 | 84.1 | 22.1 | 50.5 |
| R44000 | 大分県 | 33.2 | 63.1 | 82.3 | 20.3 | 45.0 |
| R45000 | 宮崎県 | 31.3 | 65.8 | 81.6 | 20.1 | 42.8 |
| R46000 | 鹿児島県 | 35.2 | 63.5 | 81.0 | 22.3 | 43.3 |
| R47000 | 沖縄県 | 36.6 | 65.8 | 80.0 | 17.4 | 31.1 |
以下，これら3つのテーブルが格納された関係データベースが手元にあると想定して，結合質問について説明する．
5.1. 集合演算#
高校数学で学習した「集合と論理」を覚えておられるだろうか（忘れた人はコチラを確認）． そのときに
\(A=\{1, 2, 3, 4, 9\}\)，\(B=\{3, 4, 7\}\)のとき，AとBの共通部分\(A \cap B\)は\(\{3, 4\}\)となる
みたいなことを勉強したと思う． 集合演算とは，上記のように2つの集合から別の集合を作り出す演算である． 高校数学で習った集合演算では主として数値の集合を対象としていたと思うが， 関係データベースの集合演算において対象となるのはレコード（行）の集合である． 関係データベースにおける集合演算は行を増やしたり減らしたりする，テーブルを縦方向にいじる操作ともいえる． 関係データベースにおける集合演算の振る舞いは，直感的には高校数学の集合演算と変わらない．
Warning
関係データベースにおける集合演算の注意点
関係データベースで集合演算を行う際には，演算対象となる2つのレコード集合が和両立（unicon compatible, 合併可能と呼ぶこともある）という条件を満たしている必要がある． これは，演算対象となる2つのレコード集合が
同じ列（属性）で構成されている，かつ
同じ列については，その定義域も同じである
という条件である． この条件を満たしていないレコード集合の集合演算を行おうとすると，SQL文を実行してもエラーが出力される．
5.1.1. 和集合演算（UNION）#
和集合演算は，2つのレコード集合のいずれかに含まれるレコードの集合（和集合または合併，union）を求めるものである． 高校数学で例えると，
\(A=\{1, 2, 3, 4, 9\}\)，\(B=\{3, 4, 7\}\)のとき，AとBの和集合\(A \cup B\)は\(\{1, 2, 3, 4, 7, 9\}\)
となるような演算の関係データベース版である．
SQLで和集合を求めるにはUNION演算子を用いる．
レコード集合Aとレコード集合Bの和集合を求めるには，A UNION BのようなSQL文を書く．
\(A\)や\(B\)はレコード集合であるので，実際にSQL文でUNIONを使う際には，AやBの箇所にSELECT文が入る．
以下は，elderly_population_top10テーブルとuniversity_student_population_top10テーブルの和集合を求めるSQL文である．
SELECT * FROM elderly_population_top10
UNION
SELECT * FROM university_student_population_top10;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R01000 | 北海道 | 5183000 |
| R11000 | 埼玉県 | 7340000 |
| R12000 | 千葉県 | 6275000 |
| R13000 | 東京都 | 14010000 |
| R14000 | 神奈川県 | 9236000 |
| R22000 | 静岡県 | 3608000 |
| R23000 | 愛知県 | 7517000 |
| R26000 | 京都府 | 2561000 |
| R27000 | 大阪府 | 8806000 |
| R28000 | 兵庫県 | 5432000 |
| R40000 | 福岡県 | 5124000 |
なお，（後述する積集合，差集合も同様であるが）和集合演算子UNIONの前後にはSELECT文であればよく，当然SELECT文の中にはWHERE句などを含んでいてもよい．
以下は，elderly_population_top10テーブルとuniversity_student_population_top10テーブルのレコードのうち，総人口が700万以上のレコードに限定して和集合を求めるSQL文である．
SELECT
*
FROM
elderly_population_top10
WHERE
総人口 >= 7000000
UNION
SELECT
*
FROM
university_student_population_top10
WHERE
総人口 >= 7000000;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R11000 | 埼玉県 | 7340000 |
| R13000 | 東京都 | 14010000 |
| R14000 | 神奈川県 | 9236000 |
| R23000 | 愛知県 | 7517000 |
| R27000 | 大阪府 | 8806000 |
5.1.2. 積集合演算（INTERSECT）#
積集合演算は，2つのレコード集合に共通して含まれるレコードの集合（積集合または共通集合，intersection）を求めるものである． 高校数学で例えると，
\(A=\{1, 2, 3, 4, 9\}\)，\(B=\{3, 4, 7\}\)のとき，AとBの積集合\(A \cap B\)は\(\{3, 4\}\)
となるような演算の関係データベース版である．
SQLで積集合を求めるにはINTERSECT演算子を用いる．
以下は，elderly_population_top10テーブルとuniversity_student_population_top10テーブルの積集合を求めるSQL文である（★Quiz1★，★Quiz2★）．
SELECT * FROM elderly_population_top10
INTERSECT
SELECT * FROM university_student_population_top10;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R01000 | 北海道 | 5183000 |
| R11000 | 埼玉県 | 7340000 |
| R12000 | 千葉県 | 6275000 |
| R13000 | 東京都 | 14010000 |
| R14000 | 神奈川県 | 9236000 |
| R23000 | 愛知県 | 7517000 |
| R27000 | 大阪府 | 8806000 |
| R28000 | 兵庫県 | 5432000 |
| R40000 | 福岡県 | 5124000 |
5.1.3. 差集合演算（EXCEPT）#
差集合演算は，あるレコード集合から別のレコード集合に含まれるレコードを除いたレコードの集合（差集合あるいは補集合，set difference）を求めるものである． 高校数学の範囲で例えると，
\(A=\{1, 2, 3, 4, 9\}\)，\(B=\{3, 4, 7\}\)のとき，AとBの積集合\(A \setminus B\)は\(\{1, 2, 9\}\)
となるような演算の関係データベース版である． 差集合演算は，片側の集合にしか現れない要素の集合を求めたいときに用いる．
SQLで積集合を求めるにはEXCEPT演算子を用いる．
以下は，elderly_population_top10テーブルからuniversity_student_population_top10テーブルを引いた差集合を求めるSQL文である．
SELECT * FROM elderly_population_top10
EXCEPT
SELECT * FROM university_student_population_top10;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R22000 | 静岡県 | 3608000 |
和集合演算や積集合演算とは異なり，差集合演算は演算の項の順序を入れ替えても結果は変わらない「交換法則」は成立しない． つまり，\(A \setminus B\)と\(B \setminus A\)はイコールになるとは限らない．
例えば，以下は上で求めた例とは演算の項を入れ替えた，university_student_population_top10テーブルからelderly_population_top10テーブルを引いた差集合を求めるSQL文である．
このSQL文と先のSQL文とで結果が異なることが分かる．
SELECT * FROM university_student_population_top10
EXCEPT
SELECT * FROM elderly_population_top10;
| 地域コード | 都道府県 | 総人口 |
|---|---|---|
| R26000 | 京都府 | 2561000 |
5.2. 結合（JOIN）#
結合（JOIN） とは，2つのテーブルからデータを特定の条件でデータを組み合わせて新たなレコード集合を得る操作である．
前節で扱ったUNION，INTERSECT，EXCEPTといった集合演算がテーブルを縦方向（行方向）に操作するのに対して，結合操作はテーブルを横方向（列方向） に操作する．
データをあえて複数のテーブルに分けて管理する思想をもつ関係データベースにおいては，結合操作を使いこなすことが多様かつ膨大なデータの管理・検索・分析を行う上で鍵となる．
以下，直積，内部結合，外部結合の3つの結合について説明する．
5.2.1. 直積（CROSS JOIN）#
集合論において，集合AとBの直積（デカルト積とも呼ぶ） とは，集合Aと集合Bのそれぞれの要素のすべての組み合わせ（の集合）を意味する． 例えば，集合\(A=\{1, 2, 3\}\)，集合\(B=\{a, b\}\)のとき，集合Aと集合Bの直積である\(A \times B\)は
\(A \times B = \{(1, a), (1, b), (2, a), (2, b), (3, a), (3, b)\}\)
となる．
関係データベースにおいてもテーブル同士の直積が定義されている． 関係データベースにおける直積（交差結合; cross joinと呼ぶこともある） は，2つのテーブル\(R_1\)と\(R_2\)が与えられたとき，テーブル\(R_1\)のレコードと\(R_2\)のレコードのすべての組み合わせを求める操作となる．
2つのテーブルAとBの直積を求めるSQL文は，
SELECT * FROM A CROSS JOIN B;
と書く．
以下のように，上記SQL文はCROSS JOINをカンマ（,）を省略しても書くこともできる．
SELECT * FROM A, B; -- CROSS JOINをカンマで代替
具体例を使って直積の結果を確認してみよう． 例えば，以下のような「科目」テーブルと「教員」テーブルの2つがあるとする．
| 科目名 | 開講時期 |
|---|---|
| データベース | 2年前期 |
| 機械学習 | 3年前期 |
| 統計モデリング | 2年後期 |
| 氏名 | 職階 |
|---|---|
| 山畑 | 教授 |
| 桜山 | 教授 |
| 川澄 | 准教授 |
| 田辺 | 講師 |
「科目」テーブルには3レコード，「教員」テーブルには4レコード格納されている． 上記2つのテーブルのデータを元に，科目を担当する教員について考えられうるすべての組み合わせを求めるには， 以下のように2つのテーブルの直積を求めれるSQL文を書けばよい．
SELECT
*
FROM
科目, 教員;
| 科目名 | 開講時期 | 氏名 | 職階 |
|---|---|---|---|
| データベース | 2年前期 | 山畑 | 教授 |
| データベース | 2年前期 | 桜山 | 教授 |
| データベース | 2年前期 | 川澄 | 准教授 |
| データベース | 2年前期 | 田辺 | 講師 |
| 機械学習 | 3年前期 | 山畑 | 教授 |
| 機械学習 | 3年前期 | 桜山 | 教授 |
| 機械学習 | 3年前期 | 川澄 | 准教授 |
| 機械学習 | 3年前期 | 田辺 | 講師 |
| 統計モデリング | 2年後期 | 山畑 | 教授 |
| 統計モデリング | 2年後期 | 桜山 | 教授 |
| 統計モデリング | 2年後期 | 川澄 | 准教授 |
| 統計モデリング | 2年後期 | 田辺 | 講師 |
2つのテーブルの直積を求めると，合計\(3 \times 4=12\)レコードの組み合わせ（レコード集合）が得られる（★Quiz3★）．
直積を求めるSQL文では，WHERE句を使って抽出結果を選択（絞り込む）ことができる．
大学学生数が多い上位10の都道府県名が格納された
university_student_population_top10テーブル，「学習・自己啓発・訓練」など，いくつかの活動について過去1年以内に活動したことのある人の割合（％）が都道府県別にまとめられている
activityテーブル
の直積を求めた後，都道府県名が一致しているものだけを残して表示してみよう． 以下は所望の結果を得るためのSQL文である．
Show code cell source
%sql sqlite:///data/SSDSE.db
SELECT
*
FROM
university_student_population_top10, activity
WHERE
--- 「テーブル名.都道府県」とすることで，どのテーブルの「都道府県」を参照したいかを明示
university_student_population_top10.都道府県 = activity.都道府県;
| 地域コード | 都道府県 | 総人口 | 地域コード_1 | 都道府県_1 | 学習・自己啓発・訓練 | スポーツ | 趣味娯楽 | ボランティア | 旅行・行楽 |
|---|---|---|---|---|---|---|---|---|---|
| R13000 | 東京都 | 14010000 | R13000 | 東京都 | 52.8 | 74.5 | 91.4 | 15.5 | 55.5 |
| R27000 | 大阪府 | 8806000 | R27000 | 大阪府 | 41.3 | 66.1 | 86.7 | 14.5 | 51.9 |
| R23000 | 愛知県 | 7517000 | R23000 | 愛知県 | 39.0 | 68.8 | 89.3 | 16.6 | 57.6 |
| R14000 | 神奈川県 | 9236000 | R14000 | 神奈川県 | 46.7 | 71.8 | 89.2 | 17.0 | 54.5 |
| R28000 | 兵庫県 | 5432000 | R28000 | 兵庫県 | 39.5 | 66.4 | 85.4 | 17.7 | 51.6 |
| R40000 | 福岡県 | 5124000 | R40000 | 福岡県 | 37.7 | 67.0 | 85.8 | 19.1 | 52.3 |
| R11000 | 埼玉県 | 7340000 | R11000 | 埼玉県 | 40.0 | 69.3 | 88.4 | 15.6 | 51.5 |
| R12000 | 千葉県 | 6275000 | R12000 | 千葉県 | 42.0 | 67.4 | 87.0 | 14.9 | 47.9 |
| R01000 | 北海道 | 5183000 | R01000 | 北海道 | 35.0 | 62.2 | 85.4 | 16.3 | 51.0 |
Warning
複数のテーブルを参照するときの注意点
複数のテーブルに同一の列名が存在する際，SQL文内で列名だけを指定すると，どのテーブルの列を参照しているのかが明確でないためエラーが発生する．
これを防ぐために，複数のテーブルを参照するときには，列名の前にテーブル名.をつけて列を参照するテーブル名を明確化すること．
5.2.2. 内部結合（INNER JOIN）#
内部結合（inner join） は，2つのテーブルにおいて指定した列の値が条件を満たすレコードのみを結合して抽出する操作である． 通常，結合あるいはジョインという時は内部結合を指す． テーブルAにテーブルBを内部結合するためのSQL文は
FROM Aの後でINNER JOIN B（あるいは省略してJOIN B）と書き，INNER JOIN Bの後に2つのテーブルの結合条件をON句で指定する
というフォーマットになる．
例えば，前節「直積」で扱った，
大学学生数が多い上位10の都道府県名が格納された
university_student_population_top10テーブル，「学習・自己啓発・訓練」など，いくつかの活動について過去1年以内に活動したことのある人の割合（％）が都道府県別にまとめられている
activityテーブル
の2テーブルについて「都道府県名」で内部結合をしてみよう．
SELECT
--- 「テーブル名.*」 で「そのテーブルにあるすべての列」を意味する
university_student_population_top10.*,
--- 「テーブル名.*」でなく，「テーブル名.列名」と書くと，
--- 該当テーブルの特定の列の情報だけを参照可能
activity.学習・自己啓発・訓練
FROM
university_student_population_top10
INNER JOIN
activity
ON
university_student_population_top10.都道府県 = activity.都道府県;
| 地域コード | 都道府県 | 総人口 | 学習・自己啓発・訓練 |
|---|---|---|---|
| R13000 | 東京都 | 14010000 | 52.8 |
| R27000 | 大阪府 | 8806000 | 41.3 |
| R23000 | 愛知県 | 7517000 | 39.0 |
| R14000 | 神奈川県 | 9236000 | 46.7 |
| R28000 | 兵庫県 | 5432000 | 39.5 |
| R40000 | 福岡県 | 5124000 | 37.7 |
| R11000 | 埼玉県 | 7340000 | 40.0 |
| R12000 | 千葉県 | 6275000 | 42.0 |
| R01000 | 北海道 | 5183000 | 35.0 |
前節のSQL文と似たような結果が出力されたが，処理手順は異なる．
前節のSQL文はいったん直積ですべての組み合わせを求めた後，都道府県名が一致するものを調べて結果を表示している．
これに対し，内部結合を用いた上記SQL文では，INNER JOIN句で指定した条件を満たす組み合わせのみを抽出し，その後それをすべて選択（表示）するという順で処理を行っている（★Quiz4★，★Quiz5★）．
Tip
USING句を使った省略記法
2つのテーブルを内部結合する際，結合するテーブルの列名が等しい（同じ列名で結合する）かつ結合条件が等号である場合は，以下のようにON句の代わりにUSING句を使って簡単に書くことができる．
以下は上記SQL文をUSING句を使って書き換えたものである．
SELECT
university_student_population_top10.*,
activity.学習・自己啓発・訓練
FROM
university_student_population_top10
INNER JOIN
activity
USING(都道府県);
（後述する外部結合も含めて）JOIN句とWHERE句を組み合わせる際には，JOINが行われる順序を意識することが重要となる．
例えば，先の内部結合の例題において「総人口が700万以上の都道府県に限定」したいとしよう．
そのような場合，以下のようにJOIN句の後にWHERE句で選択条件を指定する．
こうすることで，JOIN句で特定の条件でテーブルを結合した後，WHERE句で結合結果を絞り込むことができる．
SELECT
university_student_population_top10.*,
activity.学習・自己啓発・訓練
FROM
university_student_population_top10
INNER JOIN
activity
ON
university_student_population_top10.都道府県 = activity.都道府県
WHERE
university_student_population_top10.総人口 >= 7000000;
| 地域コード | 都道府県 | 総人口 | 学習・自己啓発・訓練 |
|---|---|---|---|
| R13000 | 東京都 | 14010000 | 52.8 |
| R27000 | 大阪府 | 8806000 | 41.3 |
| R23000 | 愛知県 | 7517000 | 39.0 |
| R14000 | 神奈川県 | 9236000 | 46.7 |
| R11000 | 埼玉県 | 7340000 | 40.0 |
Tip
AS修飾句
長たらしいテーブル名，列名を繰り返し使うのは面倒である．
また紛らわしいテーブル名，列名は混乱の元である．
AS修飾句を使うことで，テーブル名や列名に別名を与えることができる．
SELECT
university_student_population_top10.*,
--- 列名に別名「学習・自己鍛錬」を与える
activity.学習・自己啓発・訓練 AS 学習・自己鍛錬
FROM
---テーブルに別名populationを与える
university_student_population_top10 AS u_population
INNER JOIN
activity
ON
u_population.都道府県 = activity.都道府県 --- 別名でテーブルを参照
WHERE
u_population.総人口 >= 7000000; --- 別名でテーブルを参照
5.2.3. 外部結合（OUTER JOIN）#
内部結合は，片方のテーブル\(R_1\)にあるレコード\(r\)に対して，もう片方のテーブル\(R_2\)のレコードに結合条件を満たす列データがあるときに限ってレコード同士を結合し，その結果を表示するものであった． このことは，結合条件を満たすレコードがもう片方のテーブル\(R_2\)にない場合は\(r\)は無視されることを意味する．
実は例題に用いたactivityテーブルには「京都府」に関するレコードがない（授業用にデータを用意するときに含めないようにしていた）．
そのため，内部結合の例題ではuniversity_student_population_top10テーブルとactivityテーブルを内部結合すると，「京都府」に関するレコードが結合結果に残らない（表示されない）のである．
このような挙動をする内部結合に対して，結合条件を満たさないレコードがあった時でもそのことを示す情報を付与したレコードを残しつつ，テーブル同士を結合する演算が外部結合（outer join） である． 外部結合では，結合条件を満たさないレコードがあった場合，値が存在しない列にNULL値（ヌル値） と呼ばれる「値がないこと」を示す情報を入れて結果を出力する．
テーブルAにテーブルBを外部結合するためのSQL文は
FROM Aの後でLEFT OUTER JOIN Bと書き，LEFT OUTER JOIN Bの後に2つのテーブルの結合条件をON句で指定する
というフォーマットになる．
例を見てみよう． 以下は，内部結合の節で導入した1つ目のSQL文を外部結合に書き換えたものである．
SELECT
university_student_population_top10.*,
activity.学習・自己啓発・訓練
FROM
university_student_population_top10
LEFT OUTER JOIN --- （左）外部結合に書き換え
activity
ON
university_student_population_top10.都道府県 = activity.都道府県;
| 地域コード | 都道府県 | 総人口 | 学習・自己啓発・訓練 |
|---|---|---|---|
| R13000 | 東京都 | 14010000 | 52.8 |
| R27000 | 大阪府 | 8806000 | 41.3 |
| R23000 | 愛知県 | 7517000 | 39.0 |
| R14000 | 神奈川県 | 9236000 | 46.7 |
| R26000 | 京都府 | 2561000 | None |
| R28000 | 兵庫県 | 5432000 | 39.5 |
| R40000 | 福岡県 | 5124000 | 37.7 |
| R11000 | 埼玉県 | 7340000 | 40.0 |
| R12000 | 千葉県 | 6275000 | 42.0 |
| R01000 | 北海道 | 5183000 | 35.0 |
内部結合の例題とは異なり，「京都府」に関するレコードが表示され，「学習・自己啓発・訓練」列の値がNULLになっていることが分かる（上記結果ではNULLがNoneと表示されているが，NULLと読み替えてほしい）．
外部結合は結合条件を満たすレコードがなかったとしても，それを把握したいときなどに利用できる．
なお，上記の例題からも分かるように，外部結合には左外部結合（left outer join） と右外部結合（right outer join），および完全外部結合（full outer join） の3種類が存在する[1]． 左外部結合が結合対象の左テーブルのレコードを全て残すのに対して，
右外部結合は（結合条件を満たさなかった場合でも）結合対象の右テーブルのレコードを全て残し，
完全外部結合は（結合条件を満たさなかった場合でも）左右両方のテーブルのレコードを全て残す
演算となる． まずは左外部結合をマスターしよう（★Quiz6★，★Quiz7★）．
Note
NULLと空文字は異なる
NULLは値が存在しないことを意味する情報である．
文字列が入ることが想定される列にNULL値が入っている場合は「その列には何らかの理由で値が設定されなかった」ことを意味する．
一方，空文字（''）は長さが0の文字列である．
もし文字列が入ることが想定される列に空文字が入っている場合は「意図を持って文字がないことを示すために空文字が入れられた」ことを意味する．SQLではNULLと空文字と厳格に区別しているため，扱いには注意しよう．
5.3. クイズ#
本クイズでは，独立行政法人統計センターが公開している教育用標準データセット（SSDSE）の基本素材SSDSE-E（データの解説はこちら）および市区町村（SSDSE-A）から抜粋・加工したデータを用いる．
を参照せよ．
なお，データをダウンロードしていない者は，以下のコードを実行してデータをダウンロードしてからクイズに取り組むこと．
import os
if not os.path.isfile('data/SSDSE.db'):
os.makedirs('data', exist_ok=True)
if not os.path.isfile('download_SSDSE.py'):
!wget https://raw.githubusercontent.com/hontolab-courses/database-lecturenote/main/content/sql/download_SSDSE.py
import download_SSDSE
%load_ext sql
%config SqlMagic.feedback = 0
%sql sqlite:///data/SSDSE.db
5.3.1. Q1. 復習#
前講で用いたpopulationテーブルにあるレコードのうち，大学学生数が7万以上かつ小学校児童数が30万以上の都道府県名を表示するSQL文を書け．
なお，表示の際には都道府県名の重複は除くこと．
%%sql
5.3.2. Q2. 集合演算#
上記Q1と同様の内容を表示するSQL文をINTERSECT句を用いて書け．
%%sql
5.3.3. Q3. 直積#
populationテーブルとactivityテーブルの直積を求めるSQL文を書け．
%%sql
5.3.4. Q4. 内部結合#
populationテーブルとactivityテーブルをカラム「地域コード」が等しいという条件で内部結合を行うSQL文を書け．
ただし，結果表示時には重複するフィールド（すなわち「地域コード」「都道府県名」）は除くこと．
%%sql
5.3.5. Q5. 直積再び#
上記Q4と同様の内容を表示するSQL文をJOINを用いずに書け．
%%sql
5.3.6. Q6. 外部結合（1/2）#
university_student_population_top10テーブルに対して「地域コード」が等しいという条件でactivityテーブルを左外部結合せよ．
%%sql
5.3.7. Q7. 外部結合（2/2）#
university_student_population_top10テーブルに対して「地域コード」が等しいという条件でactivityテーブルを右外部結合せよ．
%%sql