「データベース」テーブルの自己結合

テーブルの自己結合についてメモを残しておく。 完全に自分用です。

テーブル定義とデータ登録のクエリ。

// テーブル定義
create table test42 (id integer primary key,
name varchar(50),
birthday varchar(50),
leader_id integer);

//データ登録
insert into test42 values (1, 'aaa', '2021-01-01', 2);
insert into test42 values (2, 'bbb', '2021-02-01', 2);
insert into test42 values (3, 'ccc', '2021-03-01', 2);
insert into test42 values (4, 'ddd', '2021-04-01', 4);
insert into test42 values (5, 'eee', '2021-05-01', 4);

SELECT の結果。

# select * from test42;
 id | name |  birthday  | leader_id
----+------+------------+-----------
  1 | aaa  | 2021-01-01 |         2
  2 | bbb  | 2021-02-01 |         2
  3 | ccc  | 2021-03-01 |         2
  4 | ddd  | 2021-04-01 |         4
  5 | eee  | 2021-05-01 |         4
(5 rows)

id と leader_id で自己結合する。 id と leader_id で「x.leader_id = y.id;」と結合しているので当たり前ですが、 yが以下のように取れてくるイメージが自分の中で湧きづらい。

# select * from test42 x, test42 y where x.leader_id = y.id;
 id | name |  birthday  | leader_id | id | name |  birthday  | leader_id
----+------+------------+-----------+----+------+------------+-----------
  1 | aaa  | 2021-01-01 |         2 |  2 | bbb  | 2021-02-01 |         2
  2 | bbb  | 2021-02-01 |         2 |  2 | bbb  | 2021-02-01 |         2
  3 | ccc  | 2021-03-01 |         2 |  2 | bbb  | 2021-02-01 |         2
  4 | ddd  | 2021-04-01 |         4 |  4 | ddd  | 2021-04-01 |         4
  5 | eee  | 2021-05-01 |         4 |  4 | ddd  | 2021-04-01 |         4

上記のイメージさえあれば、例えば、leader_id よりも年上の人を抽出するように 「x.birthday < y.birthday」をつけると以下の結果になる。

# select * from test42 x, test42 y where x.leader_id = y.id and x.birthday < y.birthday;
 id | name |  birthday  | leader_id | id | name |  birthday  | leader_id
----+------+------------+-----------+----+------+------------+-----------
  1 | aaa  | 2021-01-01 |         2 |  2 | bbb  | 2021-02-01 |         2

ここからは余談ですが、最近データベース知らないことが色々と足かせになってるかもしれないと思うことがあります。 やっぱりシステムって結局は何らかのデータ扱いたいためにあることがほとんどで、アプリケーションはそのデータを扱う一つの手段だと思うので、アプリな人もデータベース知っておかないとなぁと思う今日この頃です。

以上になります。