自己結合

売上表を出したら「前日と比べてどうだった?」と聞かれた。今日の行も昨日の行も同じテーブルの中に両方あるのに、1 行に並べて比べる書き方が思いつかない。自己結合はここで効きます。

同じテーブルに 2 つの名前を付けると、別のテーブルになる

-- 今日と前日の売上を 1 行に並べる
SELECT a.day, a.sales AS today, b.sales AS prev_day
FROM daily_sales a
LEFT JOIN daily_sales b
  ON b.day = a.day - INTERVAL '1 day'
ORDER BY a.day;

FROM daily_sales a と JOIN daily_sales b は同じテーブルですが、a と b という別名を付けた瞬間、SQL からは 2 つの別々のテーブルに見えます。あとは普通の結合と同じで、ON に「a の何と b の何が同じなら横に並べるか」を書くだけです。

別名は省略できません。FROM daily_sales JOIN daily_sales ON ... と書くと、どちらのテーブルの列を指しているのか決められずエラーになります。

一番端の行が消えたら、内部結合を疑う

-- 前日のデータが無い初日が消える
SELECT a.day, a.sales AS today, b.sales AS prev_day
FROM daily_sales a
JOIN daily_sales b ON b.day = a.day - INTERVAL '1 day';

内部結合では、相手が見つからない行が落ちます。前日が存在しない初日は、この書き方だと結果に出てきません。

同じことは上下関係をたどるときにも起きます。親を持たない一番上の行、組織図なら社長、カテゴリ表なら最上位カテゴリは、内部結合では必ず消えます。トップが出てこないレポートはほぼバグなので、階層をたどる自己結合では LEFT JOIN を選び、相手側の列が NULL のままでも行が残るようにします。

自分自身とペアを組ませない

-- 同じカテゴリに属する商品の組み合わせを作る
SELECT a.name AS product, b.name AS related
FROM products a
JOIN products b
  ON a.category = b.category
 AND a.id <> b.id
ORDER BY a.id, b.id;

条件が a.category = b.category だけだと、商品は自分自身とも一致してしまい「A と A」という無意味な行が混ざります。a.id <> b.id を足して自分を除きます。

たどれるのは 1 段だけ、という点も押さえておいてください。社員から課長、その上の部長、さらに役員と何段も一気に展開したいときは、自己結合を重ねるのではなく再帰的な WITH RECURSIVE を使うのが正攻法です。

解説

書く前に、同じテーブルのコピーを 2 枚並べたところを頭に浮かべてください。どちらを「自分」、どちらを「相手」とするかが決まれば、あとは普通の結合と変わりません。