Self Join SQLite: Query Tabel dengan Dirinya Sendiri

Self Join Hanyalah Join dengan Alias

Tidak ada yang istimewa dari self join. Self join adalah JOIN biasa yang kedua sisinya kebetulan tabel yang sama. Kuncinya, SQLite butuh cara untuk membedakan kedua salinan itu, jadi kamu memberi masing-masing sebuah alias.

Kamu memakainya setiap kali sebuah baris di tabel merujuk ke baris lain di tabel yang sama. Kasus klasiknya: tabel employees di mana setiap baris punya manager_id yang menunjuk ke karyawan lain:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada tidak punya manajer. Boris dan Cleo melapor ke Ada. Diego dan Esme melapor ke Boris. Hubungannya sepenuhnya ada di dalam satu tabel, dan justru di situlah self join sangat berguna.

Bentuk Dasarnya

Untuk memasangkan setiap karyawan dengan nama manajernya, join employees dengan dirinya sendiri. Satu salinan berperan sebagai "karyawan", salinan lainnya sebagai "manajer":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

Baca sebagai dua tabel yang kebetulan berbagi penyimpanan. e adalah baris karyawan; m adalah baris manajer. Kondisi join e.manager_id = m.id menyejajarkan keduanya: untuk setiap karyawan, cari baris di m yang id-nya cocok dengan manager_id milik karyawan itu.

Perhatikan bahwa Ada tidak muncul. manager_id miliknya bernilai NULL, dan INNER JOIN membuang baris yang tidak punya pasangan.

Mempertahankan Baris Tanpa Pasangan: LEFT JOIN

Jika kamu ingin semua orang ada di hasil, termasuk yang tidak punya manajer, ganti dengan LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

Sekarang Ada muncul dengan NULL di kolom manajer. Mekanisme self join-nya sama, hanya jenis join-nya yang melakukan apa yang selalu dilakukan LEFT JOIN: menyimpan setiap baris dari sisi kiri dan mengisi yang kosong jika tidak ada pasangannya.

Inilah bentuk yang biasanya kamu inginkan saat menampilkan daftar orang. "Tidak punya manajer" adalah informasi; membuang barisnya tidak.

Alias Itu Tidak Opsional

Coba join tanpa alias, dan SQLite tidak tahu apa maksudmu:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

Setiap kolom muncul dua kali, sekali dari setiap salinan tabel, dan SQLite tidak bisa memilih. Alias menyelesaikan masalah ini dengan memberi setiap salinan namanya sendiri. Pilih alias yang menggambarkan peran baris itu, bukan nama tabelnya:

• e dan m untuk karyawan/manajer.
• parent dan child untuk hierarki.
• a dan b saat kamu membandingkan pasangan sembarang.

Alias itulah satu-satunya alasan self join bisa terbaca dengan rapi.

Mencari Pasangan di Dalam Satu Tabel

Self join tidak hanya untuk hierarki. Setiap kali kamu ingin membandingkan baris di tabel yang sama, pola ini cocok. Ini daftar produk, dan kita ingin setiap pasangan yang harganya sama:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Mug',     9.99),
    ('Notebook', 9.99),
    ('Pen',     2.50),
    ('Pencil',  2.50),
    ('Lamp',   29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Ada dua hal yang perlu diperhatikan. Pertama, a.price = b.price adalah kondisi pencocokan yang sebenarnya. Kedua, a.id < b.id adalah yang mencegah query mengembalikan setiap pasangan dua kali (sekali sebagai (Mug, Notebook), sekali lagi sebagai (Notebook, Mug)) dan mencegah setiap baris dipasangkan dengan dirinya sendiri. Trik < itu layak diingat, karena muncul setiap kali kamu membuat daftar pasangan.

Naik Dua Level

Self join menangani satu lompatan dalam hierarki. Ingin tahu manajer dari manajer setiap karyawan? Join tiga kali:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Setiap alias baru mewakili satu level lagi ke atas pohon. Cara ini baik-baik saja untuk dua atau tiga lompatan, tapi cepat berantakan: kamu harus tahu kedalaman hierarkinya saat menulis query, dan menambahkan satu join untuk setiap level. Itulah tembok yang ingin ditembus oleh CTE rekursif.

Kapan Tidak Perlu Memakai Self Join

Self join adalah alat yang tepat saat kamu butuh kolom dari kedua sisi hubungan di hasilnya. Jika kamu hanya perlu memfilter, misalnya mencari setiap karyawan yang manajernya Ada, subquery sering lebih enak dibaca:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Tanpa akrobat alias, dan maksudnya tidak mungkin salah dipahami. Patokannya: apakah kamu ingin data dari kedua baris di output? Self join. Apakah kamu hanya butuh satu nilai untuk dibandingkan? Subquery.

Untuk hierarki dengan kedalaman sembarang (bagan organisasi, pohon file, komentar berutas), kedua pola ini tidak bisa diandalkan. Itu wilayah CTE rekursif.

Selanjutnya: Subquery

Self join dan subquery menyelesaikan masalah yang beririsan, dan mengetahui mana yang cocok akan menghemat banyak waktumu memelototi SQL nanti. Halaman berikutnya membahas subquery secara mendalam, yaitu bentuk skalar, berkorelasi, dan IN, serta di mana masing-masing unggul.