自己結合
売上表を出したら「前日と比べてどうだった?」と聞かれた。今日の行も昨日の行も同じテーブルの中に両方あるのに、1 行に並べて比べる書き方が思いつかない。自己結合はここで効きます。
同じテーブルに 2 つの名前を付けると、別のテーブルになる
SQL クエリ
-- 今日と前日の売上を 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 ... と書くと、どちらのテーブルの列を指しているのか決められずエラーになります。
一番端の行が消えたら、内部結合を疑う
SQL クエリ
-- 前日のデータが無い初日が消える
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 のままでも行が残るようにします。
自分自身とペアを組ませない
SQL クエリ
-- 同じカテゴリに属する商品の組み合わせを作る
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 枚並べたところを頭に浮かべてください。どちらを「自分」、どちらを「相手」とするかが決まれば、あとは普通の結合と変わりません。
テーブル構造
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(30),
manager_id INT
);
INSERT INTO employees VALUES
(1, '社長', NULL),
(2, '部長A', 1),
(3, '部長B', 1),
(4, '課長X', 2),
(5, '課長Y', 2),
(6, '一般a', 4),
(7, '一般b', 5);期待される出力
| employee_id | employee_name | manager_name |
|---|---|---|
| 1 | 社長 | NULL |
| 2 | 部長A | 社長 |
| 3 | 部長B | 社長 |
| 4 | 課長X | 部長A |
| 5 | 課長Y | 部長A |
| 6 | 一般a | 課長X |
| 7 | 一般b | 課長Y |