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 ~~~
