Cara join tabel di SQL untuk pemula: INNER, LEFT, dan baris yang diam-diam hilang atau berlipat

JOIN adalah perintah SQL pertama yang bisa menghasilkan angka salah tanpa memunculkan satu pun pesan error. Kueri berjalan, hasilnya keluar, totalnya terlihat wajar, dan tidak ada yang tahu bahwa sebagian baris sudah dibuang atau justru dihitung dua kali. Artikel ini membedah perilaku itu dan cara memeriksanya. Penilaian di artikel ini berdasarkan sumber yang dicantumkan di bagian akhir.

Di peta belajar SQL, JOIN adalah langkah keenam dan disebut di sana hanya sebagai satu butir urutan. Halaman ini memperdalam butir tersebut: bukan sintaksnya, melainkan apa yang terjadi pada jumlah baris setiap kali dua tabel disatukan.

Kenapa data dipecah menjadi beberapa tabel

Pengguna spreadsheet terbiasa dengan satu lembar lebar berisi semua kolom: nama pelanggan, kota, nomor pesanan, nama produk, harga. Basis data tidak disusun begitu. Data pelanggan ada di tabel pelanggan, pesanan di tabel pesanan, dan isi tiap pesanan di detail_pesanan.

Alasannya praktis. Kalau alamat pelanggan ikut ditulis ulang di setiap baris pesanan, pelanggan yang pindah harus diperbarui di ratusan baris sekaligus, dan satu baris yang terlewat membuat data bertentangan dengan dirinya sendiri. Dengan memisahkan tabel, alamat tersimpan sekali di satu tempat. Konsekuensinya, hampir setiap pertanyaan bisnis yang menarik membutuhkan lebih dari satu tabel: itulah pekerjaan JOIN.

Kunci primer dan kunci asing

• Kunci primer (primary key) adalah kolom yang nilainya unik di seluruh tabel dan tidak pernah kosong. Di tabel pelanggan, kolom id adalah kunci primer.
• Kunci asing (foreign key) adalah kolom di tabel lain yang menunjuk ke kunci primer itu. Di tabel pesanan, kolom pelanggan_id menunjuk ke pelanggan.id.
• Satu nilai kunci primer bisa muncul berkali-kali sebagai kunci asing: sepuluh baris pesanan boleh menunjuk ke satu baris pelanggan.
• Kunci asing boleh kosong atau menunjuk ke nilai yang sudah tidak ada, kecuali basis datanya memasang batasan yang melarang. Dua keadaan inilah sumber baris yang hilang saat JOIN.

Data contoh yang dipakai di seluruh artikel

Dua keanehan sengaja dibiarkan, karena data kantor memang begitu. Citra belum pernah memesan, dan pesanan 104 menunjuk ke pelanggan bernomor 9 yang tidak ada di tabel pelanggan.

INNER JOIN: hanya pasangan yang lengkap

SELECT p.nama, o.id AS pesanan_id, o.total
FROM pelanggan p
INNER JOIN pesanan o ON o.pelanggan_id = p.id
ORDER BY o.id;

Tiga baris keluar dari tujuh baris masukan. Ayu muncul dua kali karena punya dua pesanan. Citra lenyap karena tidak punya pesanan, dan pesanan 104 lenyap karena pelanggannya tidak ditemukan. Kata INNER boleh dihilangkan: JOIN saja berarti INNER JOIN di PostgreSQL, MySQL, dan SQLite.

Yang perlu digarisbawahi: dua baris yang hilang tadi tidak dilaporkan ke mana pun. Tidak ada peringatan, tidak ada kolom penanda. Kalau kueri ini dipakai menghitung jumlah pelanggan, jawabannya dua, bukan tiga. Kalau dipakai menghitung total penjualan, hasilnya 800.000, bukan 890.000.

LEFT JOIN: menahan semua baris tabel kiri

SELECT p.nama, o.id AS pesanan_id, o.total
FROM pelanggan p
LEFT JOIN pesanan o ON o.pelanggan_id = p.id
ORDER BY p.id, o.id;

Citra kembali muncul, dengan kolom dari tabel kanan berisi NULL. Pesanan 104 tetap tidak ada, karena LEFT JOIN hanya menjamin keutuhan tabel kiri. Inilah bentuk yang dibutuhkan setiap kali pertanyaannya berbunyi “semua pelanggan, termasuk yang belum pernah membeli”. LEFT JOIN juga alat diagnosis, dengan menyaring baris yang kolom kanannya kosong.

-- Pelanggan yang belum pernah memesan
SELECT p.id, p.nama
FROM pelanggan p
LEFT JOIN pesanan o ON o.pelanggan_id = p.id
WHERE o.id IS NULL;

-- Pesanan yang pelanggannya tidak ditemukan (data yatim)
SELECT o.id, o.pelanggan_id, o.total
FROM pesanan o
LEFT JOIN pelanggan p ON p.id = o.pelanggan_id
WHERE p.id IS NULL;

Baris yang diam-diam hilang

Kasus paling sering: analis menulis INNER JOIN padahal maksudnya LEFT JOIN, lalu melaporkan angka yang lebih kecil dari kenyataan. Karena angkanya tetap masuk akal, kesalahan ini bisa bertahan berbulan-bulan. Tiga pemicunya berulang.

1. Kunci asing kosong. Pesanan tanpa sales yang ditugaskan punya sales_id NULL, dan NULL tidak pernah cocok dengan nilai apa pun, bahkan dengan NULL lain. Seluruh pesanan tanpa sales ikut terbuang.
2. Kunci asing menunjuk ke data yang sudah dihapus, seperti pesanan 104 di atas.
3. Kunci ditulis dengan format berbeda. Kode cabang “07” sebagai teks tidak cocok dengan angka 7, dan “JKT ” dengan spasi di belakang tidak cocok dengan “JKT”. Tidak ada pesan error; hanya hasil yang lebih sedikit.

-- Berapa baris sebelum digabung
SELECT COUNT(*) AS baris_pesanan FROM pesanan;

-- Berapa baris setelah digabung, dan berapa pesanan unik yang selamat
SELECT COUNT(*)             AS baris_hasil,
       COUNT(DISTINCT o.id) AS pesanan_unik
FROM pesanan o
JOIN pelanggan p ON p.id = o.pelanggan_id;

Pada data contoh, kueri pertama menghasilkan 4 dan kueri kedua menghasilkan 3 dan 3. Selisih satu itu adalah pesanan 104. Kalau angkanya sama, tidak ada yang terbuang; kalau lebih kecil, ada baris yang tidak menemukan pasangan dan kamu perlu memutuskan apakah baris itu memang layak dibuang.

Baris yang diam-diam berlipat

Kebalikannya lebih berbahaya karena membesarkan angka, dan angka yang membesar jarang dicurigai. Penyebabnya satu: JOIN ke tabel yang kolom kuncinya tidak unik. Setiap baris kiri digandakan sebanyak baris kanan yang cocok. Contohnya tabel pesanan yang digabung dengan detail_pesanan: pesanan 101 berisi tiga jenis barang, jadi setelah JOIN baris pesanan 101 muncul tiga kali, masing-masing membawa nilai total yang sama.

-- Salah: total pesanan terhitung sekali per baris detail
SELECT SUM(o.total) AS total_penjualan
FROM pesanan o
JOIN detail_pesanan d ON d.pesanan_id = o.id;

-- Benar: jumlahkan nilai yang memang disimpan per baris detail
SELECT SUM(d.jumlah * d.harga) AS total_penjualan
FROM pesanan o
JOIN detail_pesanan d ON d.pesanan_id = o.id;

-- Benar: rekap dulu di tingkat detail, baru gabungkan
WITH detail AS (
  SELECT pesanan_id, SUM(jumlah * harga) AS nilai
  FROM detail_pesanan
  GROUP BY pesanan_id
)
SELECT o.id, o.total, d.nilai
FROM pesanan o
LEFT JOIN detail d ON d.pesanan_id = o.id;

Aturan yang mudah diingat: nilai boleh dijumlahkan hanya pada tingkat tempat nilai itu disimpan. Total pesanan disimpan per pesanan, jadi ia hanya boleh dijumlahkan ketika satu pesanan muncul satu baris. Begitu ada JOIN ke tabel yang lebih rinci, tingkatnya berubah dan SUM di kolom lama tidak lagi sah.

Kapan RIGHT JOIN dan FULL JOIN benar-benar dipakai

RIGHT JOIN menahan semua baris tabel kanan, kebalikan persis dari LEFT JOIN. Karena setiap RIGHT JOIN bisa ditulis ulang sebagai LEFT JOIN dengan urutan tabel ditukar, ia jarang muncul di kueri yang ditulis dari nol. Kegunaannya sempit: menambahkan tabel ke kueri panjang yang sudah jadi tanpa membongkar urutan FROM-nya.

FULL OUTER JOIN menahan kedua sisi sekaligus. Pemakaian nyatanya sempit: membandingkan dua daftar yang seharusnya identik, misalnya absensi mesin sidik jari dengan absensi aplikasi HR, lalu menyaring baris yang salah satu sisinya NULL sebagai selisih yang harus ditindaklanjuti.

Perlu dicatat: SQLite baru mendukung RIGHT JOIN dan FULL JOIN sejak versi 3.39, dan MySQL tidak mendukung FULL OUTER JOIN sama sekali. PostgreSQL dan SQL Server mendukung keduanya. Penilaian redaksi: kalau kamu sedang belajar dan bingung harus menghafal berapa jenis, hafalkan dua saja. INNER JOIN dan LEFT JOIN menutup hampir seluruh pekerjaan analis, dan dua sisanya bisa dipelajari saat kebutuhannya datang.

CROSS JOIN yang tidak disengaja

CROSS JOIN memasangkan setiap baris tabel kiri dengan setiap baris tabel kanan tanpa syarat. Tiga pelanggan kali empat pesanan menghasilkan dua belas baris yang tidak berarti apa-apa. Pemakaian yang sah memang ada, misalnya membuat kerangka laporan berisi semua kombinasi bulan kali cabang supaya bulan tanpa transaksi tetap muncul sebagai nol. Tetapi CROSS JOIN paling sering muncul karena kecelakaan: dua tabel ditulis di FROM dipisah koma, lalu syarat penghubungnya lupa dituliskan di WHERE.

-- Kecelakaan: tidak ada syarat penghubung, hasilnya 12 baris
SELECT p.nama, o.total
FROM pelanggan p, pesanan o;

-- Yang dimaksud penulisnya
SELECT p.nama, o.total
FROM pelanggan p
JOIN pesanan o ON o.pelanggan_id = p.id;

Penilaian redaksi: tulis gabungan dengan kata kunci JOIN dan ON, bukan dengan koma di FROM. Bentuk berkoma masih sah di semua basis data besar, tetapi ia menaruh syarat penghubung jauh dari nama tabelnya, sehingga syarat yang hilang tidak terlihat saat kueri dibaca ulang. Dengan bentuk JOIN, syarat yang hilang langsung tampak karena ON-nya kosong.

Self-join: satu tabel digabungkan dengan dirinya sendiri

Self-join dipakai ketika hubungan yang dicari ada di dalam satu tabel, misalnya tabel karyawan yang punya kolom atasan_id menunjuk ke id karyawan lain di tabel yang sama.

SELECT k.nama AS karyawan, a.nama AS atasan
FROM karyawan k
LEFT JOIN karyawan a ON a.id = k.atasan_id
ORDER BY k.nama;

Alias k dan a wajib, karena tanpa keduanya basis data tidak tahu kolom nama yang mana yang dimaksud. LEFT JOIN dipakai, bukan INNER JOIN, karena direktur utama tidak punya atasan dan akan hilang dari daftar. Pola yang sama berguna untuk mencocokkan penjualan bulan ini dengan bulan sebelumnya di tabel rekap bulanan.

Untuk yang datang dari VLOOKUP dan XLOOKUP

Kalau kamu terbiasa menempelkan harga dari daftar master ke tabel pesanan dengan VLOOKUP, sebagian besar konsep JOIN sudah ada di kepalamu. Yang berbeda adalah beberapa perilaku yang justru menjadi sumber kesalahpahaman.

Baris kedua dan ketiga adalah inti perbedaannya. VLOOKUP menyembunyikan duplikat dengan mengambil satu pasangan saja, sementara JOIN menampilkan semuanya dan membuat barismu berlipat. Sebaliknya, VLOOKUP memamerkan kegagalan pencocokan lewat #N/A yang mencolok, sementara INNER JOIN menutupinya dengan menghapus baris. Keduanya salah dengan cara berlawanan, dan keduanya perlu diperiksa.

Penilaian redaksi: orang yang pindah dari Excel ke SQL paling sering tersandung di baris kedua tabel itu. Di Excel, daftar master diperlakukan sebagai daftar unik dan duplikatnya tidak pernah terasa karena rumusnya menyembunyikan; di SQL, duplikat yang sama langsung melipatgandakan hasil. Karena itu kebiasaan memeriksa keunikan kunci dengan COUNT(*) dibanding COUNT(DISTINCT kunci) lebih penting daripada menghafal jenis-jenis JOIN.

Daftar periksa sebelum memakai hasil JOIN

1. Sebutkan dulu, sebelum menjalankan kueri, berapa baris yang kamu harapkan keluar.
2. Periksa keunikan kunci di tabel kanan dengan COUNT(*) dibanding COUNT(DISTINCT kunci). Tidak sama berarti hasil akan berlipat.
3. Bandingkan COUNT(*) sebelum dan sesudah JOIN. Turun berarti ada baris terbuang; naik berarti ada baris tergandakan.
4. Pastikan setiap SUM dan AVG dihitung pada tingkat tempat nilainya disimpan, bukan setelah digabung ke tabel yang lebih rinci.
5. Cek kunci asing yang kosong dengan WHERE kunci IS NULL sebelum memilih INNER atau LEFT JOIN.
6. Kalau memakai LEFT JOIN, pastikan tidak ada syarat tabel kanan yang tercecer di WHERE.

Enam langkah itu adalah isi sebagian besar tinjauan kueri di tim data, dan semuanya selesai dalam waktu kurang dari satu menit. Di KelasGratis, materi JOIN ada di kelas Mengolah Data dengan SQL, penanganan nilai kosong dan CTE ada di SQL Lanjutan, sedangkan sisi spreadsheet-nya dibahas di kelas Mencari Data dan Menata Data di Spreadsheet.

Satu kebiasaan penutup yang murah: setiap kali sebuah kueri gabungan akhirnya benar, simpan kueri itu bersama pertanyaan bisnis yang dijawabnya dan jumlah baris yang kamu harapkan. Catatan jumlah baris itu berfungsi seperti uji regresi sederhana. Saat kueri yang sama dijalankan bulan depan dan jumlah barisnya melompat, kamu tahu sebelum angkanya masuk ke laporan.