Cara menggunakan cross join unnest mysql

Hello my friends! Welcome to my Medium! Introduce me Anjar Desmiarti. On this occasion, my post is in the form of “Data Analyst Career Track Series” from DQLAB about “Fundamental SQL Using INNER JOIN and UNION”. Mentor data on this topic is Trisna Yulia Junita a Data Scientist in PT. BUMA

Table of Contents Show

• 1. Penggabungan Tabel dari Relasi Kolom
• Tabel Pertama
• Tabel Kedua
• Key Columns
• Menggabungkan Tabel dengan Key Columns
• Query Penggabungan Tabel
• Hasil Penggabungan Tabel
• Menggunakan Prefix Nama Tabel
• Penggabungan Tanpa Kondisi
• 2. Syntax INNER JOIN
• Menggunakan INNER JOIN
• Penggabungan hasil SELECT secara “Vertikal”
• Menyelaraskan (Conforming) Kolom
• Menggunakan UNION dan Menyelaraskan Kolom-Kolomnya
• Perbedaan antara UNION dan JOIN

1. Penggabungan Tabel dari Relasi Kolom

Tabel Pertama

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.

Tabel Kedua

Tabel ini terdiri dari dua kolom yang berisi informasi nama tumbuhan dengan kategorinya, dan memiliki delapan baris data. 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.

Key Columns

Penjelasan selanjutnya adalah mengenai key columns dan bagaimana penggunaannya dalam menggabungkan kedua tabel. Perhatikan kembali tabel ms_item_warna dan ms_item_kategori yang telah ditemui pada pembahasan sebelumnya.

Menggabungkan Tabel dengan Key Columns

Saatnya mempraktekkan penggabungan tabel ms_item_kategori dan ms_item_warna menggunakan key columns.

Cobalah tuliskan query berikut di code editor:

SELECT * FROM ms_item_kategori, ms_item_warna 
WHERE nama_barang = nama_item;

Query Penggabungan Tabel

Penggabungan dua tabel menjadi satu tabel baru menggunakan query SELECT pada subbab sebelumnya 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 detil syntax penggunaan cara di atas:

Hasil Penggabungan Tabel

Jika diamati, penggabungan tabel menghasilkan enam baris data. Sedangkan di kedua tabel asal masing-masing memiliki delapan baris data. Lalu, kenapa bisa berkurang dua baris data?

Dua tabel di bagian atas yaitu tabel ms_item_kategori dan tabel ms_item_warna merupakan tabel sumber data, dan bagian bawah adalah tabel hasil penggabungan dari kedua tabel tersebut.

Dari tabel hasil penggabungan terlihat bahwa baris data yang muncul hanyalah 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 magenta 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 merah 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.

Menggunakan Prefix Nama Tabel

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 querynya akan menjadi sebagai berikut.

SELECT ms_item_kategori.*, ms_item_warna.*
FROM ms_item_warna, ms_item_kategori
WHERE nama_barang = nama_item;

Penggabungan Tanpa Kondisi

SELECT * FROM ms_item_kategori, ms_item_warna;

Output:

+-----------+----------+-------------+--------------+
| 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 tabelseperti ini disebut dengan mekanisme cross join.

2. Syntax INNER JOIN

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.

Syntax 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. Untuk penjelasan lebih detail, coba ingat kembali penggunaan prefix dan alias, yang telah dipelajari pada modul “Fundamental SQL with SELECT statement chapter Prefix dan Alias”. Jika masing-masing tabel tersebut dianalogikan sebagai dua himpunan maka proses INNER JOIN ekivalen dengan INTERSECTION (IRISAN) antara dua himpunan.

Menggunakan INNER JOIN

SELECT * FROM ms_item_warna
INNER JOIN ms_item_kategori
ON ms_item_warna.nama_barang = ms_item_kategori.nama_item;

SELECT * FROM tr_penjualan;
SELECT * FROM ms_produk;

menggabungkan tabel tr_penjualan dan ms_produk dan menampilkan seluruh kolom dari kedua tabel

SELECT * FROM tr_penjualan
INNER JOIN ms_produk
ON tr_penjualan.kode_produk = ms_produk.kode_produk;

Apakah perbedaan antara tabel sebelum penggabungan dengan INNER JOIN dan setelah penggabungan?

Sekarang seluruh kolom dari kedua tabel berada di satu tabel dan berisi seluruh kolom — kolom dari kedua tabel. Jadi, kita juga dapat dengan mudah mengidentifikasi nama produk dari setiap kode produk yang dibeli oleh customer. 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.

Menurut kalian apa yang berbeda?

Tidak ada transaksi dengan kode_produksi prod-06 di tabel tr_penjualan. Sepertinya sudah mulai paham. 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.

SELECT tr_penjualan.kode_transaksi, tr_penjualan.kode_pelanggan, tr_penjualan.kode_produk, ms_produk.nama_produk, ms_produk.harga, tr_penjualan.qty,
ms_produk.harga*tr_penjualan.qty AS total
FROM tr_penjualan
INNER JOIN ms_produk
ON tr_penjualan.kode_produk = ms_produk.kode_produk;

3. UNION

Penggabungan hasil SELECT secara “Vertikal”

UNION adalah operator SQL yang digunakan untuk menggabungkan hasil dari 2 atau lebih SELECT — statement secara “Vertikal” dengan ketentuan:

• Setiap hasil dari SELECT statement yang akan digabungkan (UNION) memiliki jumlah kolom yang sama
• Kolom tersebut juga harus memiliki tipe data yang sama, dan
• Kolom tersebut memiliki urutan posisi yang sama.

Berikut format syntax-nya:

Pertama — tama mari kita SELECT seluruh kolom dari tabel_A. Selanjutnya kita SELECT seluruh kolom dari tabel_B.

SELECT * FROM tabel_A;
SELECT * FROM tabel_B;

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.

SELECT * FROM tabel_A
UNION
SELECT * FROM tabel_B;

Menggunakan UNION dengan klausa WHERE

SELECT * FROM tabel_A
WHERE kode_pelanggan = ‘dqlabcust03’
UNION
SELECT * FROM tabel_B
WHERE kode_pelanggan = ‘dqlabcust03’;

Menyelaraskan (Conforming) Kolom

Kali ini, kita masuk ke tahap conforming. Kebetulan data penjualan ini berada di kedua tabel A & B jumlah kolom dan posisinya sama serta nama kolomnya sama. Bagaimana kalau posisi kolom dari kedua tabelnya tidak sama? Apa tidak bisa di-UNION-kan?

Tentu saja bisa, kamu 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 :

SELECT CustomerName, ContactName, City, PostalCode 
FROM Customers 
UNION 
SELECT SupplierName, ContactName, City, PostalCode 
FROM Suppliers;

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.

Menggunakan UNION dan Menyelaraskan Kolom-Kolomnya

Selanjutnya, kita akan menggabungkan antara nama customer, nama panggilan, kota dan kode pos dengan syntax SELECT…. FROM… dan UNION

SELECT CustomerName, ContactName, City, PostalCode
FROM Customers
UNION
SELECT SupplierName, ContactName, City, PostalCode
FROM Suppliers;

Perbedaan antara UNION dan JOIN

Memang benar UNION dan JOIN digunakan untuk menggabungkan data dari 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:

The main points of this session:
Merging of two tables using WHERE clause and cross join technique. Merging two tables using INNER JOIN. Joining two tables vertically using UNION.

Thank you (❁´◡`❁)