Arsip Kategori: basis data

Menggabungkan Query Menggunakan Union di MySQL

ilustrasi

Prakata

Mungkin kita pernah membuat model tabel yang rumit seperti ini, tabel yang bisa memiliki atribut sama namun sengaja dipisahkan, tapi pada akhirnya kita harus menjalankan 2 query terpisah untuk memanggilnya. Sebut saja kita mempunyai tabel bukutamu dengan atribut (id, alamat_ip, nama, judul, pesan, tanggal), namun pada masa penggunaan ternyata banyak yang mengirimkan pesan spam yang tidak kita inginkan, dan solusi yang terpikir pada saat itu adalah membuat tabel spam (dengan atribut yang sama dengan tabel, tapi kita menyortir terlebih dahulu dengan filter anti-spam yang kita buat, atau mungkin menggunakan Akismet), dengan maksud agar si spam bisa ditinggalkan saat backup, dan tabel bukutamu dalam keadaan bebas spam.

Di sistem pengelolaan (backend), kita bisa melakukan query masing-masing terhadap kedua tabel untuk ditampilkan terpisah, tapi melihat dua halaman terpisah untuk maksud yang sama (dalam hal ini membedakan yang mana buku tamu dengan spam) bukanlah solusi yang bagus.

Dibuat tabel pengelolaan terpisah
kedua tabel dibuat terpisah dengan pengelolaan terpisah

Solusi

Dalam kasus seperti ini bisa dilakukan tiga solusi;

  1. Gabungkan kedua tabel secara fisik menjadi satu dan ditambah atribut (…, is_spam) sehingga nantinya query akan dibatasi dengan ‘ WHERE is_spam = 0‘ (dimana kita memilih hanya bukan spam)
  2. Gabungkan kedua tabel dalam query menggunakan UNION sehingga yang kita lakukan hanya sekali query saja
  3. Lakukan dua query untuk mengambil data dari kedua tabel dan menggabungkannya nanti di pemrograman.

Tapi sesuai judul, kita akan menggunakan pilihan nomor 2 saja.

UNION Beraksi

Jadi, setelah kita sepakat dengan memilih solusi nomor dua diatas, maka tinggal kita selesaikan dengan sihir UNION saja.
Meskipun sudah dibahas sedikit, tapi sebelum lebih jauh biar saya jelaskan tambahan mengenai UNION.
Pada dasarnya, union adalah fungsi untuk menggabungkan dua atau lebih query SELECT dalam satu hasil keluaran saja. Dengan catatan kedua query SELECT tersebut harus memiliki jumlah field yang sama.
Langsung kita coba, struktur tabel bukutamu dan spam yang saya gunakan adalah sebagai berikut

CREATE TABLE bukutamu
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    alamat_ip VARCHAR(16),
    nama VARCHAR(50),
    judul VARCHAR(50),
    pesan VARCHAR(250),
    tanggal TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)

Begitu pula dengan tabel spam, tidak ada yang dirubah selain nama tabelnya.
Untuk itu, dengan query ini…

(SELECT id, nama, judul, pesan, tanggal, '0' as is_spam FROM bukutamu)
UNION
(SELECT id, nama, judul, pesan, tanggal, '1' as is_spam FROM spam)
ORDER BY tanggal DESC;

kedua SELECT akhirnya akan bersatu dan menambahkan satu field is_spam yang bernilai 0 jika datang dari tabel bukutamu, dan bernilai 1 jika datang dari tabel spam.
Sifat normalnya UNION adalah selalu DISTINCT, jadi jika ditemukan record yang sama tidak akan ditampilkan kedua-kalinya.

pengelolaan terpusat pada satu tabel
Pengelolaan Bukutamu dan Spam disatukan

Kesimpulan

Jadi UNION berguna untuk menggabungkan beberapa query SELECT untuk menghasilkan satu keluaran saja.
Batasan/aturan yang dapat kita simpulkan diantaranya…

  • Query yang disatukan harus menghasilkan jumlah field yang sama.
  • Nilai record yang sama dalam UNION akan disatukan, dan tidak akan tampil dua kali (sama ketika kita menggunakan DISTINCT)
  • Statement ORDER menggunakan alias pada setiap SELECT, bukan nama field sebenarnya.
  • Statement ORDER (tanpa LIMIT pada salah-satu SELECT) harus disimpan di akhir, karena jika disimpan didalam salah-satu SELECT tidak akan berpengaruh.
  • Jika menggunakan statement LIMIT (dan atau ORDER), harus ditentukan didalam salah-satu SELECT atau LIMIT total.

Untuk batasan standar penggunaan saya rasa ini cukup memenuhi, namun jika anda merasa ini masih kurang, saya pastikan informasi lebih lengkap terdapat di MySQL Manual : Union Syntax.

Kemana Lagi Setelah Ini

MySQL Manual : Union Syntax

Database Abstraction Layer :: Kemudahan Melawan Performa

koneksi antara aplikasi database engine, abstraction layer dan developer/user/application layer
koneksi antara aplikasi database engine, abstraction layer dan developer/user/application layer

Abstraction Layer atau lapisan abstraksi adalah suatu lapisan untuk menyembunyikan kerumitan yang ada pada fungsi-fungsi yang serupa. Begitu pula dengan Database Abstraction Layer, suatu pendekatan penyederhanaan/penyeragaman fungsi dari setiap database engine yang ada, jadi kita tidak perlu mengetahui fungsi API database spesifik yang kita gunakan, yang perlu kita ketahui hanyalah fungsi API dari Database Abstraction Layer yang kita gunakan. Terkesan sederhana. Ya, memang ini menjadikan pekerjaan programmer sederhana.
Mungkin selama ini anda pernah mendengar ODBC, khususnya bagi anda pengguna Windows, karena memang pada awalnya ODBC muncul di sistem operasi ini. ODBC adalah salah satu dari berbagai jenis Database Abstraction Layer yang ada. Jenis lainnya adalah ORM, di PHP tersedia ActiveRecord, Doctrine, dll; di Java ada Hibernate, dll. Juga ada jenis yang lebih sederhana, seperti pada PHP ada PDO(PECL), atau PEAR DB(PEAR).
Dengan pendekatan ini, para programmer akan semakin mudah dalam menghubungkan aplikasi yang mereka buat dengan database, tapi harus kita ingat bahwa setiap pemecahan masalah akan selalu menimbulkan masalah baru. Memang benar bahwa dengan menggunakan Doctrine, kita tidak perlu mengkhawatirkan engine database apa yang kita gunakan, kita bisa menggunakan postgreSQL, MySQL, MSSQL, ataupun juga Oracle; dengan menggunakan Hibernate kita hanya perlu menggunakan fungsi API hibernate saja. Tapi, dengan adanya abstraction layer, artinya ada rutin fungsi lain yang dijalankan, ya, dengan itu berarti aplikasi jadi yang kita buat bisa menjadi lebih lambat dalam mengakses data :(, sayang sekali…
Contohnya mungkin pada skrip php yang menggunakan PDO berikut ini:

<?php
// tentukan $dsn, $username dan $password
$dsn = "mysql:host=localhost;dbname=telepon";
$username = "user";
$passwd = "password";

try{
    $pdo = new PDO($dsn, $username, $passwd);
    $runq = $pdo-&gt;query("SELECT * FROM akun a, telepon t WHERE a.id=t.id_akun");
    foreach ($runq as $row){
        echo $row['nama']." ".$row['telepon']."&lt;br/&gt;";
    }
}catch(PDOException $e){
    echo $e-&gt;getMessage();
}
?>

Ini dia, lihat pada $dsn, disini $dsn bisa diganti dengan driver lain yang disupport oleh PDO, kita bisa mengubahnya menjadi mssql ataupun yang lainnya. Untuk menjalankan query, kita hanya perlu menjalankan $pdo->query($sql), dan lakukan iterasi untuk memunculkan hasilnya.
Namun, lihat pada objek PDO, objek ini menerima berbagai masukan jenis database engine yang ada, tapi mengeluarkan hasil yang sama, artinya ada sesuatu yang kompleks yang terjadi di dalam fungsi objek ini, dan tentunya itu akan makan waktu, yang berharga untuk setiap permintaan query user :(.
Dengan rangkuman bahwa Database Abstraction Layer memiliki:
Kelebihan

  • Waktu pengembangan (yang perlu dilakukan developer hanya mengetahui fungsi DBAL saja)
  • Tidak perlu mengkhawatirkan database engine (selama driver terpenuhi, maka fungsi DBAL akan berjalan sesuai keinginan)

Kekurangan

  • Waktu (karena DBAL menjalankan rutin fungsi yang lebih banyak daripada fungsi native yang disediakan, tentunya waktu query jadi lebih lambat)
  • Fungsi Kompleks Spesifik Engine (mengingat DBAL ini fungsinya untuk menyeragamkan fungsi dan hasil, kita tidak akan menemukan kelebihan spesifik (fungsi kompleks) dari engine database. misal: pengoptimasian query, karena tidak semua database engine menyediakan cara untuk optimasi query (lewat index), jadi DBAL-pun tidak menyertakannya)

dan, itulah garis besar dari database abstraction layer.

Join Table di MySQL

setelah mengisi praktikum basisdata kemarin, saya berjanji untuk membuat materi ini… baiklah ini nih.
saya ambil langsung terjemahan dari MySQL 5.0 reference, biar cepet.. hehe…
post ini menjelaskan tentang
(cross join, inner join, right join, left join, natural join, join using)
yang berlaku di MySQL 5

pada suatu kasus, terdapat tabel sederhana pengguna dengan struktur dan data sebagai berikut
tabel pengguna

CREATE TABLE `pengguna` (
`id_pengguna` int(11) NOT NULL auto_increment,
`nama` varchar(20) NOT NULL,
PRIMARY KEY  (`id_pengguna`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tabel pengguna_lengkap

CREATE TABLE `pengguna_lengkap` (
`id_pengguna` int(11) NOT NULL,
`alamat` varchar(50) NOT NULL,
`telepon` varchar(15) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

data:

INSERT INTO `pengguna` (`id_pengguna`, `nama`) VALUES
(1, 'khalifavi'),
(2, 'silvershade'),
(3, 'kaelina');
INSERT INTO `pengguna_lengkap` (`id_pengguna`, `alamat`, `telepon`) VALUES
(1, 'cigiringsing', '081910435544'),
(2, 'cijambe', '022 7815913'),
(4, 'tubagus ismail', '022 91724325');

dari data tersebut, dapat kita lihat bahwa pada tabel pengguna, tidak terdapat record dengan id 4, dan begitu pula pada tabel pengguna_lengkap tidak terdapat record dengan id 3.
apabila dilakukan join biasa maka beberapa record tidak akan tampil seperti yang kita mau

SELECT *
FROM pengguna p, pengguna_lengkap pk
WHERE p.id_pengguna = pk.id_pengguna;

hanya akan menampilkan

id_pengguna nama id_pengguna alamat telepon
1 khalifavi 1 cigiringsing 081910435544
2 silvershade 2 cijambe 022 7815913

2 rows in set (0.01 sec)

lalu dimana yang memiliki id_pengguna 3 dan 4?, untuk itulah ada beberapa join khusus…

untuk menampilkan semua yang terdapat di tabel pengguna namun tidak terdapat di tabel pengguna_lengkap kita bisa menggunakan LEFT JOIN

SELECT *
FROM pengguna p LEFT JOIN pengguna_lengkap pk
ON p.id_pengguna = pk.id_pengguna;

maka akan menghasilkan

id_pengguna nama id_pengguna alamat telepon
1 khalifavi 1 cigiringsing 081910435544
2 silvershade 2 cijambe 022 7815913
3 kaelina NULL NULL NULL

3 rows in set (0.01 sec)

pada penggunaan LEFT JOIN ini, record yang ada di kedua tabel atau hanya di tabel kiri akan selalu ditampilkan, dan akan menghasilkan nilai NULL pada tabel di kanan

begitu pula sebaliknya dengan RIGHT JOIN query ini

SELECT *
FROM pengguna p RIGHT JOIN pengguna_lengkap pk
ON p.id_pengguna = pk.id_pengguna;

akan menghasilkan

id_pengguna nama id_pengguna alamat telepon
1 khalifavi 1 cigiringsing 081910435544
2 silvershade 2 cijambe 022 7815913
NULL NULL 4 tubagus ismail 022 91724325

3 rows in set (0.02 sec)

digunakan untuk menampilkan record yang ada di kedua tabel atau hanya terdapat di tabel kanan

Note: penggunaan SELECT * pada LEFT atau RIGHT join akan menampilkan column yang redundansi. jadi?? kita lanjut…kan

namun mari kita bandingkan dengan penggunaan NATURAL JOIN

SELECT *
FROM pengguna p NATURAL JOIN pengguna_lengkap pk;
id_pengguna nama alamat telepon
1 khalifavi cigiringsing 081910435544
2 silvershade cijambe 022 7815913

2 rows in set (0.00 sec)
hey, ini sama dengan query pertama

SELECT *
FROM pengguna p, pengguna_lengkap pk
WHERE p.id_pengguna = pk.id_pengguna;
id_pengguna nama id_pengguna alamat telepon
1 khalifavi 1 cigiringsing 081910435544
2 silvershade 2 cijambe 022 7815913

2 rows in set (0.01 sec)
jadi bisa dikatakan bahwa ini penyederhanaan query pertama, namun menghapus column yang redundansi, hmmm, solusi yang bagus juga…

Note: hasil NATURAL JOIN sama dengan JOIN … USING

SELECT *
FROM pengguna p NATURAL JOIN pengguna_lengkap pk;

akan menghasilkan output yang sama dengan penggunaan JOIN … USING ini

SELECT *
FROM pengguna p JOIN pengguna_lengkap pk USING (id_pengguna);

ok, sekarang kita kombinasikan NATURAL JOIN dengan RIGHT JOIN

SELECT *
FROM pengguna p NATURAL RIGHT JOIN pengguna_lengkap pk;
id_pengguna nama alamat telepon
1 khalifavi cigiringsing 081910435544
2 silvershade cijambe 022 7815913
4 NULL tubagus ismail 022 91724325

3 rows in set (0.00 sec)
hmmm, untuk yang ini silahkan bandingkan sendiri…

dan mari kita coba INNER JOIN

SELECT *
FROM pengguna p INNER JOIN pengguna_lengkap pk
ON p.id_pengguna = pk.id_pengguna;
id_pengguna nama id_pengguna alamat telepon
1 khalifavi 1 cigiringsing 081910435544
2 silvershade 2 cijambe 022 7815913

2 rows in set (0.00 sec)
yang satu ini benar-benar mirip dengan query pertama, dan tanpa perbedaan sama sekali…

Note: di MySQL, INNER JOIN dan CROSS JOIN ini serupa (dapat saling menggantikan). namun di SQL Standar ini tidak serupa, INNER JOIN dapat menggunakan ON, tapi tidak dengan CROSS JOIN.