Self-JOIN: meja dengan dirinya sendiri

Terkadang hubungan yang ingin Anda lewati bukan di antara keduanya tabel yang berbeda, tetapi di antara baris tabel yang sama. Contoh tipikal: a hierarki "kategori → subkategori", dengan induk dan anak berada dalam baris tabel categories yang sama, dihubungkan oleh parent_id.

Untuk membaca bersama satu baris (anak) dan baris lainnya (induk) "nya", Anda menggunakan a bergabung sendiri: tabel yang sama muncul dua kali di FROM, dengan dua alias yang berbeda.

Sintaksnya

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

Alias wajib: jika kita menghilangkan AS a / AS b, PostgreSQL tidak memiliki cara untuk mengetahui salinan mana yang kita maksud ketika kita menulis name atau id.

-- 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;

Baca dalam bahasa Inggris sederhana: "ambil baris categories bernama child, dan untuk masing-masingnya carilah baris categories bernama induk yang id-nya adalah parent_id anak".

Hasilnya tidak termasuk kategori akar (kategori dengan parent_id = NULL) karena INNER JOIN melewatkan baris yang tidak cocok. Jika Anda ingin menyertakan mereka, gunakan LEFT JOIN.

Gabung sendiri dengan LEFT untuk "menjaga akarnya juga"

-- 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;

Sekarang Anda mendapatkan 9 baris (setiap kategori dalam kumpulan data), dengan parent = NULL untuk 3 akar.

Bergabung dengan bergabung sendiri

Ini sangat cocok dipadukan dengan GROUP BY untuk menanyakan "berapa banyak anak yang melakukan masing-masing anak orang tua punya":

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;

Perhatikan dua detail penting:

1. WHERE p.parent_id IS NULL memfilter sebelum GABUNG: kami hanya mengambil akar sebagai "tabel sebelah kiri".
2. COUNT(c.id) (bukan COUNT(*)) mengembalikan 0 untuk akar tanpa anak, sedangkan COUNT(*) akan menghasilkan 1 karena baris LEFT JOIN dengan BATAL.

Cobalah sendiri

Tinjau latihan