Join SQL
Dalam praktik di dunia nyata, data yang dibutuhkan seringkali berasal dari berbagai tabel sumber. Pada tulisan ini, saya akan membahas tentang JOIN yang merupakan cara untuk menggabungkan satu atau lebih tabel dengan kesamaan kolom. PostgreSQL mendukung kita untuk melakukan beberapa operasi seperti INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN, dan jenis khusus yang disebut SELF-JOIN.
INNER JOIN
Operasi INNER JOIN akan memberikan hasil berupa baris yang memiliki kesamaan nilai pada kolom di setiap tabelnya. Berikut sintaksis penggunaan INNER JOIN:
SELECT
daftar_kolom
FROM
tabel1 t1
INNER JOIN tabel2 t2
ON
t1.nama_kolom = t2.nama_kolom
Jika kedua nama kolom baik pada tabel 1 maupun tabel 2 bernilai sama, maka kamu bisa menggunakan kata kunci USING dengan sintaksis berikut:
SELECT
daftar_kolom
FROM
tabel1 t1
INNER JOIN tabel2 t2
USING(nama_kolom)
Untuk setipa baris di tabel1, inner join akan membandingkan nilai pada nama_kolom dengan nilai di setiap baris pada tabel2. Jika nilai di kedua tabel bernilai sama, inner join akan membuat satu baris baru termasuk semua kolom dari kedua tabel dan menaruhnya di hasil kueri. Sebaliknya, jika nilai kolom di kedua tabel memiliki nilai yang berbeda maka pasangan baris dari kedua tabel tidak akan diproses. Proses tersebut diulangi ke baris berikutnya sehingga semua baris pada tabel selesai dicocokkan.
INNER JOIN untuk menggabungkan dua tabel
Pada contoh kali ini, saya akan menggabungkan dua tabel dari database dvdrental yakni tabel film_actor dan tabel actor dengan kolom sebagai berikut:
table_name|column_name|
----------+-----------+
film_actor|actor_id |
film_actor|film_id |
film_actor|last_update|
table_name|column_name|
----------+-----------+
actor |actor_id |
actor |last_update|
actor |first_name |
actor |last_name |
dari kedua tabel tersebut terdapat kesamaan kolom actor_id. Dengan melakukan operasi inner join, kita dapat mengetahui nama aktor yang bermain pada suatu film. Berikut contoh kueri untuk mendapatkan daftar nama aktor yang bermain pada film dengan id tertentu.
SELECT
fa.film_id ,
a.first_name || ' ' || a.last_name AS actor_name
FROM
film_actor fa
INNER JOIN actor a USING(actor_id)
WHERE
fa.film_id = 8
Berikut hasil kueri:
film_id|actor_name |
-------+-----------+
8|Fay Kilmer |
8|Gene Willis|
8|Susan Davis|
8|Lucille Dee|
INNER JOIN untuk menggabungkan tiga tabel
Kalau sebelumnya kita sudah berhasil mengetahui nama aktor yang bermain pada film tertentu. Selanjutnya saya akan tunjukkan cara menggabungkan tiga tabel untuk mengetahui judul film apa yang diperankan oleh nama aktornya. Konsepnya sama seperti sebelumnya, kamu cukup menambahkan tabel dan kolom yang menjadi kunci untuk melakukan join.
Berikut contoh kueri untuk mengetahui nama film, dan nama lengkap dari para aktornya. Pada kueri ini saya akan menggabungkan tabel film, film_actor dan actor.
SELECT
f.title ,
a.first_name || ' ' || a.last_name AS actor_name
FROM
film f
INNER JOIN film_actor fa
USING(film_id)
INNER JOIN actor a
USING(actor_id)
WHERE
fa.film_id = 8
ORDER BY 2
LEFT JOIN
Penggunaan LEFT JOIN akan menggabungkan tabel kiri dengan tabel kanan dan akan mengembalikkan baris dari tabel kiri yang mungkin punya atau tidak punya kesamaan nilai pada tabel kanan.
Berikut contoh kueri untuk menampilan daftar film yang stoknya sedang kosong di inventori. Saya akan melakukan LEFT JOIN terhadap tabel film dan inventory dengan join key berupa kolom film_id.
SELECT
f.film_id ,
f.title ,
i.inventory_id
FROM
film f
LEFT JOIN inventory i
USING(film_id)
WHERE
i.film_id IS NULL
ORDER BY
f.title;
hasil kueri:
film_id|title |inventory_id|
-------+----------------------+------------+
14|Alice Fantasia | [NULL]|
33|Apollo Teen | [NULL]|
36|Argonauts Town | [NULL]|
38|Ark Ridgemont | [NULL]|
41|Arsenic Independence | [NULL]|
87|Boondock Ballroom | [NULL]|
Pada kueri tersebut, akan dihasilkan film yang ada di tabel film terlepas dari kondisi apakah ada baris dengan nilai film_id yang sama dengan nilai film_id di tabel inventory.
RIGHT JOIN
Konsep yang dilakukan oleh RIGHT JOIN sebenarnya mirip dengan LEFT JOIN namun posisi tabelnya yang ditukar. Pada praktiknya fungsi ini lebih jarang digunakan dibanding dengan LEFT JOIN yang lebih umum.
Berikut contoh kueri untuk memberikan hasil yang sama dengan contoh kueri pada bagian LEFT JOIN sebelumnya.
SELECT
f.film_id ,
f.title ,
i.inventory_id
FROM
inventory i
RIGHT JOIN film f
USING(film_id)
WHERE
i.film_id IS NULL
ORDER BY
f.title;
SELF JOIN
Sesuai dengan namanya, self join merupakan istilah untuk melakukan proses join pada tabel yang sama. Kamu dapat menggunakaan nama alias yang berbeda pada tabel yang sama untuk melakukan self join. Self join biasanya digunakan untuk melakukan pengecekan kondisi pada tabel seperti mencari kondisi yang sama atau yang berhubungan dengan hirarki. Berikut contoh kueri untuk mencari film dengan durasi dan durasi peminjaman yang sama.
SELECT
f1.title,
f2.title ,
f1.length,
f1.rental_duration
FROM
film f1
INNER
JOIN film f2 ON
f1.film_id > f2.film_id -- agar dua judul berbeda bisa dibandingkan
AND f1.length = f2.length
AND f1.rental_duration = f2.rental_duration
hasil kueri:
title |title |length|rental_duration|
-----------------------+---------------------------+------+---------------+
Arabia Dogma |Airplane Sierra | 62| 6|
Dying Maker |Antitrust Tomatoes | 168| 5|
Baby Hall |Apollo Teen | 153| 5|
Banger Pinocchio |Attacks Hate | 113| 5|
Bilko Anonymous |Behavior Runaway | 100| 3|
Born Spinal |Anonymous Human | 179| 7|
FULL OUTER JOIN
Proses FULL OUTER JOIN akan menggabungkan dua tabel atau lebih dan akan menghasilkan semua baris dari semua tabel, baik yang memiliki kesesuaian nilai maupun yang tidak. Kata kunci OUTER bersifat opsional sehingga jenis join ini dikenal juga dengan FULL JOIN.
Sebagai contoh, saya akan melakukan operasi FULL OUTER JOIN menggunakan tabel dosen dan matakuliah.
(tabel dosen)
dosen_id|nama_dosen|mk_id |
--------+----------+------+
1|Diluc | 1|
2|Rosaria | 1|
3|Keqing | 2|
4|Zhongli | 3|
5|Sayu | 4|
6|Clorinde |[NULL]|
(tabel matakuliah)
mk_id|nama_mk |
-----+-----------------+
1|Database |
2|Algoritma |
3|Sistem Operasi |
4|Kecerdasan Buatan|
Berikut adalah contoh kueri FULL JOIN dari kedua tabel tersebut:
SELECT
nama_dosen,
nama_mk
FROM
dosen
FULL JOIN matakuliah
USING(mk_id);
Berikut hasil kuerinya:
nama_dosen|nama_mk |
----------+-----------------+
Diluc |Database |
Rosaria |Database |
Keqing |Algoritma |
Zhongli |Sistem Operasi |
Sayu |Kecerdasan Buatan|
Clorinde |[NULL] |
baris dengan nama_dosen bernilai Clorinde tetap ditampilkan pada hasil kueri meskipun baris tersebut tidak memiliki nilai pada kolom mk_id.
CROSS JOIN
Dalam PostgreSQL, CROSS JOIN memungkinkan kamu untuk menggabungkan dua tabel dengan mengkombinasikan setiap baris dari tabel pertama dengan setiap baris pada tabel kedua. Dalam teori himpunan, bisa dikatakan bahwa hasil dari CROSS JOIN adalah perkalian kartesian dari baris di kedua tabel.
Sebagai contoh, saya akan melakukan CROSS JOIN dari tabel dosen dan matakuliah yang sebelumnya digunakan sebagai contoh namun kali ini saya hanya akan mengambil masing-masing kolom nama_dosen dan nama_mk. Berikut contoh kueri untuk mendapatkan hasil tersebut:
SELECT
*
FROM
(
SELECT
nama_dosen
FROM
dosen) AS dosen
CROSS JOIN (
SELECT
nama_mk
FROM
matakuliah) AS matakuliah;
berikut cuplikan hasil kuerinya:
nama_dosen|nama_mk |
----------+-----------------+
Diluc |Database |
Diluc |Algoritma |
Diluc |Sistem Operasi |
Diluc |Kecerdasan Buatan|
Rosaria |Database |
Rosaria |Algoritma |
(24 Rows)
Karena terdapat 6 jenis nilai di nama_dosen dan 4 jenis nilai di nama_mk maka akan dihasilkan 6 * 4 = 24 baris pada hasil kueri.
NATURAL JOIN
Konsep NATURAL JOIN akan membuat join secara implisit berdasarkan kolom yang digunakan. Berikut contoh kueri NATURAL JOIN menggunakan tabel dosen dan matakuliah seperti sebelumnya:
SELECT
*
FROM
dosen
NATURAL JOIN matakuliah;
Hasil kueri:
mk_id |dosen_id|nama_dosen|nama_mk |
------+--------+----------+-----------------+
1| 1|Diluc |Database |
1| 2|Rosaria |Database |
2| 3|Keqing |Algoritma |
3| 4|Zhongli |Sistem Operasi |
4| 5|Sayu |Kecerdasan Buatan|
Kueri yang diberikan pada contoh di atas ekivalen dengan INNER JOIN dengan kolom mk_id sebagai key joinnya.