[sqlite3] 自己結合 (Self Join) の活用

[SQL] 自己結合 (Self Join) の活用

今回はSQLの「自己結合 (Self Join)」について解説する。 自己結合とは、その名の通り「自分自身と結合する」操作のことである。

物理的には1つのテーブルだが、別名（エイリアス）をつけることで、論理的に「2つの異なるテーブル」として扱い、それらを結合することができる。 階層構造を持つデータや、同じテーブル内の行同士を比較したい場合に非常に役立つ。

準備

本サイトではpythonのライブラリであるsqlite3とipython-sqlを利用して、Jupyter Notebook上でSQLを実行・解説する。 また、データフレームの表示には Polars を利用する。

github

• githubのjupyter notebook形式のファイルはこちら

google colaboratory

• google colaboratory で実行する場合はこちら

筆者の環境

筆者の環境である。

!sw_vers

ProductName:		macOS
ProductVersion:		15.5
BuildVersion:		24F74

!python -V

Python 3.12.12

必要なライブラリを読み込む。

import sqlite3
import polars as pl

%load_ext sql
%config SqlMagic.feedback = True

%config SqlMagic.style = '_DEPRECATED_DEFAULT'

データの準備

サンプルとして、社員とその上司の関係を表す Employees テーブルを作成する。 manager_id カラムには、その社員の上司の id が入る。

import os

if os.path.exists("data.db"):
    os.remove("data.db")

os.system("touch data.db")
os.system("chmod 664 data.db")

# データベース接続
%sql sqlite:///data.db

'Connected: @data.db'

%%sql
CREATE TABLE Employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER
);

INSERT INTO Employees (id, name, manager_id) VALUES
    (1, '佐藤社長', NULL),
    (2, '鈴木部長', 1),
    (3, '高橋課長', 2),
    (4, '田中係長', 2),
    (5, '伊藤社員', 3),
    (6, '渡辺社員', 3);

 * sqlite:///data.db
Done.
6 rows affected.





[]

%%sql
SELECT * FROM Employees;

 * sqlite:///data.db
Done.

例: 社員名と上司名を表示する

Employees テーブルには上司の id しか入っていないため、上司の name を知るには、再度 Employees テーブルを参照する必要がある。

ここで自己結合を利用する。 Employees テーブルを「部下テーブル (E)」と「上司テーブル (M)」の2つに見立てて結合する。

query = """
SELECT
    E.name as Employee_Name,
    M.name as Manager_Name
FROM
    Employees as E
LEFT JOIN
    Employees as M
ON
    E.manager_id = M.id;
"""

with sqlite3.connect("data.db") as conn:
    df = pl.read_database(query, connection=conn)
    display(df)

shape: (6, 2)

解説

• FROM Employees as E: 1つ目の Employees テーブルを E（Employee）とする。
• LEFT JOIN Employees as M: 2つ目の Employees テーブルを M（Manager）として結合する。
• ON E.manager_id = M.id: 部下の manager_id と 上司の id を紐付ける。

これにより、同じテーブル内の情報を紐付けて、意味のある形（社員名と上司名のペア）で抽出できた。 佐藤社長は上司がいない（NULL）ため、LEFT JOIN の結果、Manager_Name も NULL になっている。

まとめ

今回は自己結合（Self Join）について紹介した。

• エイリアス を使うことで、1つのテーブルを複数の役割（部下と上司など）で扱える。
• 階層構造（組織図、カテゴリツリーなど）を扱う際に必須のテクニックである。

一見難しそうに見えるが、「コピーしたテーブル同士を結合している」とイメージすると理解しやすい。