PHI's Corner

Business Intelligence (BI)

Training BI & SQL

Bahasa SQL‎ > ‎

Menggabungkan Table Transaksi dengan UNION

Pendahuluan

Di dalam berbagai kasus praktek di lapangan yang penulis temukan, ada kesalahan SQL umum yang dilakukan jika membaca diagram ERD (Entity Relationship Diagram) pada kasus relasi  "many to many" - seperti pada gambar di bawah ini.

Terlihat ada dua table transaksi - tr_penjualan dan target_penjualan - yang memiliki referensi table yang sama, yaitu ms_produk.


Gambar 1 : Hubungan Antar Table Referensi / Master dengan Table Transaksi

Kesalahan Umum : Penggunaan JOIN

Kesalahan umum yang terjadi biasanya adalah para developer atau dba langsung menggunakan konstruksi join berdasarkan hubungan yang kelihatan sangat nyata pada diagram Gambar 1 - yakni key "kode_produk".

Perintah Select SQL yang dihasilkan menggunakan konstruksi JOIN kira-kira sebagai berikut :

SELECT *
FROM   ms_produk,
       tr_penjualan,
       target_penjualan
WHERE  ms_produk.kode_produk = tr_penjualan.kode_produk
       AND ms_produk.kode_produk = target_penjualan.kode_produk

atau

SELECT *
FROM   ms_produk
       JOIN tr_penjualan
         ON ms_produk.kode_produk = tr_penjualan.kode_produk
       JOIN target_penjualan
         ON ms_produk.kode_produk = target_penjualan.kode_produk

Dan jika menggunakan agregasi function seperti SUM akan seperti ini :

SELECT ms_produk.kode_produk,
       SUM(jumlah_pembelian)       AS jumlah,
       SUM(nilai_target_penjualan) AS nilaitarget
FROM   ms_produk
       JOIN tr_penjualan
         ON ms_produk.kode_produk = tr_penjualan.kode_produk
       JOIN target_penjualan
         ON ms_produk.kode_produk = target_penjualan.kode_produk
GROUP  BY ms_produk.kode_produk


Konstruksi ini jelas salah karena JOIN akan menghasilkan kombinasi perkalian dari table-table yang terlibat. Dan karena hubungan antar kedua table transaksi adalah many to many Ini akan mengakibatkan "distorsi" nilai. Kecuali jika masing-masing JOIN digrouping terlebih dahulu dengan level yang sama dan menghasilkan unique row untuk tiap grouping.

Solusi : Penggunaan UNION

Agar konsisten maka table-table ini perlu digabung dengan mekanisme UNION.

Syntax :

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

Untuk selection, jumlah kolom dan tipe data dari table-table yang digabung harus sama.

Contoh :

SELECT tr_penjualan.kode_produk,
       tr_penjualan.jumlah_pembelian,
       NULL AS nilai_target_penjualan
FROM   ms_produk,
       tr_penjualan
WHERE  ms_produk.kode_produk = tr_penjualan.kode_produk
UNION
SELECT target_penjualan.kode_produk,
       NULL AS jumlah_pembelian,
       nilai_target_penjualan
FROM   ms_produk,
       target_penjualan
WHERE  ms_produk.kode_produk = target_penjualan.kode_produk

dan untuk agregasi SUM akan seperti ini :

SELECT t1.kode_produk,
       t1.nama_produk,
       SUM(t1.jumlah_pembelian)       AS jumlah,
       SUM(t1.nilai_target_penjualan) AS target
FROM   (SELECT tr_penjualan.kode_produk,
               ms_produk.nama_produk,
               tr_penjualan.jumlah_pembelian,
               NULL AS nilai_target_penjualan
        FROM   ms_produk,
               tr_penjualan
        WHERE  ms_produk.kode_produk = tr_penjualan.kode_produk
        UNION
        SELECT target_penjualan.kode_produk,
               ms_produk.nama_produk,
               NULL AS jumlah_pembelian,
               nilai_target_penjualan
        FROM   ms_produk,
               target_penjualan
        WHERE  ms_produk.kode_produk = target_penjualan.kode_produk) AS t1

Hasil UNION tanpa GROUP BY akan tampak seperti pada gambar di bawah ini.



~~~ Selesai ~~~
Comments