3．SQLによる結合

概要

リレーショナルデータベースは複数のテーブルから構成される．関連するデータが複数のテーブルに分散して格納されているため，複数のテーブルにまたがる情報を得るにはテーブルの結合が必要となる．本教材では，複数のテーブルを結合して1つのテーブルを生成する操作について説明し，SQLite3データベースとPythonを用いた結合操作の演習を行う．

外部キー

テーブル R(…, A_i, …) と S(…, B_j, …) について，属性 B_j がテーブル S の主キーであるとする．次の条件が成り立つとき，属性 A_i をテーブル R の外部キー（foreign key）という．

R の任意の行における A_i の値は，S の B_j のいずれかの値と等しいか，または空値（NULL）である．

結合演算では，外部キーとそれが参照する主キーを結合条件として使用する．

結合演算

R(A₁, A₂, …, A_n) と S(B₁, B₂, …, B_m) をテーブルとする．R の属性 A_i と S の属性 B_j に対する結合演算「R[A_i θ B_j]S」は次のように定義される（θ は比較演算子）．

結合演算「R[A_i θ B_j]S」は，SQLで次のように記述する．

SELECT * FROM R, S WHERE R.Ai θ S.Bj

結合問い合わせ

結合問い合わせとは，SQL問い合わせの WHERE <式> に結合演算を含む問い合わせである．

SQLite3のデータベースファイル

本教材では，リレーショナルデータベース管理システム SQLite3 を使用する．SQLite3 では，リレーショナルデータベースが1つのファイルに格納される．そのため，データベースファイルのコピーや配布が容易である．

UML図

UML（Unified Modeling Language）図は，テーブル名，各テーブルの属性名，各属性のデータ型，主キーなどを図示したものである．本教材で使用するデータベース（SQLite版）の構造を以下に示す．

凡例 — PK: 主キー，FK: 外部キー，UNIQUE: 一意制約

テーブル間の関連: employee ←1:N→ dept_emp ←N:1→ department，employee ←1:N→ dept_manager ←N:1→ department，employee ←1:N→ title，employee ←1:N→ salary

出典: MySQL Employees Sample Database
https://dev.mysql.com/doc/employee/en/sakila-structure.html より引用，SQLite版（bytebase/employee-sample-database）に合わせて表記．

演習準備

本演習では Python とテキストエディタを使用する．Python のコードはテキストエディタで作成し，ファイルとして保存してから実行する．Python の起動方法やプログラムの作成・実行方法が分からない場合は，先に「Windows環境でのPython：基本とプログラムの作成・実行」を参照すること．

掲載されているPythonコードは，Windows 上の Python および Google Colab のいずれの環境でも動作する．Windows で実行する場合は，下記の「Python 3.12 のインストール（Windows 上）」を展開し，手順に従いインストールすること．Google Colab で実行する場合は，sqlite3，urllib.request はあらかじめ利用可能であり，追加インストールは不要である．

winget install --id Python.Python.3.12 -e --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 Include_test=0 Include_pip=1 Include_launcher=1 InstallLauncherAllUsers=1 TargetDir=\"C:\Program Files\Python312\""
powershell -Command "$p='C:\Program Files\Python312'; $s=\"$p\Scripts\"; $m=[Environment]::GetEnvironmentVariable('Path','Machine'); if($m -notlike \"*$s*\") { [Environment]::SetEnvironmentVariable('Path', \"$p;$s;$m\", 'Machine') }"

python --version

使用するデータベースについて

Employees Sample データベース（SQLite版）

このデータベースは，MySQL Employees Sample Database を SQLite 形式に変換したものであり，GitHub 上の bytebase/employee-sample-database リポジトリで公開されている．出典と著作権表示は次のとおりである．

データの出典: MySQL Employees Sample Database（原著者: Fusheng Wang, Carlo Zaniolo．リレーショナルスキーマ: Giuseppe Maxia．データ変換: Patrick Crews）

This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.

SQLite adaptation: bytebase/employee-sample-database (MIT License)

演習

演習1．Employees Sample データベース（SQLite版）のダウンロード

手順

1. 作業ディレクトリ（例：C:\work）を作成し，コマンドプロンプトで以下のように移動する．Google Colab を使用する場合はこの手順は不要である．

   cd C:\work

2. 以下のプログラムをファイル step1.py として作業ディレクトリに保存する（Google Colab を使用する場合はセルにコピーする）．

   import urllib.request
   
   url = "https://raw.githubusercontent.com/bytebase/employee-sample-database/main/sqlite/dataset_small/employee.db"
   urllib.request.urlretrieve(url, "employee.db")

3. コマンドプロンプトで以下を実行し，データベースファイル employee.db を作業ディレクトリにダウンロードする．

   python step1.py

4. 作業ディレクトリに employee.db が作成されていることを確認する．

ヒント

• urllib.request.urlretrieve(URL, ファイル名) は，URL から取得した内容を，指定したファイル名で作業ディレクトリに保存する．

考察ポイント

• ダウンロードしたファイルのサイズを確認し，1つのファイルにデータベース全体が格納されていることを確かめる．

演習2．データベースの内容確認

手順

1. 以下のプログラムをファイル step2.py として作業ディレクトリ（employee.db と同じディレクトリ）に保存する．

   import sqlite3
   
   conn = sqlite3.connect('employee.db')
   
   result = conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
   for row in result:
       print(row)
   print()
   
   for table_name in ['department', 'dept_manager', 'dept_emp', 'employee', 'title', 'salary']:
       print(table_name)
       result = conn.execute(f"PRAGMA table_info({table_name})").fetchall()
       for row in result:
           print(row)
       print()
   
   conn.close()

2. コマンドプロンプトで以下を実行する．

   python step2.py

3. テーブル名の一覧と，各テーブルの列名・データ型・主キー情報が表示されることを確認する．

ヒント

• sqlite_master は SQLite が内部に保持するシステムテーブルで，テーブルやインデックスの定義情報が格納されている．条件 type='table' でテーブル名のみを取得できる．
• PRAGMA table_info(テーブル名) は，指定したテーブルの列名・データ型・主キーの有無などを返す SQLite 固有のコマンドである．

考察ポイント

• 表示されたテーブル名と，本教材の UML 図に示されたテーブル名が一致するかを確認する．
• 各テーブルの主キーがどの列に設定されているかを確認し，UML 図と対応づける．

演習3．単一テーブルへの問い合わせ

手順

1. 以下のプログラムをファイル step3.py として保存する．

   import sqlite3
   
   conn = sqlite3.connect('employee.db')
   
   print('--- department ---')
   result = conn.execute("SELECT * FROM department").fetchall()
   for row in result:
       print(row)
   
   print('--- dept_manager ---')
   result = conn.execute("SELECT * FROM dept_manager").fetchall()
   for row in result:
       print(row)
   
   print('--- salary (amount > 120000) ---')
   result = conn.execute("SELECT * FROM salary WHERE amount > 120000").fetchall()
   for row in result:
       print(row)
   
   conn.close()

2. コマンドプロンプトで以下を実行する．

   python step3.py

ヒント

• execute(SQL文).fetchall() は，問い合わせ結果の全行をリストで返す．
• salary テーブルの amount 列は給与額を表す．

考察ポイント

• dept_manager の結果から，同じ dept_no に対して複数の行（複数の emp_no，異なる from_date・to_date）が存在する部門を見つけ，マネージャの交替時期を読み取る．
• amount > 120000 の結果から，対象となる行の数と emp_no の重複の有無を確認する（同一従業員に複数の期間の給与レコードが存在しうるか）．

演習4．結合問い合わせ

手順

1. 以下のプログラムをファイル step4.py として保存する．

   import sqlite3
   
   conn = sqlite3.connect('employee.db')
   
   print('--- salary JOIN employee (amount > 120000) ---')
   result = conn.execute("""SELECT *
       FROM salary, employee
       WHERE salary.emp_no = employee.emp_no
       AND amount > 120000""").fetchall()
   for row in result:
       print(row)
   
   print('--- salary JOIN title (amount > 120000) ---')
   result = conn.execute("""SELECT *
       FROM salary, title
       WHERE salary.emp_no = title.emp_no
       AND amount > 120000""").fetchall()
   for row in result:
       print(row)
   
   conn.close()

2. コマンドプロンプトで以下を実行する．

   python step4.py

ヒント

• SQL 文を複数行で書くため，トリプルクォート（""" … """）を使用している．
• (1) は，amount > 120000 を満たす給与レコードの従業員氏名（first_name, last_name）を取得することを目的とする．
• (2) は，同じ条件の従業員の職位（title）を取得することを目的とする．
• 結合条件 salary.emp_no = employee.emp_no は，外部キー salary.emp_no と主キー employee.emp_no を対応づけている．

考察ポイント

• (1) の結果から，amount > 120000 を満たす従業員の氏名を読み取る．同一従業員が複数行に現れる場合，その理由を salary テーブルの主キー（emp_no, from_date）から考察する．
• (2) の結果から，高給与者の職位の分布を読み取る．同一従業員が複数の title を持つ場合があることを確認する．
• 結合条件を省略した場合に得られる行数（直積集合の要素数）を予想し，結合条件の役割を整理する．