Fundamental SQL using INNER JOIN and UNION
Halo guys!!! Kali ini aku akan berbagi ilmu tentang INNER JOIN dan UNION pada SQL. Yuk langsung aja simak materinya!!!
Penggabungan Tabel dari Relasi Kolom
Sebagai data scientist, mungkin kita akan mengalami kesulitan seperti menggabungkan beberapa tabel menjadi sebuah tabel pada suatu database. Sebagai contoh, kita ingin menggabungkan tabel data penjualan dengan tabel yang berisi detail produk, mekanisme penggabungan ini dinamakan JOIN. Dengan JOIN, beberapa tabel pada suatu database bisa digabungkan satu sama lain. Kemampuan inilah sebenarnya ciri khas dan kekuatan yang membedakan RDMS dengan sistem database lain. Mari kita coba praktikkan penggunaannya lebih konkrit dengan dua contoh tabel sederhana, yaitu ms_item_warna dan ms_item_category, sehingga kita dapat dengan mudah memahami penggunaan JOIN.
Tabel pertama yang kita gunakan adalah tabel ms_item_warna seperti yang terlihat berikut ini.
Tabel ini terdiri dari dua kolom yang berisi informasi nama buah atau sayuran dengan warnanya, dan memiliki delapan baris data. Selanjutnya, jika permasalahan kita adalah ingin mengetahui informasi tidak hanya mengenai warna tetapi juga jenis/kategori dari setiap nama barang apakah tergolong sayuran atau buah, maka kita tidak dapat memperoleh informasi tersebut jika hanya berdasarkan tablel ms_item_warna. Oleh karena itu, kita perlu menghubungkan tabel tersebut dengan tabel lain yang berisi informasi tersebut, yaitu tabel ms_item_kategori seperti yang terlihat berikut ini.
Nah, sekarang kita telah memiliki tabel kedua yaitu tabel ms_item_kategori yang memiliki delapan baris data dan berisi informasi mengenai nama tumbuhan dengan kategorinya. Sama seperti permasalahan di tabel sebelumnya, jika kita hanya memiliki tabel ms_item_kategori maka kita tidak dapat memperoleh informasi mengenai nama dan warna tumbuhan. Oleh karena itu, kita bisa menggabungkan kedua tabel tersebut menjadi satu tabel baru yang berisi informasi lengkap mengenai nama, warna dan kategori setiap barang/item.
Hal pertama yang perlu diperhatikan sebelum menggabungkan kedua tabel adalah mengidentifikasi kolom mana dari kedua tabel yang memiliki kecocokan atau memiliki informasi yang sama. Dari kedua tabel di atas (ms_item_warna dan ms_item_kategori), kolom yang memiliki kesamaan/ kecocokan data adalah kolom nama_barang (ms_item_warna) dan nama_item (ms_item_kategori) yang selanjutnya disebut sebagai kolom kunci atau key columns.
Menggabungkan Tabel dengan Key Columns
Saatnya mempraktekkan penggabungan tabel ms_item_kategori dan ms_item_warna menggunakan key columns. Coba tuliskan query berikut.
Jika dieksekusi akan menampilkan output berikut.
Dapat dilihat hasil dari penggabungan dua tabel tersebut yaitu berupa tabel baru dengan empat kolom dan enam baris data. Perlu diketahui bahwa penggabungan ini bersifat sementara artinya tabel asli di database tidak mengalami perubahan, dan tabel baru hasil penggabungan ini juga tidak serta merta tersimpan di database.
Penggabungan dua tabel menjadi satu tabel baru menggunakan query SELECT pada contoh di atas dilakukan dengan cara berikut:
- Menuliskan dua nama tabel yang akan digunakan dengan dipisahkan operator koma (,).
- Menuliskan pasangan key columns dengan penghubung operator sama dengan (=) di bagian filter atau kondisi.
Berikut adalah gambaran detail sintaks penggunaan cara di atas:
Cara join seperti ini disebut dengan cara join menggunakan operator koma.
Jika kita amati, penggabungan tabel menghasilkan enam baris data, sedangkan di kedua tabel asal masing-masing memiliki delapan baris data. Lalu, kenapa bisa berkurang dua baris data? Untuk menjawab pertanyaan tersebut, cobalah perhatikan gambar berikut:
Dari tabel di atas terlihat bahwa ada beberapa data pada key columns (nama_barang dan nama_item) yang tidak sama di kedua tabel. Tabel hasil penggabungan hanya akan memunculkan baris data yang isi datanya terdapat di kedua key columns, dan data yang isinya cocok untuk kedua tabel jumlahnya hanya enam dari total delapan baris data yang ada. Sedangkan baris data yang berisi belimbing, jamur, apel dan daun bawang, tidak terdapat di kedua tabel, sehingga baris data ini akan di-exclude dan tidak akan muncul di tabel hasil penggabungan.
Panah berwarna merah menunjukkan proses pencocokan dan penggabungan data. Sebagai contoh: Baris data pertama dengan isi “bayam” pada key column nama_item (tabel ms_item_kategori), akan mencari isi yang sama di key column nama_barang (tabel ms_item_warna), dan ditemukan pada baris kedua kemudian dihubungkan menjadi satu baris di tabel baru hasil penggabungan. Pencocokan dengan pencarian ini berdasarkan bagian kondisi (conditional clause) pada query yang ditandai dengan warna kuning berikut.
Setelah seluruh data ditemukan dengan kondisi ini, dan jika tidak menentukan spesifik kolom maka seluruh kolom data dari kedua tabel akan dimunculkan dengan isi/baris data yang sama dan terdapat di kedua key column digabungkan menjadi satu baris pada tabel baru.
Isi data yang ditandai dengan warna kuning adalah data yang tidak memiliki pasangan sehingga tidak bisa digabungkan, dan dengan demikian tidak memiliki data gabungan pada hasil akhir. Cara pencocokan dan penggabungan disebut dengan INNER JOIN, dimana isi dari key column kedua tabel harus cocok satu sama lain baru dapat digabungkan. Jika kita biasa bekerja dengan Excel, proses ini hampir menyerupai penggunaan fungsi vlookup.
Jika akan mengubah urutan tabel di bagian FROM pada query inner join, maka hanya urutan kolom saja yang berubah tetapi isi data dan jumlah data tidak berubah.
dan akan menampilkan output:
Terlihat jumlah data yang dihasilkan tetap 6 baris data, namun dengan urutan kolom yang berbeda, dimana dua kolom pertama adalah dari tabel ms_item_warna dan dua kolom berikutnya dari tabel ms_item_barang. Hal ini sesuai dengan urutan nama tabel yang diketikkan setelah FROM.
Menggunakan Prefix Nama Tabel
Seperti yang dijelaskan sebelumnya, jika akan menggunakan wildcard (*), dan tidak menentukan spesifik nama kolom yang akan dimunculkan di bagian SELECT, maka secara default urutan kolom dimulai dengan kolom dari tabel yang dinyatakan pertama di bagian FROM. Akan tetapi, bisa juga memanfaatkan wildcard dengan menambahkan prefix nama tabel, dimana dengan merinci prefix nama tabel ini, dimungkinkan untuk menentukan urutan kolom dari tabel mana yang muncul duluan.
Sebagai contoh, jika akan menggabungkan kedua tabel, dengan menyatakan tabel ms_item_warna terlebih dahulu di bagian FROM, tetapi yang ingin dimunculkan kolomnya di awal hasil adalah dari yang ms_item_kategori, maka query-nya akan menjadi sebagai berikut.
Jika dieksekusi akan menghasilkan output berikut.
Terlihat urutan kolom yang muncul adalah dari tabel ms_item_kategori terlebih dahulu.
Penggabungan Tanpa Kondisi
Penjelasan dan praktek yang kita dilakukan pada materi sebelumnya adalah penggabungan dua tabel dengan menggunakan kondisi, yaitu terdapat data yang sama pada key kolom dari kedua tabel. Akan tetapi, dalam beberapa case di real problem, sering kali terdapat permasalahan tertentu dimana kita ingin menggabungkan tabel tanpa ada kondisi. Proses penggabungan ini juga dapat dilakukan dengan metode koma dan tanpa menggunakan kondisi relasi antar kolom.
Terlihat pada query kita hanya menyertakan nama dua tabel yang akan diambil datanya, tapi tidak ada informasi kondisi bagaimana kedua tabel tersebut berelasi satu dengan yang lainnya melalui key column. Lalu apa hasilnya?
+-----------+----------+-------------+--------------+
| nama_item | kategori | nama_barang | warna |
+-----------+----------+-------------+--------------+
| bayam | sayuran | apel | merah |
| belimbing | buah | apel | merah |
| duku | buah | apel | merah |
| durian | buah | apel | merah |
| gandum | buah | apel | merah |
| jamur | sayuran | apel | merah |
| jambu air | buah | apel | merah |
| jeruk | buah | apel | merah |
| bayam | sayuran | bayam | hijau |
| belimbing | buah | bayam | hijau |
| duku | buah | bayam | hijau |
| durian | buah | bayam | hijau |
| gandum | buah | bayam | hijau |
| jamur | sayuran | bayam | hijau |
| jambu air | buah | bayam | hijau |
| jeruk | buah | bayam | hijau |
| bayam | sayuran | daun bawang | hijau |
| belimbing | buah | daun bawang | hijau |
| duku | buah | daun bawang | hijau |
| durian | buah | daun bawang | hijau |
| gandum | buah | daun bawang | hijau |
| jamur | sayuran | daun bawang | hijau |
| jambu air | buah | daun bawang | hijau |
| jeruk | buah | daun bawang | hijau |
| bayam | sayuran | duku | kuning pekat |
| belimbing | buah | duku | kuning pekat |
| duku | buah | duku | kuning pekat |
| durian | buah | duku | kuning pekat |
| gandum | buah | duku | kuning pekat |
| jamur | sayuran | duku | kuning pekat |
| jambu air | buah | duku | kuning pekat |
| jeruk | buah | duku | kuning pekat |
| bayam | sayuran | durian | kuning |
| belimbing | buah | durian | kuning |
| duku | buah | durian | kuning |
| durian | buah | durian | kuning |
| gandum | buah | durian | kuning |
| jamur | sayuran | durian | kuning |
| jambu air | buah | durian | kuning |
| jeruk | buah | durian | kuning |
| bayam | sayuran | gandum | coklat |
| belimbing | buah | gandum | coklat |
| duku | buah | gandum | coklat |
| durian | buah | gandum | coklat |
| gandum | buah | gandum | coklat |
| jamur | sayuran | gandum | coklat |
| jambu air | buah | gandum | coklat |
| jeruk | buah | gandum | coklat |
| bayam | sayuran | jambu air | merah |
| belimbing | buah | jambu air | merah |
| duku | buah | jambu air | merah |
| durian | buah | jambu air | merah |
| gandum | buah | jambu air | merah |
| jamur | sayuran | jambu air | merah |
| jambu air | buah | jambu air | merah |
| jeruk | buah | jambu air | merah |
| bayam | sayuran | jeruk | oranye |
| belimbing | buah | jeruk | oranye |
| duku | buah | jeruk | oranye |
| durian | buah | jeruk | oranye |
| gandum | buah | jeruk | oranye |
| jamur | sayuran | jeruk | oranye |
| jambu air | buah | jeruk | oranye |
| jeruk | buah | jeruk | oranye |
+-----------+----------+-------------+--------------+Terlihat banyak sekali hasil yang keluar, ini dikarenakan setiap baris data pada kedua tabel akan dihubungkan satu sama lain, tanpa ada hubungan. Jumlah enam puluh empat baris data ini adalah hasil perkalian dari jumlah data dari kedua tabel, dimana masing-masing memiliki delapan baris data. Cara menggabungkan kedua tabel seperti ini disebut dengan mekanisme cross join.
INNER JOIN
Pada subbab sebelumnya kita sudah mempelajari mengenai konsep dan penggunaan inner join menggunakan operator koma dengan klausa FROM …WHERE … pada perintah SELECT untuk menggabungkan dua tabel menjadi satu sumber data baru. Selanjutnya kita akan belajar bagaimana menggabungkan dua tabel dengan metode INNER JOIN lain, yaitu dengan keyword INNER JOIN yang memberikan hasil yang sama, tetapi dengan konstruksi atau sintaks yang sedikit berbeda. Konstruksi baru ini akan memberi manfaat lebih ketika pada suatu query melibatkan beberapa join sehingga penggunaan operator koma menjadi sangat terbatas.
Untuk penjelasan mengenai materi JOIN dengan penggunaan keyword INNER JOIN, kita akan kembali menggunakan dua contoh tabel sederhana, yaitu ms_item_warna dan ms_item_kategori, sehingga kita dapat dengan mudah memahami penggunaan keyword INNER JOIN untuk menggabungkan dua tabel tersebut.
Tujuan JOIN adalah menggabungkan dua atau lebih tabel yang berbeda di dalam database.
Nah, JOIN ini hanya bisa dilakukan apabila tabel-tabel tersebut memiliki key kolom yang sama. Di SQL, terdapat beberapa tipe JOIN yaitu INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, dan SELF JOIN. Setiap tipe join memiliki tujuan dan hasil query yang berbeda. Pada metode INNER JOIN, baris dari kedua atau lebih tabel akan dibandingkan untuk mengecek baris-baris mana saja yang cocok satu sama lain berdasarkan kondisi JOIN yang ditentukan. Berikut adalah sintaks inner join:
Penggunaan prefix pada tabel_1.nama_kolom dan tabel_2.nama_kolom ditujukan agar terdapat kejelasan (clarity) kolom pada tabel mana yang dijadikan acuan dalam proses INNER JOIN. Tentunya, penggunaan prefix ini ini sangat bermanfaat jika ingin menggabungkan beberapa tabel. Jika masing-masing tabel tersebut dianalogikan sebagai dua himpunan maka proses INNER JOIN ekivalen dengan INTERSECTION (IRISAN) antara dua himpunan.
Proses INNER JOIN memiliki konsep penggabungan untuk records pada kedua tabel yang bernilai sama.
Selanjutnya, kita akan menggunakan klausa INNER JOIN … ON …; untuk menggabungkan tabel ms_item_warna dan ms_item_kategori berdasarkan sintaks INNER JOIN yang telah dijelaskan sebelumnya.
Jika dieksekusi akan menampilkan output berikut.
Hasil yang diperoleh dengan penggunaan SELECT … FROM … INNER JOIN … ON …; adalah sama dengan penerapan SELECT … FROM … WHERE …;.
Tabel tr_penjualan dan Tabel ms_produk
Setelah kita menggunakan tabel ms_item_warna dan ms_item_kategori, sekarang mari kita gunakan tabel tr_penjualan dan tabel ms_produk yang ada di-database, sehingga kita bisa lebih paham mengenai penerapan konsep JOIN di real case.
Jika dieksekusi akan menampilkan output berikut.
Oke, sekarang kita praktikkan pada penggabungan tabel tr_penjualan dan ms_produk dan menampilkan seluruh kolom dari kedua tabel.
Jika dieksekusi akan menampilkan output berikut.
Sekarang perhatikan lebih lanjut dan bandingkan antara kolom kode_produk pada hasil query dengan INNER JOIN di atas dengan kolom kode_produk pada tabel ms_produk. Pada tabel hasil query , tidak ditemukan prod-06, padahal di tabel ms_produk terdapat data prod-06 yaitu pulpen multifunction + laser DQLAB. Dan tidak ada transaksi dengan kode_produksi prod-06 di tabel tr_penjualan.
INNER JOIN akan mencocokkan key kolom tabel tr_penjualan dengan key kolom ms_produk, jika value dari key kolom sama-sama ada di kedua tabel , maka baris itu akan dikembalikan sebagai hasil query dan membentuk satu tabel yang berisi seluruh kolom dari kedua tabel, sedangkan jika value key kolom hanya terdapat di satu kolom, maka baris ini tidak akan ditampilkan. prod-06 ada di tabel di ms_produk tetapi tidak ada di data penjualan sehingga saat penggabungan tabel dengan INNER JOIN, data prod-06 dari tabel ms_produk tidak akan muncul di hasil query.
Memilih Beberapa Kolom untuk Ditampilkan
Sementara itu, jika kita ingin menampilkan beberapa kolom saja dari tabel hasil penggabungan, maka sintaks SQL dari INNER JOIN dinyatakan sebagai berikut ini.
Bagian query
SELECT tabel1.nama_kolom1, tabel1.nama_kolom2, ..., tabel2.nama_kolom2, ....
FROM tabel1menghendaki pemilihan kolom mana saja dari kedua tabel yang akan digabungkan. Disini diperlukan penggunaan prefix nama tabelnya. Selanjutnya, bagian query
INNER JOIN tabel2digunakan untuk menggabungkan tabel1 dengan tabel2. Akhirnya, bagian query
ON tabel_1.nama_kolom1 = tabel2.nama_kolom1;adalah acuan penggabungan tabel1 dan tabel2 berdasarkan kolom yang memiliki tingkat kecocokan yang tinggi. Kedua kolom dengan tingkat kecocokan yang tinggi pada masing-masing tabel disebut juga dengan key column.
Catatan: Perlu diperhatikan jika menampilkan kolom dengan nama yang sama di kedua tabel, maka pada bagian Select, tidak bisa hanya mengetikkan nama kolom saja, tetapi juga harus didahului oleh prefix nama tabel dimana kolom itu berasal untuk menghindari error karena ambiguitas.
Sekarang kita akan mempraktikkan untuk menggabungkan tabel tr_penjualan dan ms_produk dengan kolom yang ditampilkan dari tabel tr_penjualan adalah kode_transaksi, kode_pelanggan, kode_produk, dan qty. Untuk tabel ms_produk tampilkan kolom nama_produk dan harga. Bentuk kolom total yang merupakan hasil perkalian setiap baris pada kolom harga di tabel ms_produk dengan kolom qty di tabel tr_penjualan. Tabel hasil penggabungan haruslah membentuk kolom-kolom dengan urutannya adalah kode_transaksi, kode_pelanggan, kode_produk, nama_produk, harga, qty, dan total.
Jika dieksekusi akan menampilkan output berikut.
UNION
Pada materi ini, kita akan menggunakan data berupa analisis hasil penjualan suatu store. Akan tetapi ketika kita coba cek di database, terdapat 2 tabel penjualan, yaitu:
- Tabel A berisi data transaksi untuk kode transaksi ‘tr-001’ sampai ‘tr-003’, dan
- Tabel B berisi data data transaksi untuk kode transaksi ‘tr-004’ sampai ‘tr-006’.
Untuk kasus seperti ini kita perlu menggunakan metode UNION agar kedua tabel di atas menjadi satu tabel.
UNION adalah operator SQL yang digunakan untuk menggabungkan hasil dari 2 atau lebih SELECT statement secara “Vertikal”.
dengan catatan,
- Setiap hasil dari SELECT statement yang akan digabungkan (UNION) memiliki jumlah kolom yang sama.
- Kolom tersebut juga harus memiliki tipe data yang sama.
- Kolom tersebut memiliki urutan posisi yang sama.
Berikut format sintaks-nya:
Sekarang kita SELECT seluruh kolom pada tabel_A.
Selanjutnya kita SELECT seluruh kolom pada tabel_B.
Sesuai dengan syarat untuk penggabungan dengan UNION yang telah dijelaskan tadi bahwa:
- Jumlah kolom tabel_A dan tabel_B adalah sama.
- Kolom-kolom pada tabel_A dan tabel_B memiliki tipe data yang sama.
- Kolom-kolom pada tabel_A dan tabel_B memiliki urutan posisi yang sama.
Melalui pengecekan pada tabel_A dan tabel_B pastikan bahwa ketiga syarat penggabungan dengan UNION yang dinyatakan di atas terpenuhi. Langkah ini kita lakukan sebelum melanjutkan pada praktek berikutnya menggunakan UNION.
Kedua tabel_A dan tabel_B sudah memiliki jumlah kolom yang sama, dan juga urutan posisi kolom juga sama, jadi bisa langsung menggabungkan kedua kolom tersebut dengan menambahkan UNION. Sekarang ketikkan kode berikut.
Jika dieksekusi akan menampilkan output berikut.
Menggunakan UNION dengan Klausa WHERE
Jika ada kondisi WHERE, sintaks-nya bagaimana? Misalnya kita hanya ingin menggabungkan tabel yang isinya data penjualan untuk kode produk prod-04 saja.
Jika dieksekusi akan menampilkan output berikut.
Menyelaraskan (Conforming) Kolom
Bagaimana kalau posisi kolom dari kedua tabelnya tidak sama? Apa tidak bisa di-UNION-kan? Tentu saja bisa, kita bisa menyelaraskan kolom dari kedua tabel di SELECT-statement. Mari kita contohkan dengan data dari tabel berikut ini.
tabel Customers
dan tabel Supplier
Jumlah kolom dari kedua tabel tersebut sama-sama 7 kolom, tetapi kolom posisi kolom ContactName dari kedua tabel tidak sama. Di tabel Customer, posisi kolom ContactName berada di Kolom ke-3 sedangkan di tabel supplier berada di kolom ke-2. Jika langsung menggabungkan keduanya, tanpa menyelaraskan kolom hasilnya akan sebagai berikut:
Tentunya, ini hasil UNION yang tidak diinginkan, oleh karena itu, urutkan posisi kolom tersebut di SELECT-statement dan juga pilih kolom yang ingin digabungkan, sehingga tidak perlu semua kolom dari kedua tabel di-UNION-kan, seperti berikut ini:
Jika terdapat perbedaan nama kolom antara SELECT-statement pertama dan SELECT-statement kedua, maka secara default akan digunakan nama kolom dari SELECT-statement yang pertama.
Perbedaan antara UNION dan JOIN
Sebenarnya kalau dipikir-pikir fungsi UNION dan JOIN tampak mirip yaitu menggabungkan dua atau lebih tabel. Tapi yang membedakan adalah bagaimana tabel-tabel itu digabungkan. Kita menggunakan JOIN ketika akan menggabungkan tabel secara horizontal, sehingga hasil join akan memuat kolom-kolom dari kedua atau lebih tabel yang digabungkan. Berikut gambaran penggabungan tabel dengan metode JOIN.
Pada metode JOIN, penggabungan dilakukan berdasarkan key/kolom tertentu yang terdapat di tabel-tabel yang akan digabungkan dan key/kolom ini memiliki nilai yang saling terkait. Seperti yang terlihat pada gambar, Kolom A dan Kolom E merupakan key/kolom yang saling terkait sehingga kedua tabel dapat digabungkan dengan mencocokan nilai dari kedua kolom ini. Proses JOIN tidak dapat dilakukan jika tidak terdapat key/kolom yang saling terkait di kedua atau lebih tabel yang akan digabungkan.
Untuk UNION seperti yang sudah dijelaskan, digunakan ketika ingin menggabungkan tabel secara secara vertikal yaitu menggabungkan baris/row dari dua atau lebih tabel. Tidak seperti JOIN, untuk penggabungan dengan UNION, tidak diperlukan key/kolom yang saling terkait tetapi UNION mensyaratkan bahwa jumlah kolom dari tabel-tabel yang akan digabungkankan adalah sama dan berada diposisi yang sama pula. Berikut ilustrasi penggabungan dengan UNION:
Pada proses penggabungan UNION, tidak terdapat penambahan kolom tetapi jumlah baris/rows yang akan bertambah.
Nah, materi di atas adalah materi tentang INNER JOIN dan UNION pada SQL yang sudah aku rangkum. Sebenarnya masih ada lagi materi tentang SQL yang bakal aku share di postingan selanjutnya. Stay tune!!!
Reference:
Modul DQLab : Fundamental SQL using INNER JOIN and UNION
