3．SQLによる結合

概要

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

外部キー

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

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）図は，リレーショナルデータベースのテーブル名，各テーブルの属性名，各属性のデータ型，主キーなどを図示したものである。この授業で使用するデータベースの構造を以下に示す。

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

テーブル間の関連: employees ←1:N→ dept_emp ←N:1→ departments，employees ←1:N→ dept_manager ←N:1→ departments，employees ←1:N→ titles，employees ←1:N→ salaries

出典: MySQL Employees Sample Database
https://dev.mysql.com/doc/employee/en/sakila-structure.html より引用

演習準備

この演習では Python を使用する。Python がインストールされていない場合は，下記の「Python 3.12 のインストール（Windows 上）」を展開し，手順に従いインストールすること。

winget install -e --id Python.Python.3.12 --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 AssociateFiles=1 InstallLauncherAllUsers=1"

python --version

演習

ステップ1：データベースの準備

以下の手順で，Employees Sample データベース（SQLite版）を準備する。

このデータベースは，MySQL Employees Sample Database を SQLite 形式に変換したものであり，GitHub 上の bytebase/employee-sample-database リポジトリで公開されている。

コマンドプロンプトで以下のコマンドを実行し，データベースファイルをカレントディレクトリにダウンロードする。python -c は，引用符内の Python コードをコマンドライン上で直接実行するオプションである。urllib.request.urlretrieve は，第1引数に指定した URL からファイルを取得し，第2引数に指定したファイル名で保存する関数である。

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

ステップ2：SQL演習

Pythonの対話型シェルを使って，SQLによるデータ操作を実行する。

① コマンドプロンプトまたはターミナルを開き，python と入力してPythonの対話型シェルを起動する。

python

② 次のPythonプログラムを実行し，SQLite3データベースに接続する。この演習で使用するデータベースファイルは，ステップ1でカレントディレクトリにダウンロードした employee.db である。

import sqlite3
c = sqlite3.connect('employee.db')

③ データベースに含まれるテーブルの一覧を確認する。sqlite_master は SQLite が内部的に保持するシステムテーブルであり，データベース内のテーブルやインデックスなどの定義情報が格納されている。type='table' の条件を指定することで，テーブル名のみを取得できる。

result = c.execute("select name from sqlite_master where type='table'").fetchall()
print(result)

④ 各テーブルの列情報を確認する。PRAGMA table_info(テーブル名) は，指定したテーブルの列名，データ型，主キーの有無などの構造情報を返す SQLite 固有のコマンドである。

for table_name in ['department', 'dept_manager', 'dept_emp', 'employee', 'title', 'salary']:
    print(table_name)
    result = c.execute(f"PRAGMA table_info({table_name})").fetchall()
    print(result)
    print()

⑤ 次のPythonプログラムを実行し，テーブルの全データを読み出す。データの読み出しには，SQL文の実行と結果の取得を行うオブジェクトであるカーソルを使用する。

result = c.execute("select * from department").fetchall()
print(result)

続けて，以下も実行する。

result = c.execute("select * from dept_manager").fetchall()
print(result)

実行結果から，例えば部門番号 d001 のマネージャが交替した時期などが確認できる。

⑥ 次のPythonプログラムを実行し，条件に合致する行を読み出す。salary テーブルの amount 列は給与額を表す。

result = c.execute("select * from salary where amount > 155000").fetchall()
print(result)

⑦ 結合問い合わせを実行する。amount > 155000 を満たす従業員の氏名などを取得する。ここでは，salary テーブルと employee テーブルを従業員番号（emp_no）で結合する。SQL文を複数行で記述するため，Pythonの三重引用符 """ を使用する。

result = c.execute("""select *
    from salary, employee
    where salary.emp_no = employee.emp_no
    and amount > 155000""").fetchall()
print(result)

続けて，高給与の従業員の職位情報を得るために，salary テーブルと title テーブルを従業員番号（emp_no）で結合する以下の問い合わせも実行する。

result = c.execute("""select *
    from salary, title
    where salary.emp_no = title.emp_no
    and amount > 155000""").fetchall()
print(result)

⑧ 接続を閉じる

c.close()