Database u2y@5ktji6y-u

【SQLite3】自己結合とは？使い道と使い方を紹介

PCで仕事をしている人

内部・外部結合など、今までは２つのテーブルを結合して、出力結果を得る。ことを目的としていました。

SQLの仕様では、結合元・結合先のテーブルが同一であっても、結合することができます。

サーバ・DBなどのイメージズを作成している

【SQLite3】JOIN句で内部結合（inner join）する方法

2022年11月28日
サーバ・DBなどのイメージズを作成している

【SQLite3】JOIN句で外部結合（outer join）する方法

2022年11月30日

今回は、自己結合の使い道や、動作をご紹介します。

記事要約
  1. 自己結合とはなにか？について解説しています。
  2. 自己結合の使い道について、解説しています。
  3. サンプルコードと動作確認を行います。

目次

自己結合とは？なにかを解説

自己結合の定義

禄太
結合元・結合先のテーブルが同一、つまり自分自身と結合することを、自己結合と言います。

今までご紹介してきた結合は、２つのテーブルを対象としてJOINしてきました。

しかし、自己結合はその名の通り、自分自身と結合をするのです。

では、結合するとき同一テーブル（自分自身）と結合するにはどうしたらいいのでしょうか？

自己結合のイメージ

自己結合するときには、大切な条件があります。それは、、、

一つのテーブルの中に、キーとなる列が２つ存在すること。です！

例えば、以下のようなテーブルを用意します。

FLowerテーブル
  1. flower_ID：主キーとなる列。
    必ずしも連番ではない。
  2. flower_fruit：花や果物の名前が格納されている。
    10/20/30：花の名前を表すと同時に、何科の植物かを表す。
    1〜7：花、もしくはフルーツの名前
  3. family_ID：flower_fruitの各植物が、何科に属するか？を数値で表す。
    この列もキーになる。

自己結合の動作を、図で表現すると

自己結合の動作
  1. 結合元のテーブルと、結合先のテーブルを作成する

    2. 自分自身をコピーするイメージ

  2. family_IDの中から、flower_IDとキーが一致する行を検索する。
  3. 一致していたら、結合する
    （この例では、LEFT JOINを想定）
禄太
テーブル名の中が、(as A)(as B)と補記されている理由はサンプルコードの項目をご参照ください。

結合結果

自己結合の目的・使い道

上記の例で言うと、フルーツが何科の植物か？を表すテーブルを例にしてみました。

しかし、ここで思った方もいるはず。

禄太
こんなの、何に使うのだ？と。
少なくとも、私は初めて自己結合を学んだときそう思いました。

使う場面（SNSのフォロー・フォロワー機能）

例えば、SNS（Twitter・Instagramなど）を利用していると、好きになったユーザをフォローしたりすると思います。

そういった、他のユーザをフォローする（あるいは、フォローを外す）としたら、データベース上どうやって作ったらいいでしょうか？

悪い例

先に悪い例を言ってしまうと、以下のようなテーブル構成です。

  • テーブル作成するときに、friendの人数を制限してしまう。
    ※フォローできる人数が限られてしまう。
  • 将来的に人数を増やしたい。としたときに拡張性がなくなる。
  • また、もしフォローを外したときに、値がNullのカラムが発生して無駄が生じる。

自己結合を利用する例

禄太
FLowerテーブルと似たような話になってしまいますが、結合先テーブルをコピーしてJOINします。
こうすることで、悪い例のように人数に制限がなくなります（何万人フォローしたい人がいてお大丈夫です！）。

基本構文とサンプル

基本的な書き方

すっかり前置きが長くなってしまいましたが、基本的な構文は内部・外部結合と書き方は似ています。

構文の記載方法は、LEFT JOINを前提に記載しています。

select キーワード1.カラム名,キーワード2.カラム名,...
    from テーブル名称 as キーワード1
    left join テーブル名称 as キーワード2
    on キーワード1.カラム名 = キーワード2.カラム名;

事前準備（Flowerテーブル)

create table Flower(flower_ID integer primary key, flower_fruit text,family_ID integer);

insert into Flower(flower_ID, flower_fruit,family_ID) values(10,'バラ',10);
insert into Flower(flower_ID, flower_fruit,family_ID) values(20,'つつじ',20);
insert into Flower(flower_ID, flower_fruit,family_ID) values(30,'みかん',30);
insert into Flower(flower_ID, flower_fruit,family_ID) values(1,'りんご',10);
insert into Flower(flower_ID, flower_fruit,family_ID) values(2,'グレープフルーツ',30);
insert into Flower(flower_ID, flower_fruit,family_ID) values(3,'シャクナゲ',20);
insert into Flower(flower_ID, flower_fruit,family_ID) values(4,'ブルーベリー',20);
insert into Flower(flower_ID, flower_fruit,family_ID) values(5,'ソメイヨシノ',10);
insert into Flower(flower_ID, flower_fruit,family_ID) values(6,'桃',10);
insert into Flower(flower_ID, flower_fruit,family_ID) values(7,'サザンクロス',30);

サンプルコード

select A.flower_ID,A.flower_fruit,A.family_ID,B.flower_fruit
from FLower as A
left join Flower as B
on A.family_ID = B.flower_ID;
禄太
AS句を利用して、テーブルにキーワドを付与している点、ポイントです。
自己結合では、同一のテーブルに対し、別のキーワード（別名）をつける必要があります。

動作確認

禄太
Flowerテーブルの結合結果のようになっていること、ご確認いただけましたか？！

EOF

自己結合について、まとめました。

ユーザ管理（や、在庫管理とかでも使えそう）で、利用されることもあります。

もしかしたら縁がなく、特に使う必要がない！と言う方もいらっしゃるかもしれませんが、ご参照いただけたら幸いです。

禄太
本日も、一緒に学習してくださって、ありがとうございました！

では！また！

RECOMMENDこちらの記事も人気です。