INNER JOIN

Join adalah operasi penggabungan baris dari dua tabel berdasarkan kondisi yang menghubungkan keduanya, dan INNER JOIN adalah bentuknya yang paling sering dipakai dalam query harian. Topik ini membahas sintaks JOIN ... ON, mekanisme pencocokan baris, penggabungan tiga tabel melalui tabel penghubung, serta perbedaan letak kondisi pada ON dan WHERE. Seluruh contoh dieksekusi terhadap basis data contoh resmi sakila, sehingga keluaran yang ditampilkan dapat direproduksi ulang secara persis.

Sintaks dasar#

Klausa JOIN ... ON mendefinisikan dua hal sekaligus, yaitu tabel kedua yang digabungkan dan kondisi yang membuat sebuah baris disebut berpasangan. Sintaks umumnya diperlihatkan potongan berikut.

-- tidak-dijalankan
SELECT t1.kolom_a, t2.kolom_b
FROM tabel1 t1
JOIN tabel2 t2 ON t2.kunci = t1.kunci;

Kata INNER boleh ditulis maupun tidak, karena JOIN tanpa kata sifat sudah berarti INNER JOIN. Penulisannya menjadi ringkas dengan alias tabel, yaitu nama pendek yang diberikan setelah nama tabel dan dipakai untuk mengualifikasi kolom. Kondisi pada ON biasanya membandingkan kolom kunci kedua tabel, tetapi kondisi tersebut boleh memuat lebih dari satu perbandingan. Seluruh kolom dari kedua tabel dapat dirujuk pada daftar SELECT, asalkan setiap nama kolom yang muncul di kedua tabel diberi kualifikasi.

Perintah berikut menggabungkan tabel film dengan tabel language untuk menampilkan judul film beserta nama bahasanya. Jalankan perintahnya.

# judul film beserta nama bahasanya
mysql -t -u root sakila -e "SELECT f.title, l.name AS bahasa FROM film f JOIN language l ON l.language_id = f.language_id ORDER BY f.film_id LIMIT 5;"

+------------------+---------+
| title            | bahasa  |
+------------------+---------+
| ACADEMY DINOSAUR | English |
| ACE GOLDFINGER   | English |
| ADAPTATION HOLES | English |
| AFFAIR PREJUDICE | English |
| AFRICAN EGG      | English |
+------------------+---------+

Keluarannya memuat lima baris pertama film yang diurutkan menurut film_id. Setiap baris hasil merupakan gabungan satu baris film dan satu baris language yang kuncinya sama. Karena seluruh film tercatat berbahasa Inggris, kolom bahasa berisi nilai yang sama untuk semua baris.

Pasangan baris dan penggandaan baris#

INNER JOIN memasangkan setiap baris tabel kiri terhadap setiap baris tabel kanan, lalu menyimpan pasangan yang lolos kondisi ON. Baris yang tidak menemukan pasangan gugur dari hasil, baik dari sisi kiri maupun sisi kanan. Sebaliknya, baris yang menemukan lebih dari satu pasangan muncul berulang sebanyak pasangannya. Diagram berikut memperlihatkan ketiga keadaan tersebut pada tabel film dan film_actor.

Pengulangan baris akibat banyaknya pasangan sering disebut fan-out. Perilakunya terlihat pada perhitungan berikut yang membandingkan jumlah baris ketiga sumber tersebut.

# jumlah baris tiap sumber dan jumlah hasil join
mysql -t -u root sakila -e "SELECT COUNT(*) AS film FROM film;"
mysql -t -u root sakila -e "SELECT COUNT(*) AS film_actor FROM film_actor;"
mysql -t -u root sakila -e "SELECT COUNT(*) AS hasil_join FROM film f JOIN film_actor fa ON fa.film_id = f.film_id;"

+------+
| film |
+------+
| 1000 |
+------+
+------------+
| film_actor |
+------------+
|       5462 |
+------------+
+------------+
| hasil_join |
+------------+
|       5462 |
+------------+

Tabel film berisi 1000 baris dan tabel film_actor berisi 5462 baris, sedangkan hasil join juga berjumlah 5462. Angka terakhir sama dengan jumlah baris film_actor karena setiap barisnya menemukan tepat satu pasangan film. Jumlah baris hasil join, oleh karena itu, tidak boleh dianggap sebagai jumlah film. Menghitung jumlah film dari hasil join membutuhkan COUNT(DISTINCT ...).

Menggabungkan tiga tabel#

Hubungan many-to-many antara film dan aktor disimpan dalam tabel penghubung film_actor, sehingga nama aktor tidak dapat dicapai dengan satu kali join. Pola penyelesaiannya adalah merangkai beberapa klausa JOIN, masing-masing dengan kondisi ON sendiri. Tabel film digabungkan terlebih dahulu dengan film_actor, kemudian hasilnya digabungkan dengan actor. Kolom dari tabel mana pun boleh dipakai pada WHERE di akhir query.

# lima film pertama yang dibintangi aktor dengan actor_id 1
mysql -t -u root sakila -e "SELECT f.title, a.first_name, a.last_name FROM film f JOIN film_actor fa ON fa.film_id = f.film_id JOIN actor a ON a.actor_id = fa.actor_id WHERE a.actor_id = 1 ORDER BY f.title, f.film_id LIMIT 5;"

+-----------------------+------------+-----------+
| title                 | first_name | last_name |
+-----------------------+------------+-----------+
| ACADEMY DINOSAUR      | PENELOPE   | GUINESS   |
| ANACONDA CONFESSIONS  | PENELOPE   | GUINESS   |
| ANGELS LIFE           | PENELOPE   | GUINESS   |
| BULWORTH COMMANDMENTS | PENELOPE   | GUINESS   |
| CHEAPER CLYDE         | PENELOPE   | GUINESS   |
+-----------------------+------------+-----------+

Keluaran menampilkan lima film pertama yang dibintangi aktor dengan actor_id 1, yaitu PENELOPE GUINESS. Urutannya ditentukan oleh ORDER BY f.title, f.film_id, dengan film_id sebagai pemecah seri bila dua film kebetulan berjudul sama. Pola rantai seperti ini dapat diteruskan ke tabel keempat tanpa batas praktis, tetapi setiap tambahan join menambah peluang penggandaan baris, sehingga jumlah hasil akhir selalu perlu dicek ulang.

Alias tabel dan kolom ambigu#

Masalah paling umum saat menulis join adalah nama kolom yang sama dipakai dua tabel sekaligus. Pada tabel film dan film_actor, kolom film_id muncul di keduanya. Apabila kolom itu dirujuk tanpa kualifikasi, MySQL menolak query dengan error 1052. Perintah berikut sengaja memunculkan error tersebut agar bentuk pesannya dikenali.

# kolom film_id dirujuk tanpa kualifikasi
mysql -t -u root sakila -e "SELECT film_id FROM film JOIN film_actor ON film.film_id = film_actor.film_id LIMIT 3;"

ERROR 1052 (23000) at line 1: Column 'film_id' in field list is ambiguous

Perbaikannya adalah memberi kualifikasi pada setiap rujukan kolom, sebagaimana contoh-contoh sebelumnya yang sudah memakai alias f dan fa. Kebiasaan ini sekaligus menjelaskan asal setiap kolom kepada pembaca query. Praktik yang dianjurkan adalah mengualifikasi seluruh kolom pada join, bukan hanya kolom yang benar-benar ambigu.

Versi yang sudah diperbaiki diperlihatkan berikut ini, dan hasilnya kembali normal.

-- tidak-dijalankan
SELECT f.film_id, fa.actor_id
FROM film f
JOIN film_actor fa ON fa.film_id = f.film_id
ORDER BY f.film_id, fa.actor_id
LIMIT 3;

# versi yang sudah memakai alias pada seluruh kolom
mysql -t -u root sakila -e "SELECT f.film_id, fa.actor_id FROM film f JOIN film_actor fa ON fa.film_id = f.film_id ORDER BY f.film_id, fa.actor_id LIMIT 3;"

+---------+----------+
| film_id | actor_id |
+---------+----------+
|       1 |        1 |
|       1 |       10 |
|       1 |       20 |
+---------+----------+

ON versus WHERE#

Untuk INNER JOIN, kondisi yang ditulis pada ON dan kondisi yang ditulis pada WHERE menghasilkan baris yang sama. Optimizer menyatukan kedua kondisi tersebut sebelum mengeksekusi, sehingga tidak ada perbedaan keluaran maupun rencana eksekusi. Perintah berikut menjalankan dua query yang isinya identik, hanya beda letak kondisinya.

# kondisi yang sama, satu di WHERE dan satu di ON
mysql -t -u root sakila -e "SELECT COUNT(*) AS via_where FROM film f JOIN language l ON l.language_id = f.language_id WHERE l.name = 'English';"
mysql -t -u root sakila -e "SELECT COUNT(*) AS via_on FROM film f JOIN language l ON l.language_id = f.language_id AND l.name = 'English';"

+-----------+
| via_where |
+-----------+
|      1000 |
+-----------+
+--------+
| via_on |
+--------+
|   1000 |
+--------+

Kedua versi mengembalikan 1000, yaitu seluruh film yang berpasangan dengan bahasa Inggris. Kesetaraan ini khusus berlaku untuk INNER JOIN. Pada OUTER JOIN, letak kondisi mengubah makna secara nyata, sebagaimana dibahas pada topik LEFT JOIN. Konvensi yang mudah dipertahankan adalah menaruh kondisi penghubung tabel pada ON dan kondisi penyaringan hasil pada WHERE.

Kesalahan umum#

Klausa JOIN pada MySQL boleh ditulis tanpa ON. Tanpa kondisi, setiap baris tabel kiri dipasangkan dengan setiap baris tabel kanan, sehingga hasilnya menjadi perkalian kartesian dan jumlah barisnya melonjak. Contoh berikut menggabungkan 1000 film dengan 6 bahasa tanpa kondisi apa pun.

# JOIN tanpa ON memasangkan setiap baris dengan setiap baris
mysql -t -u root sakila -e "SELECT COUNT(*) AS hasil_kartesian FROM film JOIN language;"

+-----------------+
| hasil_kartesian |
+-----------------+
|            6000 |
+-----------------+

Hasil 6000 baris hampir pasti bukan yang dimaksud. Perbaikannya sederhana, yaitu selalu menyertakan ON yang menyebut kolom kunci kedua tabel. Kesalahan kedua yang berkaitan adalah membaca jumlah baris hasil join sebagai jumlah entitas. Setiap kali join melibatkan tabel penghubung, satu film dapat muncul berkali-kali, sehingga perhitungan yang benar memakai COUNT(DISTINCT f.film_id). Periksa jumlah baris hasil dengan cepat sebelum dipakai untuk laporan.

Ringkasan#

• JOIN atau INNER JOIN hanya menyimpan pasangan baris yang lolos kondisi ON, sedangkan baris tanpa pasangan gugur dari kedua sisi.
• Baris dengan beberapa pasangan muncul berulang (fan-out), sehingga jumlah baris hasil join tidak sama dengan jumlah entitasnya.
• Tabel penghubung menyambungkan hubungan many-to-many, dan beberapa JOIN dapat dirangkai dalam satu query.
• Nama kolom yang sama di dua tabel wajib dikualifikasi, dan alias tabel membuat kualifikasi tersebut ringkas.

Referensi#

• JOIN Clause — MySQL 8.0 Reference Manual — Definisi resmi klausa JOIN beserta variasi INNER, LEFT, dan RIGHT.
• SELECT Statement — MySQL 8.0 Reference Manual — Tata urutan klausa SELECT yang menjadi tempat JOIN berada.