Self-JOIN: それ自体とのテーブル

場合によっては、あなたが通過したい関係が 2 つの間の関係ではないことがあります** 異なるテーブルですが、**同じテーブルの行**の間です。典型的な例: 「カテゴリ→サブカテゴリ」階層。親と子は両方とも次の行になります。 同じ categories テーブルが parent_id によってリンクされています。

1 つの行 (子) と「その」他の行 (親) を一緒に読み取るには、 自己結合: 同じテーブルが FROM に 2 回表示されます。 さまざまな別名。

構文

SELECT colonne
FROM   tabella AS alias_a
JOIN   tabella AS alias_b ON alias_a.foreign_key = alias_b.id;

エイリアスは 必須です: AS a / AS b を省略すると、PostgreSQL にはエイリアスがありません。 name または id と書くときに、2 つのコピーのどちらを意味するのかを知る方法です。

-- Ogni sotto-categoria con il nome della categoria padre:
SELECT child.name  AS subcategory,
       parent.name AS category
FROM   categories AS child
JOIN   categories AS parent ON child.parent_id = parent.id;

平易な英語で読む: 「child という名前の categories の行を取得し、 それぞれについて、parent という名前の categories の行を見つけます。その id は 子のparent_id「.

結果にはルート カテゴリ (parent_id = NULL を持つカテゴリ) が除外されます。 INNER JOIN は一致しない行をスキップするためです。含めたい場合は、 LEFT JOIN を使用してください。

LEFT と自己結合して「ルーツも維持」

-- Tutte le categorie, con nome del padre o NULL se sono radice:
SELECT child.name AS category,
       parent.name AS parent
FROM   categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id;

これで、parent = NULL を含む 9 行 (データセット内のすべてのカテゴリ) が取得されました。 3つの根のために。

自己結合による集約

GROUP BY と非常によく組み合わせて、「それぞれの子が何人いるか」を尋ねます。 親が持っている":

SELECT p.name AS parent_category,
       COUNT(c.id) AS children
FROM   categories AS p
LEFT JOIN categories AS c ON c.parent_id = p.id
WHERE  p.parent_id IS NULL          -- solo le radici
GROUP BY p.name
ORDER BY p.name;

2 つの重要な詳細に注意してください。

1. WHERE p.parent_id IS NULL は JOIN の前** をフィルターします。 「左側のテーブル」としてルートします。
2. COUNT(c.id) (COUNT(*) ではありません) は、子のないルートに対して 0 を返します。 一方、LEFT JOIN 行には次があるため、COUNT(*) は 1 を返します。 NULL。

自分で試してみてください

復習演習