SQL Self JOIN -- テーブルを自身に結合する

Self JOINとは？

自己結合は、テーブルが自分自身に結合される通常の結合です。同じテーブルの2つの「コピー」を区別するためにテーブルエイリアスを使用します。自己結合は別の結合タイプではなく、INNER、LEFT、その他の結合キーワードを使用できます。

構文 -- 従業員-マネージャーの例

SELECT
  emp.name AS employee,
  mgr.name AS manager
FROM employees AS emp
LEFT JOIN employees AS mgr
  ON emp.manager_id = mgr.id;

ここでemployeesは2回出現します：empとして（従業員を取得）とmgrとして（マネージャーを検索）。LEFT JOINにより、マネージャーがいない従業員（CEOなど）もマネージャーカラムにNULLで表示されます。

重複の検出

自己結合は重複データの検出に優れています：

SELECT a.id, b.id, a.email
FROM users AS a
INNER JOIN users AS b
  ON a.email = b.email
  AND a.id < b.id;

a.id < b.id条件により、各ペアが2回表示されることを防ぎ、行が自分自身とマッチすることを防ぎます。

階層データ

自己結合は組織図、カテゴリツリー、スレッドコメントを実現します。深い階層（2-3レベル以上）の場合は、複数の自己結合をネストする代わりに再帰CTE（WITH RECURSIVE）の使用を検討してください。任意の深さを処理できます。

パフォーマンスの考慮事項

自己結合は同じテーブルへの読み取り負荷を2倍にします。結合カラムにインデックスがあることを確認してください。非常に大きなテーブルの場合、階層を別のマッピングテーブルに実体化するか、PostgreSQLのltree拡張などのデータベース固有の機能を使用することを検討してください。

使用すべきでない場合

階層をトラバースするために3つ以上の自己結合を書いている場合、再帰CTEまたはグラフデータベースの方が適切であるという強いシグナルです。