実行できるSQLiteドキュメント: Self Join

Q: SQLiteのself joinとは何ですか？

self join（自己結合）は、同じテーブル同士をJOINする書き方のことです。同一のテーブルに2つの異なるエイリアスを付けることで、SQLiteはそれぞれを別の行ソースとして扱えるようになります。あとは、ある行と別の行を結びつけるカラム（典型的には社員と上司のような親子関係）でマッチさせます。

Q: なぜself joinではエイリアスが必須なのですか？

エイリアスを付けないと、同じカラム名を書いたときにSQLiteがどちらのテーブルを指しているのか判断できません。例えば社員側にe、上司側にmというエイリアスを付けておけば、e.manager_id = m.idのように曖昧さなく書けます。これは省略可能なオプションではなく、付けないとそもそもクエリがパースできません。

Q: self joinとサブクエリはどう使い分ければよいですか？

結果に両方の行のカラムを並べたいとき（例：社員の名前と上司の名前を同じ行に出したい）はself joinが向いています。一方、単に絞り込みや単一値の参照だけならサブクエリで十分です。なお、組織図のような深い階層を辿りたい場合はどちらも力不足なので、再帰CTE（WITH RECURSIVE）の出番になります。

self join はただのエイリアス付き JOIN

sqlite の self join に特別な仕掛けはありません。両側が同じテーブルになっただけの普通の JOIN です。ポイントは、SQLite が 2 つのコピーを区別できるよう、それぞれにエイリアスを付けてやることだけ。

self join の出番は、テーブルの中の行が「同じテーブル内の別の行」を参照しているときです。定番は employees テーブルで、各行に上司を指す manager_id が入っているケースですね:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Adaには上司がいません。BorisとCleoはAdaの部下、DiegoとEsmeはBorisの部下です。この関係はすべて1つのテーブルの中で完結しています。まさに self join（自己結合）が真価を発揮する場面ですね。

self join の基本形

各社員と上司の名前をペアで取り出すには、employees テーブルを自分自身と結合します。同じテーブルを2回登場させ、片方を「社員」役、もう片方を「上司」役として扱うのがポイントです。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

ストレージを共有しているだけの 2 つのテーブルとして読むのがコツです。e は従業員側の行、m は上司側の行を表します。結合条件の e.manager_id = m.id がこの 2 つを紐づけていて、各従業員に対して、その manager_id に一致する id を持つ行を m 側から探してくる、という動きになります。

ここで Ada が結果に出てこなかったことに注目してください。彼女の manager_id は NULL なので、一致しない行を切り捨てる INNER JOIN の挙動によって落とされています。

マッチしない行も残したいときは LEFT JOIN

上司がいない人も含めて全員を結果に残したい場合は、LEFT JOIN に切り替えます。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

今度は Ada が manager 列に NULL を持った状態で出てきます。仕組み自体は同じ自己結合（self join）ですが、結合の種類が変わったことで LEFT JOIN 本来の動き ―― 左側の行は全部残して、対応がないところは空欄で埋める ―― がそのまま効いています。

人物一覧を表示したいときは、たいていこちらの形が欲しくなります。「上司なし」という情報自体に意味があるので、行ごと消してしまうのはもったいないんですよね。

エイリアスは省略できません

試しにエイリアスなしで self join を書いてみると、SQLite はもう何を指しているのか判断できません：

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- エラー: 列名があいまいです: name

各カラムがテーブルの両方のコピーから1回ずつ、合計2回登場するため、SQLite はどちらを指しているのか判断できません。これを解決するのがエイリアスで、それぞれのコピーに別々の名前を付けてあげます。エイリアスは、テーブル名そのままではなく、その行が担う役割が伝わる名前を選ぶのがコツです。

社員と上司なら e と m
階層構造なら parent と child
任意のペアを比較するなら a と b

self join が読みやすいコードになるかどうかは、ほぼエイリアス次第と言っていいくらいです。

同じテーブル内でペアを探す

self join は階層データ専用というわけではありません。同じテーブル内の行同士を比較したい場面なら、どんなときでもこのパターンが活きてきます。たとえば次のような商品リストから、価格が同じ商品の組み合わせをすべて抽出したいケースを見てみましょう。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('マグカップ',   9.99),
    ('ノート',      9.99),
    ('ペン',        2.50),
    ('鉛筆',        2.50),
    ('ランプ',     29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

注目すべきポイントは2つあります。まず、a.price = b.price が実際のマッチング条件です。次に、a.id < b.id は、同じペアが2回返ってくる((マグカップ, ノート) と (ノート, マグカップ) の両方)のを防ぎ、さらに同じ行同士のペアリングも除外しています。この < のテクニックはペアを列挙する場面で繰り返し登場するので、覚えておくと便利です。

2階層上をたどる

self joinは階層を1段階だけ遡るのに使えます。では、各社員の「上司の上司」を取得したい場合はどうするか? テーブルを3回結合すればOKです。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

新しいエイリアスを追加するたびに、ツリーを1階層さかのぼれるイメージです。2〜3階層くらいなら問題なく動きますが、それ以上になると一気に破綻します。クエリを書く時点で階層の深さを知っておく必要があり、しかも階層ごとに JOIN を足さなければなりません。この限界を突破するために生まれたのが、再帰 CTE です。

self join を使わない方がいい場面

self join が真価を発揮するのは、関係の両側にある列を結果に含めたいときです。ただ単に絞り込みたいだけ——たとえば「Ada が上司である社員を全員出したい」——という場合は、サブクエリの方が読みやすくなることが多いです。

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

エイリアスでこねくり回す必要もなく、意図もハッキリします。判断の目安はこうです。両方の行のデータを出力に含めたいなら self join、比較用の値が欲しいだけならサブクエリ。

ただし、深さが決まらない階層構造(組織図、ファイルツリー、スレッド型コメントなど)になると、どちらのパターンもスケールしません。そこは再帰CTEの出番です。

次は: サブクエリ

self join とサブクエリは解決できる問題が重なる部分があり、どちらを選ぶべきか分かっていると、後で SQL を眺めて頭を抱えずに済みます。次のページではサブクエリを深掘りします — スカラー、相関、IN 形式まで、それぞれが活きる場面を見ていきましょう。

よくある質問

SQLiteのself joinとは何ですか？

self join（自己結合）は、同じテーブル同士をJOINする書き方のことです。同一のテーブルに2つの異なるエイリアスを付けることで、SQLiteはそれぞれを別の行ソースとして扱えるようになります。あとは、ある行と別の行を結びつけるカラム（典型的には社員と上司のような親子関係）でマッチさせます。

なぜself joinではエイリアスが必須なのですか？

エイリアスを付けないと、同じカラム名を書いたときにSQLiteがどちらのテーブルを指しているのか判断できません。例えば社員側にe、上司側にmというエイリアスを付けておけば、e.manager_id = m.idのように曖昧さなく書けます。これは省略可能なオプションではなく、付けないとそもそもクエリがパースできません。

self joinとサブクエリはどう使い分ければよいですか？

結果に両方の行のカラムを並べたいとき（例：社員の名前と上司の名前を同じ行に出したい）はself joinが向いています。一方、単に絞り込みや単一値の参照だけならサブクエリで十分です。なお、組織図のような深い階層を辿りたい場合はどちらも力不足なので、再帰CTE（WITH RECURSIVE）の出番になります。