Arsip Tag: mysql

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

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.