Contoh Soal SQL JOIN: INNER, LEFT, dan Kenapa Barisnya Bertambah

INNER JOIN hanya mengembalikan baris yang punya pasangan di kedua tabel, sedangkan LEFT JOIN mengembalikan semua baris tabel kiri dan mengisi NULL kalau pasangannya tidak ada. Kebanyakan soal UTS tentang JOIN tidak menguji definisi itu. Yang diuji adalah tiga jebakan: jumlah baris yang bertambah setelah JOIN, NULL dari LEFT JOIN yang hilang karena WHERE, dan cara mencari data yang tidak punya pasangan. Semua contoh soal SQL JOIN di bawah memakai dua tabel kecil yang sama, dan hasilnya ditulis baris per baris supaya bisa kamu cocokkan.

#Data dua tabel yang dipakai di semua contoh

Tabel mahasiswa (4 baris):

Tabel krs (6 baris):

Dua baris sengaja dibuat tanpa pasangan. Bayu (2204) belum mengisi KRS. Baris terakhir krs memakai NIM 2209 yang tidak ada di tabel mahasiswa, misalnya karena salah ketik dan tabelnya tidak memakai foreign key. Kedua baris ini yang membuat hasil INNER, LEFT, dan RIGHT JOIN berbeda.

#Hasil INNER JOIN baris per baris

SELECT m.nim, m.nama, k.kode_mk, k.nilai
FROM mahasiswa m
INNER JOIN krs k ON m.nim = k.nim
ORDER BY m.nim, k.kode_mk;

Hasilnya 5 baris. Bayu tidak muncul karena tidak punya baris di krs. Baris NIM 2209 juga tidak muncul karena tidak punya pasangan di mahasiswa. INNER JOIN membuang yang tidak berpasangan dari kedua sisi.

Cara mengerjakannya di kertas: ambil satu baris tabel kiri, cari semua baris tabel kanan yang memenuhi kondisi ON, lalu tulis satu baris hasil untuk setiap pasangan. Rina punya tiga pasangan, jadi Rina muncul tiga kali.

#LEFT JOIN dan RIGHT JOIN: dari mana NULL muncul

Ganti INNER JOIN dengan LEFT JOIN pada query yang sama, dan hasilnya 6 baris:

Lima baris pertama sama dengan INNER JOIN. Bayu ditambahkan di akhir dengan kolom dari krs berisi NULL, karena LEFT JOIN menjamin setiap baris tabel kiri muncul minimal sekali.

RIGHT JOIN adalah kebalikannya: semua baris krs dijamin muncul. Hasilnya juga 6 baris, tetapi baris keenamnya berbeda:

Di query RIGHT JOIN, ambil k.nim, bukan m.nim. Kalau yang diambil m.nim, kolom NIM baris itu ikut NULL dan kamu tidak tahu NIM mana yang bermasalah.

Ringkasan jumlah baris untuk data ini:

MySQL tidak punya FULL OUTER JOIN. Di sana hasilnya dibuat dengan LEFT JOIN yang di-UNION dengan RIGHT JOIN.

#Kenapa jumlah baris bertambah setelah JOIN?

Tabel mahasiswa punya 4 baris, tetapi INNER JOIN menghasilkan 5. Ini bukan error. Relasi mahasiswa ke KRS adalah satu ke banyak, jadi setiap mahasiswa muncul sebanyak jumlah KRS-nya: Rina 3, Andi 1, Dewi 1.

Masalahnya muncul saat kamu menghitung sesuatu dari tabel kiri setelah JOIN. Soal: "Berapa jumlah mahasiswa yang sudah mengisi KRS di setiap prodi?"

SELECT m.prodi, COUNT(*) AS jumlah
FROM mahasiswa m
JOIN krs k ON m.nim = k.nim
GROUP BY m.prodi;

Query ini memberi SI = 4 dan TI = 1. Jawaban SI salah, karena mahasiswa SI yang mengisi KRS hanya Rina dan Dewi. Angka 4 datang dari 3 baris Rina ditambah 1 baris Dewi. Ganti COUNT(*) dengan COUNT(DISTINCT m.nim) dan hasilnya menjadi SI = 2, TI = 1.

Penggandaan ini makin besar kalau kedua sisi sama-sama banyak. Misalkan ada tabel jadwal dengan 2 baris untuk IF201 (kelas A dan kelas B). JOIN krs ke jadwal pada kode_mk membuat 2 baris KRS IF201 masing-masing berpasangan dengan 2 jadwal, sehingga hasilnya 2 × 2 = 4 baris. SUM nilai apa pun di atas hasil itu langsung dua kali lipat.

Jebakan serupa ada di LEFT JOIN. Untuk menghitung jumlah mata kuliah per mahasiswa, COUNT(*) memberi Bayu nilai 1, karena baris NULL-nya tetap terhitung sebagai baris. COUNT(k.kode_mk) memberi 0, karena COUNT pada kolom melewati NULL. Hasil lengkapnya: Rina 3, Andi 1, Dewi 1, Bayu 0. Pengelompokan seperti ini dibahas lebih jauh di contoh soal GROUP BY dan HAVING.

#Kenapa NULL dari LEFT JOIN hilang setelah ditambah WHERE?

Soal: "Tampilkan semua mahasiswa beserta nilai IF201-nya. Mahasiswa yang tidak mengambil IF201 tetap ditampilkan dengan nilai kosong."

Jawaban yang sering ditulis:

SELECT m.nim, m.nama, k.nilai
FROM mahasiswa m
LEFT JOIN krs k ON m.nim = k.nim
WHERE k.kode_mk = 'IF201';

Hasilnya hanya 2 baris: Rina A dan Andi C. Dewi dan Bayu hilang. Urutannya begini: LEFT JOIN membuat 6 baris dulu, lalu WHERE menyaring. Baris Bayu punya kode_mk NULL, dan NULL = 'IF201' tidak bernilai benar, jadi baris itu dibuang. Baris Dewi berisi IF202, juga dibuang. LEFT JOIN-nya berubah menjadi INNER JOIN tanpa kamu sadari.

Perbaikannya, pindahkan syarat tabel kanan ke dalam ON:

SELECT m.nim, m.nama, k.nilai
FROM mahasiswa m
LEFT JOIN krs k ON m.nim = k.nim AND k.kode_mk = 'IF201'
ORDER BY m.nim;

Aturannya: syarat untuk tabel kanan di LEFT JOIN taruh di ON. Syarat untuk tabel kiri (misalnya m.prodi = 'SI') boleh di WHERE, karena tidak menyentuh baris NULL.

#Mencari mahasiswa yang belum mengambil mata kuliah

Soal "tampilkan mahasiswa yang belum mengisi KRS" dijawab dengan anti join: LEFT JOIN, lalu ambil hanya baris yang pasangannya NULL.

SELECT m.nim, m.nama
FROM mahasiswa m
LEFT JOIN krs k ON m.nim = k.nim
WHERE k.nim IS NULL;

Hasilnya satu baris: 2204 Bayu. Di sini WHERE pada tabel kanan justru disengaja, karena yang dicari memang baris NULL. Bedanya dengan jebakan di atas: syaratnya IS NULL, bukan perbandingan nilai.

Versi yang lebih spesifik, "mahasiswa yang belum mengambil IF201", menggabungkan kedua teknik. Syarat mata kuliah masuk ON, syarat NULL masuk WHERE:

SELECT m.nim, m.nama
FROM mahasiswa m
LEFT JOIN krs k ON m.nim = k.nim AND k.kode_mk = 'IF201'
WHERE k.nim IS NULL;

Hasilnya Dewi dan Bayu. Kalau k.kode_mk = 'IF201' dipindah ke WHERE, hasilnya kosong, karena tidak ada baris yang kode_mk-nya IF201 sekaligus NIM-nya NULL.

Cara lain yang hasilnya sama adalah NOT EXISTS:

SELECT m.nim, m.nama
FROM mahasiswa m
WHERE NOT EXISTS (SELECT 1 FROM krs k WHERE k.nim = m.nim);

Hindari NOT IN (SELECT nim FROM krs) untuk soal ini. Kalau ada satu saja baris krs dengan nim NULL, NOT IN mengembalikan hasil kosong untuk semua mahasiswa, termasuk Bayu.

#Kesalahan yang sering terjadi di soal JOIN

1. Menulis kondisi JOIN di WHERE dengan koma (FROM mahasiswa, krs) lalu lupa kondisinya. Hasilnya cross join: 4 × 6 = 24 baris.
2. Menganggap jumlah baris hasil JOIN sama dengan jumlah baris tabel kiri. Itu hanya benar kalau relasinya satu ke satu.
3. Memakai COUNT(*) setelah LEFT JOIN untuk menghitung jumlah pasangan, sehingga yang tidak punya pasangan terhitung 1.
4. Menaruh syarat tabel kanan di WHERE pada LEFT JOIN.
5. Mengambil kolom kunci dari tabel yang bisa NULL. Di LEFT JOIN, ambil m.nim. Di RIGHT JOIN, ambil k.nim.
6. Menulis = NULL alih-alih IS NULL. Perbandingan = NULL tidak pernah bernilai benar.

Kalau tabel di soalmu berasal dari desain yang kamu buat sendiri, pastikan dulu relasinya benar lewat mengubah ERD ke tabel. Data yatim seperti NIM 2209 hanya bisa masuk kalau foreign key tidak dipasang, seperti dibahas di normalisasi 1NF, 2NF, 3NF.

#FAQ

#Apakah JOIN tanpa kata INNER sama dengan INNER JOIN?

Ya. JOIN saja diperlakukan sebagai INNER JOIN di MySQL, PostgreSQL, SQL Server, dan SQLite.

#Apakah LEFT JOIN sama dengan LEFT OUTER JOIN?

Sama. Kata OUTER boleh ditulis atau tidak.

#Apakah RIGHT JOIN pernah benar-benar diperlukan?

Hampir tidak. Setiap RIGHT JOIN bisa ditulis ulang sebagai LEFT JOIN dengan menukar urutan tabel, dan kebanyakan orang lebih mudah membaca query yang semua JOIN-nya ke kiri.

#Apakah urutan tabel berpengaruh di INNER JOIN?

Tidak untuk hasilnya. mahasiswa JOIN krs dan krs JOIN mahasiswa memberi baris yang sama, hanya urutan kolomnya yang mengikuti SELECT. Di LEFT JOIN urutan menentukan tabel mana yang dijamin tampil.

#Bagaimana JOIN tiga tabel ditulis?

Rantaikan saja: FROM mahasiswa m JOIN krs k ON m.nim = k.nim JOIN matakuliah mk ON k.kode_mk = mk.kode_mk. Kalau JOIN pertama LEFT, JOIN berikutnya ke tabel kanan biasanya juga harus LEFT, supaya baris NULL tidak terbuang di langkah kedua.