自己結合はいつ使うか

自テーブル各レコードの特性を

単純な射影や選択では比較できない場合に使う

ここでは CASE, 相関サブクエリ, 内部/外部結合などに言及しないとする

(Web 記事や書籍を眺めてみたのだが、なかなか扱いがよくわからん構文

勉強しきれてないが、これ使うのであれば

OLAP 関数使ったりテーブル設計見直したほうが、良いのでは)

例

テーブル論理要件

• 会社員テーブル(employees) が以下の属性を持っている
  • 会社員ID(employeeid)
  • 上司ID(bossid)
  • 会社員名
• 会社員テーブルは以下の特性を持つ
  • その会社員が誰を上司とするかを判断する為に
    • 上司ID が 会社員ID を参照
    • ただし、上司を持たない会社員は bossid が 0 である
  • bossid = 0 のデータは内部で事前に用意していおく

テーブル物理要件

CREATE TABLE employees (
  employeeid INTEGER
    NOT NULL
  bossid INTEGER
    NOT NULL
    DEFAULT 0, /* 0 means "I don't have boss" */
  name VARCHAR(64)
    NOT NULL,
  PRIMARY KEY (employeeid),
  FOREIGN KEY bossid REFERENCES(employees),
  CONSTRAINT employees_identifier_validation
    CHECK((employeeid >= 0) AND (bossid >= 0))
);

SELECT
  *
FROM
  employees;

employeeid | bossid |        name         
------------+--------+---------------------
         0 |      0 | you have not a boss
         1 |      0 | Sakano
         2 |      1 | Yoneda
         3 |      1 | Umino
         4 |      3 | Saejima
         5 |      4 | Kobayashi

要望

各社員の名前, その社員の上司名

を表示する SQL を書いて欲しい

内部でダミーの社員ってのを用意しているらしいけど

そのデータは表示しないように

実行 SQL

SELECT
  EMP_MAIN.name AS "When employee is...",
  EMP_SUB.name  AS "Then Boss is..."
FROM
  employees AS EMP_MAIN, employees AS EMP_SUB
WHERE
  EMP_MAIN.employeeid <> 0
  AND
  EMP_MAIN.bossid = EMP_SUB.employeeid;

When employee is... |   Then Boss is...   
---------------------+---------------------
Sakano              | you have not a boss
Yoneda              | Sakano
Umino               | Sakano
Saejima             | Umino
Kobayashi           | Saejima

蛇足

SELECT
  EMP_MAIN.bossid,
  EMP_MAIN.name AS "When employee is...",
  EMP_SUB.employeeid,
  EMP_SUB.name  AS "Then Boss is..."
FROM
  employees AS EMP_MAIN, employees AS EMP_SUB

EMP_MAIN.bossid | When employee is... | EMP_SUB.employeeid |   Then Boss is...   
--------+---------------------+------------+---------------------
     0 | you have not a boss |          0 | you have not a boss
     0 | you have not a boss |          1 | Sakano
     0 | you have not a boss |          2 | Yoneda
     0 | you have not a boss |          3 | Umino
     0 | you have not a boss |          4 | Saejima
     0 | you have not a boss |          5 | Kobayashi
     0 | Sakano              |          0 | you have not a boss
     0 | Sakano              |          1 | Sakano
     0 | Sakano              |          2 | Yoneda
     0 | Sakano              |          3 | Umino
     0 | Sakano              |          4 | Saejima
     0 | Sakano              |          5 | Kobayashi
     1 | Yoneda              |          0 | you have not a boss
     1 | Yoneda              |          1 | Sakano
     1 | Yoneda              |          2 | Yoneda
     1 | Yoneda              |          3 | Umino
     1 | Yoneda              |          4 | Saejima
     1 | Yoneda              |          5 | Kobayashi
     1 | Umino               |          0 | you have not a boss
     1 | Umino               |          1 | Sakano
     1 | Umino               |          2 | Yoneda
     1 | Umino               |          3 | Umino
     1 | Umino               |          4 | Saejima
     1 | Umino               |          5 | Kobayashi
     3 | Saejima             |          0 | you have not a boss
     3 | Saejima             |          1 | Sakano
     3 | Saejima             |          2 | Yoneda
     3 | Saejima             |          3 | Umino
     3 | Saejima             |          4 | Saejima
     3 | Saejima             |          5 | Kobayashi
     4 | Kobayashi           |          0 | you have not a boss
     4 | Kobayashi           |          1 | Sakano
     4 | Kobayashi           |          2 | Yoneda
     4 | Kobayashi           |          3 | Umino
     4 | Kobayashi           |          4 | Saejima
     4 | Kobayashi           |          5 | Kobayashi
(36 rows)

WHERE
  EMP_MAIN.employeeid <> 0
  AND
  EMP_MAIN.bossid = EMP_SUB.employeeid;

When employee is... |   Then Boss is...   
---------------------+---------------------
Sakano              | you have not a boss
Yoneda              | Sakano
Umino               | Sakano
Saejima             | Umino
Kobayashi           | Saejima