LEFT JOIN

LEFT JOIN menggabungkan dua tabel sebagaimana INNER JOIN, tetapi tetap menampilkan setiap baris tabel kiri meskipun tidak menemukan pasangan di tabel kanan. Topik ini membahas kolom kanan yang berisi NULL, perhitungan pasangan per kelompok, pola anti-join untuk mencari baris yang absen, serta perbedaan letak kondisi yang khusus berlaku pada OUTER JOIN. Contoh dieksekusi terhadap basis data contoh sakila sehingga seluruh keluarannya dapat direproduksi.

Baris kiri yang tetap tampil#

LEFT JOIN adalah bentuk OUTER JOIN yang menahan seluruh baris tabel kiri. Ketika sebuah baris kiri tidak lolos kondisi ON, baris tersebut tetap muncul di hasil dengan seluruh kolom kanan berisi NULL. Sintaksnya diperlihatkan potongan berikut.

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

Diagram berikut merangkum mekanismenya, yaitu baris kiri yang berpasangan digabungkan, baris kiri tanpa pasangan tetap tampil dengan kolom kanan NULL, sedangkan baris kanan yang tidak terpasang gugur dari hasil.

Basis data sakila menyediakan contoh yang mudah diamati. Tabel language berisi enam bahasa, tetapi seluruh film hanya mencantumkan bahasa Inggris, sehingga lima bahasa lain tidak punya pasangan. Query berikut menyaring tabel kiri ke tiga bahasa tanpa pasangan, dan eksekusinya memperlihatkan kolom title yang berisi NULL.

# tiga bahasa tanpa pasangan film tetap tampil
mysql -t -u root sakila -e "SELECT l.name AS bahasa, f.title FROM language l LEFT JOIN film f ON f.language_id = l.language_id WHERE l.language_id IN (2,3,4) ORDER BY l.language_id;"

+----------+-------+
| bahasa   | title |
+----------+-------+
| Italian  | NULL  |
| Japanese | NULL  |
| Mandarin | NULL  |
+----------+-------+

Tiga bahasa tersebut tetap tampil walau tidak ada satu pun film yang memakainya. Nilai NULL bukan tanda error, melainkan cara SQL menyatakan ketiadaan pasangan. Pengujian keberadaan pasangan dilakukan dengan IS NULL atau IS NOT NULL, bukan dengan perbandingan = NULL.

Menghitung pasangan per kelompok#

Agregasi setelah LEFT JOIN sering membingungkan karena dua cara menghitung memberi angka yang berbeda. COUNT(*) menghitung seluruh baris hasil, termasuk baris tanpa pasangan. COUNT(kolom) menghitung baris yang kolomnya tidak NULL, sehingga angkanya menyatakan jumlah pasangan yang sesungguhnya. Query berikut menjalankan keduanya sekaligus pada satu kelompok yang sama.

# COUNT(*) menghitung baris, COUNT(f.film_id) menghitung pasangan
mysql -t -u root sakila -e "SELECT l.name, COUNT(*) AS jumlah_baris, COUNT(f.film_id) AS jumlah_pasangan FROM language l LEFT JOIN film f ON f.language_id = l.language_id GROUP BY l.language_id, l.name ORDER BY l.language_id;"

+----------+--------------+-----------------+
| name     | jumlah_baris | jumlah_pasangan |
+----------+--------------+-----------------+
| English  |         1000 |            1000 |
| Italian  |            1 |               0 |
| Japanese |            1 |               0 |
| Mandarin |            1 |               0 |
| French   |            1 |               0 |
| German   |            1 |               0 |
+----------+--------------+-----------------+

Tabel language yang digabungkan dengan LEFT JOIN menghasilkan enam kelompok. Bahasa Inggris mencatat 1000 baris dan 1000 pasangan, sedangkan lima bahasa lain masing-masing mencatat satu baris dengan nol pasangan. Membaca jumlah pasangan dari COUNT(f.film_id), bukan dari COUNT(*), membuat laporan per kelompok jujur terhadap data yang tidak lengkap. Pola ini berlaku untuk setiap agregasi yang dihitung dari tabel kanan, misalnya SUM dan AVG yang mengabaikan NULL.

Pola anti-join#

Baris yang tidak punya pasangan dapat ditemukan dengan menggabungkan LEFT JOIN dan IS NULL. Pola ini sering disebut anti-join karena menyaring baris yang justru gagal terhubung. Polanya dibaca sebagai perintah, yaitu ambil seluruh baris kiri, lalu sisakan yang kolom kanannya NULL.

Tabel inventory pada sakila memuat 4581 item DVD yang dapat disewa, sedangkan tabel rental mencatat setiap peminjaman. Item yang tidak pernah disewa adalah baris inventory yang tidak muncul di rental. Query berikut menghitung jumlahnya lalu menampilkannya.

# menghitung dan menampilkan inventaris yang tidak pernah disewa
mysql -t -u root sakila -e "SELECT COUNT(*) AS jumlah_inventaris FROM inventory;"
mysql -t -u root sakila -e "SELECT COUNT(*) AS tanpa_sewa FROM inventory i LEFT JOIN rental r ON r.inventory_id = i.inventory_id WHERE r.rental_id IS NULL;"
mysql -t -u root sakila -e "SELECT i.inventory_id, i.film_id FROM inventory i LEFT JOIN rental r ON r.inventory_id = i.inventory_id WHERE r.rental_id IS NULL ORDER BY i.inventory_id;"

+-------------------+
| jumlah_inventaris |
+-------------------+
|              4581 |
+-------------------+
+------------+
| tanpa_sewa |
+------------+
|          1 |
+------------+
+--------------+---------+
| inventory_id | film_id |
+--------------+---------+
|            5 |       1 |
+--------------+---------+

Keluaran menyatakan 4581 item tersedia dan hanya satu yang tidak pernah disewa, yaitu inventory_id 5 dengan film_id 1. Kolom yang diuji dengan IS NULL harus kolom yang tidak pernah NULL pada tabel kanan. Kolom rental_id adalah primary key, sehingga menjadi pilihan yang aman untuk pola ini.

Kondisi kanan: ON atau WHERE#

Pada INNER JOIN, letak kondisi tidak mengubah hasil. Pada LEFT JOIN, letaknya mengubah makna, karena kondisi pada ON menentukan pasangan yang boleh terbentuk, sedangkan kondisi pada WHERE menyaring baris hasil setelah NULL terbentuk. Syarat yang menyebut kolom tabel kanan pada WHERE akan menghilangkan seluruh baris ber-NULL, sehingga LEFT JOIN berperilaku seperti INNER JOIN.

Query berikut menghitung pasangan film berperingkat G dengan dua letak kondisi yang berbeda.

# kondisi rating G, sekali di ON dan sekali di WHERE
mysql -t -u root sakila -e "SELECT COUNT(*) AS kondisi_di_on FROM language l LEFT JOIN film f ON f.language_id = l.language_id AND f.rating = 'G';"
mysql -t -u root sakila -e "SELECT COUNT(*) AS kondisi_di_where FROM language l LEFT JOIN film f ON f.language_id = l.language_id WHERE f.rating = 'G';"

+---------------+
| kondisi_di_on |
+---------------+
|           183 |
+---------------+
+------------------+
| kondisi_di_where |
+------------------+
|              178 |
+------------------+

Versi ON menghasilkan 183 baris, yaitu 178 pasangan film G ditambah lima bahasa tanpa pasangan yang tetap ditahan. Versi WHERE menghasilkan 178 baris karena seluruh baris ber-NULL tersaring dan lima bahasa tersebut hilang. Keduanya sah secara sintaks, tetapi menyatakan pertanyaan yang berbeda, sehingga letak kondisi harus dipilih sesuai maksud analisis.

Kesalahan umum#

Kesalahan pertama adalah menguji NULL dengan = NULL. Perbandingan terhadap NULL tidak pernah menghasilkan benar, sehingga anti-join yang ditulis dengan WHERE r.rental_id = NULL mengembalikan hasil kosong tanpa pesan error. Ujiannya wajib memakai IS NULL, dan kesalahan ini tidak terdeteksi oleh MySQL karena secara sintaks querynya sah.

Kesalahan kedua adalah menjadikan COUNT(*) sebagai jumlah pasangan pada agregasi LEFT JOIN. Angkanya minimal satu untuk setiap baris kiri, termasuk kelompok yang tidak punya pasangan. Gunakan COUNT(kolom_kanan) agar kelompok tanpa pasangan terbaca nol. Kesalahan ketiga, menaruh syarat pada WHERE yang menyebut kolom kanan, telah diperlihatkan pada bagian sebelumnya sebagai penyebab hilangnya baris ber-NULL.

Ringkasan#

• LEFT JOIN menahan seluruh baris tabel kiri, dan baris tanpa pasangan ditampilkan dengan kolom kanan NULL.
• COUNT(kolom) menghitung pasangan yang sesungguhnya, sedangkan COUNT(*) menghitung baris hasil termasuk yang ber-NULL.
• Pola LEFT JOIN ditambah IS NULL pada kolom kanan yang tidak pernah NULL menemukan baris yang absen, yaitu anti-join.
• Syarat pada WHERE yang menyebut kolom kanan menyaring baris ber-NULL dan mengubah makna LEFT JOIN.

Referensi#

• JOIN Clause — MySQL 8.0 Reference Manual — Definisi resmi LEFT JOIN dan RIGHT JOIN pada manual MySQL 8.0.
• Outer Join Simplification — MySQL 8.0 Reference Manual — Perilaku optimizer yang mengubah OUTER JOIN menjadi INNER JOIN bila kondisi WHERE menyingkirkan NULL.