Self-join

Menggabungkan tabel dengan dirinya sendiri untuk struktur hierarki dan pasangan antarbaris.

Daftar isi

Topik ini membahas self-join, yaitu join yang menyandingkan sebuah tabel dengan dirinya sendiri melalui alias yang berbeda. Dua pola yang dilayani adalah hierarki yang menyimpan rujukan ke baris induknya, dan pencarian pasangan antarbaris pada satu tabel yang sama.

Blok penyiapan dataset#

bash

# bagan staf dengan rujukan atasan pada tabel yang sama
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori, staf CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
CREATE TABLE staf (
    id         SERIAL PRIMARY KEY,
    nama       TEXT NOT NULL,
    atasan_id  INTEGER REFERENCES staf(id)
);
INSERT INTO staf (nama, atasan_id) VALUES
    ('Hana Zahira', NULL),
    ('Ivan Gunawan', 1),
    ('Joko Prasetyo', 1),
    ('Kartika Dewi', 2),
    ('Lukman Hakim', 2);
SQL

Tabel staf menyimpan atasan sebagai id yang menunjuk baris pada tabel yang sama, dan Hana Zahira tanpa atasan menempati puncak bagan.

Menyandingkan bawahan dengan atasannya#

Self-join memakai dua alias untuk satu tabel, sehingga server memperlakukannya sebagai dua salinan logis. Query berikut menyandingkan setiap staf dengan atasannya melalui LEFT JOIN, karena baris teratas tidak memiliki atasan dan harus tetap tampil.

bash

# setiap staf beserta nama atasannya
psql -U postgres -d perpustakaan -c "SELECT s.nama AS bawahan, a.nama AS atasan FROM staf s LEFT JOIN staf a ON s.atasan_id = a.id ORDER BY s.id;"

text Keluaran

    bawahan    |    atasan
---------------+--------------
 Hana Zahira   |
 Ivan Gunawan  | Hana Zahira
 Joko Prasetyo | Hana Zahira
 Kartika Dewi  | Ivan Gunawan
 Lukman Hakim  | Ivan Gunawan
(5 rows)

Kolom atasan milik Hana Zahira tampil kosong karena LEFT JOIN menjaganya, sebagaimana dibahas pada topik outer join. Pertanyaan kebalikannya, yaitu daftar bawahan setiap atasan, diselesaikan dengan membalik arah join yang sama, dan kedua arah ini melayani tampilan bagan organisasi naik dan turun.

Hierarki berlapis yang lebih dalam, seperti anak dari anak, menuntut join bertingkat per tingkat atau CTE rekursif yang dibahas pada modul subquery dan CTE, karena jumlah join bertingkat bertambah setiap tingkat hierarki.

Mencari pasangan dalam satu tabel#

Pola kedua menyandingkan baris dengan baris lain yang berbagi sifat, misalnya dua buku karya penulis yang sama. Syarat pembanding id yang mengecil memastikan setiap pasangan muncul sekali, alih-alih dua kali dalam urutan berbeda, dan baris yang dipasangkan dengan dirinya sendiri tereliminasi.

bash

# pasangan buku karya penulis yang sama
psql -U postgres -d perpustakaan -c "SELECT a.judul AS buku_1, b.judul AS buku_2, a.penulis FROM buku a JOIN buku b ON a.penulis = b.penulis AND a.id < b.id ORDER BY a.penulis, a.id;"

text Keluaran

       buku_1        |           buku_2            |    penulis
---------------------+-----------------------------+----------------
 Jaringan Komputer   | Algoritma dan Struktur Data | Ahmad Fauzi
 Fisika untuk Pemula | Kimia Organik               | Rina Marlina
 Dasar Basis Data    | Statistika Terapan          | Sari Wulandari
(3 rows)

Tiga penulis yang menulis lebih dari satu buku menghasilkan tiga pasangan, dan syarat a.id < b.id menjadi penentu keunikan pasangan. Tanpa syarat itu, setiap pasangan muncul dua kali dalam urutan terbalik, ditambah setiap buku yang berpasangan dengan dirinya sendiri.

Kesalahan umum#

Kesalahan pertama adalah memakai satu tabel dua kali tanpa alias, yang ditolak server dengan pesan nama tabel duplikat, dan dua alias yang bermakna seperti s untuk staf serta a untuk atasan menjaga query tetap terbaca. Kesalahan kedua adalah lupa syarat pembanding pada pencarian pasangan, sehingga setiap baris berpasangan dengan dirinya sendiri dan jumlah baris hasil melonjak.

Kesalahan ketiga adalah menulis syarat pasangan dengan <> alih-alih pembanding arah, yang menghasilkan setiap pasangan dua kali dalam urutan berbeda. Kesalahan keempat adalah mengira self-join menggandakan data, padahal yang digandakan hanya tampilan logisnya, dan server membaca berkas tabelnya sekali sesuai kebutuhan rencana eksekusi.

Ketika hierarki yang disimpan bertambah kompleks, dua pola penyimpanannya perlu dikenali, yaitu daftar ketetanggaan berupa kolom atasan yang dipakai di sini, dan tabel penutup yang menyimpan seluruh pasangan leluhur-keturunan sejak awal. Daftar ketetanggaan hemat pada penulisan dan dipakai ulang pada CTE rekursif, sedangkan tabel penutup mempercepat query kedalaman dengan menanggung biaya pemeliharaan pada trigger. Pilihan awalnya mengikuti frekuensi pertanyaan, karena hierarki yang sering dibaca lintas kedalaman mempercepat dirinya sendiri melalui tabel penutup.

Ringkasan#

Self-join memakai dua alias untuk satu tabel, sehingga baris dapat disandingkan dengan baris lain di tabel yang sama.
Hierarki dengan kolom atasan dibaca melalui LEFT JOIN agar baris teratas tetap tampil dengan kolom induk kosong.
Pencarian pasangan memakai syarat pembanding arah seperti a.id < b.id agar setiap pasangan muncul tepat sekali.
Hierarki dalam berlapis lebih nyaman ditelusuri CTE rekursif, sedangkan self-join cocok untuk satu tingkat hubungan.

Referensi#

Table and Column Aliases — PostgreSQL Documentation — Alias yang diperlukan untuk memakai satu tabel dua kali.
Joined Tables — PostgreSQL Documentation — Referensi bentuk join yang dipakai pada self-join.
Joins Between Tables — PostgreSQL Documentation — Bagian tutorial resmi tentang join.