KAL
Menu

Join Diri dan Multi-Tabel

Join tabel dengan dirinya sendiri untuk hierarki dan rantai empat tabel yang realistis.

Daftar isi

Join tidak selalu menghubungkan dua tabel yang berbeda. Sebuah tabel dapat digabungkan dengan dirinya sendiri untuk membaca data yang barisnya saling merujuk di dalam satu tabel, misalnya hierarki kategori. Beberapa tabel juga dapat dirangkai dalam satu query untuk menyusun laporan. Topik ini menyiapkan basis data latihan perpustakaan, memperagakan join diri pada hierarki kategori, lalu menutup dengan rantai empat tabel dari peminjaman sampai kategori. Seluruh data bersifat buatan dan deterministik, sehingga eksekusi ulang menghasilkan keluaran yang sama.

Basis data latihan perpustakaan#

Empat tabel disiapkan, yaitu kategori, buku, anggota, dan peminjaman. Tabel kategori menyimpan hierarki dua tingkat melalui kolom induk_id yang merujuk ke kategori_id pada tabel yang sama. Relasi seperti ini adalah foreign key yang menunjuk tabelnya sendiri, dan untuk keperluan latihan pola dua tingkat sudah cukup menampilkan ide dasarnya. Berkas berikut membuat ulang seluruh basis data dari nol setiap kali dijalankan.

sql skema-perpustakaan.sql 
-- tidak-dijalankan
-- Skema basis data latihan "perpustakaan" untuk topik join diri dan multi-tabel.
-- Menjalankan ulang berkas ini selalu menghasilkan isi yang sama.

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE kategori (
  kategori_id TINYINT UNSIGNED PRIMARY KEY,
  nama VARCHAR(40) NOT NULL,
  induk_id TINYINT UNSIGNED NULL,
  CONSTRAINT fk_induk FOREIGN KEY (induk_id) REFERENCES kategori (kategori_id)
);

INSERT INTO kategori (kategori_id, nama, induk_id) VALUES
  (1, 'Fiksi', NULL),
  (2, 'Non-Fiksi', NULL),
  (3, 'Novel', 1),
  (4, 'Cerpen', 1),
  (5, 'Sejarah', 2),
  (6, 'Sains', 2);

CREATE TABLE buku (
  buku_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  judul VARCHAR(60) NOT NULL,
  kategori_id TINYINT UNSIGNED NOT NULL,
  CONSTRAINT fk_buku_kategori FOREIGN KEY (kategori_id) REFERENCES kategori (kategori_id)
);

INSERT INTO buku (judul, kategori_id) VALUES
  ('Laut Tenang', 3),
  ('Kota Tua', 3),
  ('Dua Musim', 4),
  ('Jalan Sore', 4),
  ('Kapal Dagang Nusantara', 5),
  ('Surat dari Koloni', 5),
  ('Awan dan Hujan', 6),
  ('Iklim Kepulauan', 6);

CREATE TABLE anggota (
  anggota_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nama VARCHAR(40) NOT NULL
);

INSERT INTO anggota (nama) VALUES
  ('Ali'), ('Budi'), ('Citra'), ('Dewi');

CREATE TABLE peminjaman (
  peminjaman_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  anggota_id INT UNSIGNED NOT NULL,
  buku_id INT UNSIGNED NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_pinjam_anggota FOREIGN KEY (anggota_id) REFERENCES anggota (anggota_id),
  CONSTRAINT fk_pinjam_buku FOREIGN KEY (buku_id) REFERENCES buku (buku_id)
);

INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES
  (1, 1, '2024-07-01', '2024-07-08'),
  (1, 5, '2024-07-03', '2024-07-10'),
  (2, 3, '2024-07-05', NULL),
  (3, 2, '2024-07-06', '2024-07-13'),
  (3, 7, '2024-07-08', NULL),
  (4, 6, '2024-07-10', '2024-07-17'),
  (4, 4, '2024-07-12', NULL),
  (2, 8, '2024-07-14', NULL);
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < skema-perpustakaan.sql

Perintah di atas tidak mencetak apa pun, karena keluaran DDL pada klien batch memang kosong. Isi tabel kategori diperlihatkan berikut agar hierarkinya terbaca sebelum digabungkan.

bash 
# isi tabel kategori dengan hierarki dua tingkat
mysql -t -u root perpustakaan -e "SELECT kategori_id, nama, induk_id FROM kategori ORDER BY kategori_id;"
text Keluaran 
+-------------+-----------+----------+
| kategori_id | nama      | induk_id |
+-------------+-----------+----------+
|           1 | Fiksi     |     NULL |
|           2 | Non-Fiksi |     NULL |
|           3 | Novel     |        1 |
|           4 | Cerpen    |        1 |
|           5 | Sejarah   |        2 |
|           6 | Sains     |        2 |
+-------------+-----------+----------+

Dua kategori teratas memiliki induk_id NULL karena tidak berada di bawah kategori lain. Empat kategori sisanya merujuk ke salah satu dari keduanya. Struktur seperti ini tidak dapat dibaca dengan join biasa, karena data induk dan data anak berada pada tabel yang sama.

Join diri dengan alias peran#

Join diri memperlakukan satu tabel seolah menjadi dua salinan, lalu menggabungkan kedua salinan itu. SQL menuntut alias yang berbeda untuk masing-masing salinan, dan alias tersebut sekaligus menyatakan perannya. Alias anak menunjuk baris kategori yang sedang dibaca, sedangkan alias induk menunjuk kategori di atasnya. Karena kategori teratas tidak punya induk, join yang tepat adalah LEFT JOIN agar kategori tersebut tetap tampil.

sql Join diri pada tabel kategori 
-- tidak-dijalankan
SELECT anak.nama, induk.nama
FROM kategori anak
LEFT JOIN kategori induk ON induk.kategori_id = anak.induk_id;
bash 
# setiap kategori ditampilkan bersama nama induknya
mysql -t -u root perpustakaan -e "SELECT anak.nama AS kategori_anak, induk.nama AS kategori_induk FROM kategori anak LEFT JOIN kategori induk ON induk.kategori_id = anak.induk_id ORDER BY anak.kategori_id;"
text Keluaran 
+---------------+----------------+
| kategori_anak | kategori_induk |
+---------------+----------------+
| Fiksi         | NULL           |
| Non-Fiksi     | NULL           |
| Novel         | Fiksi          |
| Cerpen        | Fiksi          |
| Sejarah       | Non-Fiksi      |
| Sains         | Non-Fiksi      |
+---------------+----------------+

Kolom kategori_induk berisi NULL untuk Fiksi dan Non-Fiksi, sedangkan kategori sisanya membawa nama induknya. Kedua alias pada query di atas memang mengacu ke tabel yang sama, tetapi mewakili baris yang berbeda-beda. Membaca alias sebagai peran, bukan sekadar nama pendek, membuat join diri jauh lebih mudah diikuti. Pola yang sama berlaku untuk hierarki yang lebih dalam, misalnya tiga tingkat yang membutuhkan dua kali join diri beruntun.

Rantai empat tabel#

Laporan peminjaman yang lengkap menyatukan empat tabel sekaligus. Kolom anggota_id pada peminjaman merujuk ke tabel anggota, kolom buku_id merujuk ke tabel buku, dan tabel buku membawa kategori_id yang merujuk ke tabel kategori. Rantainya berbunyi peminjaman, anggota, buku, lalu kategori, dan setiap mata rantai memiliki satu kondisi ON sendiri. Query berikut menyusun daftar peminjaman lengkap dengan nama peminjam, judul buku, dan kategorinya.

bash 
# peminjaman lengkap dengan anggota, buku, dan kategorinya
mysql -t -u root perpustakaan -e "SELECT p.peminjaman_id, a.nama AS anggota, b.judul, k.nama AS kategori, p.tanggal_pinjam FROM peminjaman p JOIN anggota a ON a.anggota_id = p.anggota_id JOIN buku b ON b.buku_id = p.buku_id JOIN kategori k ON k.kategori_id = b.kategori_id ORDER BY p.peminjaman_id;"
text Keluaran 
+---------------+---------+------------------------+----------+----------------+
| peminjaman_id | anggota | judul                  | kategori | tanggal_pinjam |
+---------------+---------+------------------------+----------+----------------+
|             1 | Ali     | Laut Tenang            | Novel    | 2024-07-01     |
|             2 | Ali     | Kapal Dagang Nusantara | Sejarah  | 2024-07-03     |
|             3 | Budi    | Dua Musim              | Cerpen   | 2024-07-05     |
|             4 | Citra   | Kota Tua               | Novel    | 2024-07-06     |
|             5 | Citra   | Awan dan Hujan         | Sains    | 2024-07-08     |
|             6 | Dewi    | Surat dari Koloni      | Sejarah  | 2024-07-10     |
|             7 | Dewi    | Jalan Sore             | Cerpen   | 2024-07-12     |
|             8 | Budi    | Iklim Kepulauan        | Sains    | 2024-07-14     |
+---------------+---------+------------------------+----------+----------------+

Delapan baris peminjaman tampil dengan seluruh nama dan judul yang sudah terurai. Kolom peminjaman_id dipakai sebagai urutan karena nilainya naik sesuai urutan penyisipan. Membaca query semacam ini paling mudah dengan mengikuti alias satu per satu, yaitu dari p ke a, lalu b, dan k. Pola rantai empat tabel seperti ini sudah mendekati bentuk query laporan yang dipakai sehari-hari, hanya dengan jumlah baris yang lebih besar.

Kesalahan umum#

Kesalahan pertama khusus pada join diri, yaitu menulis dua salinan tabel tanpa alias yang membedakan. Server langsung menolak dengan pesan yang menunjuk soal alias, sebagaimana diperlihatkan perintah berikut.

bash 
# dua salinan kategori tanpa alias yang membedakan
mysql -t -u root perpustakaan -e "SELECT nama FROM kategori JOIN kategori ON kategori.induk_id = kategori.kategori_id LIMIT 1;"
text Keluaran 
ERROR 1066 (42000) at line 1: Not unique table/alias: 'kategori'

Perbaikannya adalah memberi alias berbeda untuk setiap salinan tabel, seperti anak dan induk pada bagian sebelumnya. Kesalahan kedua adalah menggabungkan tabel tanpa kondisi, yang menghasilkan perkalian kartesian. Tabel peminjaman berisi 8 baris dan anggota berisi 4 baris, sehingga gabungan tanpa ON menghasilkan 32 baris yang tidak bermakna.

bash 
# peminjaman digabung anggota tanpa kondisi
mysql -t -u root perpustakaan -e "SELECT COUNT(*) AS hasil FROM peminjaman JOIN anggota;"
text Keluaran 
+-------+
| hasil |
+-------+
|    32 |
+-------+

Angka 32 tersebut muncul tanpa pesan error apa pun, sehingga kesalahan ini tidak terasa sampai laporan dibaca ulang. Disiplin yang mencegahnya sederhana, yaitu setiap JOIN wajib diikuti ON yang menyebut kolom kunci kedua tabel. Aturan yang sama berlaku pada join diri, karena kondisinya justru yang menentukan baris mana dari salinan pertama berpasangan dengan baris mana dari salinan kedua.

Ringkasan#

  • Join diri menggabungkan satu tabel dengan salinannya sendiri, dan setiap salinan wajib diberi alias berbeda.
  • Kolom yang merujuk ke tabelnya sendiri, seperti induk_id, dibaca dengan LEFT JOIN agar baris teratas yang tidak punya induk tetap tampil.
  • Rantai empat tabel disusun dengan beberapa JOIN beruntun, dan setiap hubungan memiliki satu kondisi ON.
  • Join tanpa ON menghasilkan perkalian kartesian tanpa peringatan, sehingga jumlah barisnya melonjak.

Referensi#