Pendahuluan
Sama seperti halnya RDBMS (Relational Database Management System) yang lain, MySQL mempunyai kemampuan untuk mendefinisikan hubungan antara beberapa tabel dan menggambarkan informasi dari tabel-tabel tersebut dalam hubungan tersebut dalam sebuah statemen. Operasi ini dikenal sebagai operasi JOIN, yang merupakan salah satu operasi utama dari database berbasis SQL.
Pada proses perancangan database mungkin kita perlu memecah informasi yang saling berhubungan dalam beberapa tabel untuk merepresentasikan struktur database relasional, atau yang biasa disebut sebagai normalisasi. JOIN adalah merupakan tehnik yang dapat kita pakai untuk membangun kembali informasi yang saling berhubungan tersebut untuk mendapat informasi yang utuh.
Persiapan
Sebagai bahan pelatihan, di sini kita mempersiapkan beberapa tabel untuk digunakan menjelaskan beberapa operasi JOIN yang akan dijelaskan dibagian berikutnya.
Persiapan Database
Langkah awal yang perlu dipersiapkan adalah membuat databaselatihan
jika database tersebut belum ada, dan menggunakan database latihan
tersebut utnuk keperluan latihan kita, sebagai berikut
CREATE DATABASE IF NOT EXIST latihan; USE latihan;
Membuat Tabel Pelanggan
Tabel pertama yang dipersiapkan adalah tabelpelanggan
. Query yang digunakan untuk membuat dan mengisi serta menampilkan hasil data yang dimasukkan adalah sebagai berikut:
DROP TABLE IF EXISTS pelanggan; CREATE TABLE IF NOT EXISTS pelanggan ( pelanggan_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, nama VARCHAR(50) NOT NULL, PRIMARY KEY (pelanggan_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO pelanggan(nama) VALUES ('Ahmad Subagyo'), ('Benny Sihaloho'), ('Citra Mahardika'); SELECT * FROM pelanggan; +--------------+-----------------+ | pelanggan_id | nama | +--------------+-----------------+ | 1 | Ahmad Subagyo | | 2 | Benny Sihaloho | | 3 | Citra Mahardika | +--------------+-----------------+ 3 rows in set (0.00 sec)
Membuat Tabel Orders
Tabel kedua yang dipersiapkan adalah tabelorders
. Query yang digunakan untuk membuat dan mengisi serta menampilkan hasil data dari tabel orders
yang dimasukkan adalah sebagai berikut:
DROP TABLE IF EXISTS orders; CREATE TABLE IF NOT EXISTS orders ( order_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, pelanggan_id SMALLINT UNSIGNED NOT NULL, pegawai_id SMALLINT UNSIGNED NOT NULL, tanggal DATE NOT NULL, pengiriman_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(order_id) ) ENGINE=MYISAM DEFAULT CHARSET=utf8; INSERT INTO orders (pelanggan_id,pegawai_id,tanggal,pengiriman_id) VALUES (2,6,'2014-07-18',3), (5,2,'2014-07-19',1), (1,3,'2014-07-20',2); SELECT order_id AS id, pelanggan_id AS pelanggan, pegawai_id AS pegawai, tanggal, pengiriman_id AS pengiriman FROM orders; +----+-----------+---------+------------+------------+ | id | pelanggan | pegawai | tanggal | pengiriman | +----+-----------+---------+------------+------------+ | 1 | 2 | 6 | 2014-07-18 | 3 | | 2 | 5 | 2 | 2014-07-19 | 1 | | 3 | 1 | 3 | 2014-07-20 | 2 | +----+-----------+---------+------------+------------+ 3 rows in set (0.00 sec)
Membuat Tabel Pegawai
Tabel berikutnya yang perlu dipersiapkan adalah tabelpegawai
. Sedangkan query yang digunakan untuk membuat dan mengisi serta menampilkan hasil data yang dimasukkan adalah sebagai berikut:
DROP TABLE IF EXISTS pegawai; CREATE TABLE IF NOT EXISTS pegawai ( pegawai_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, nama_depan VARCHAR(20) NOT NULL, nama_belakang VARCHAR(20) NOT NULL, tanggal_lahir DATE NOT NULL, PRIMARY KEY(pegawai_id) ) ENGINE=MYISAM DEFAULT CHARSET=utf8; INSERT INTO pegawai(nama_depan,nama_belakang,tanggal_lahir) VALUES ('Desi','Anggraeni','1998-12-08'), ('Edwin','Pardosi','2002-02-19'), ('Friza','Annisa','2003-08-30'); SELECT * FROM pegawai; +------------+------------+---------------+---------------+ | pegawai_id | nama_depan | nama_belakang | tanggal_lahir | +------------+------------+---------------+---------------+ | 1 | Desi | Anggraeni | 1998-12-08 | | 2 | Edwin | Pardosi | 2002-02-19 | | 3 | Friza | Annisa | 2003-08-30 | +------------+------------+---------------+---------------+ 3 rows in set (0.00 sec)
INNER JOIN
Query INNER JOIN
digunakan untuk menampilkan hanya data yang ada di kedua tabel yang direlasikan (irisan), dengan suatu kondisi tertentu
Salah satu perintah JOIN yang paling sering dipakai adalah INNER JOIN
atau cukup dituliskan sebagai JOIN
saja. Hasil umum dari operasi INNER JOIN
ini sama dengan hasil yang didapat dari operasi natural join. Sebagai contoh, ingin ditampilkan nama pelanggan dan nomor order_id-nya dari data yang diperoleh dari tabel pelanggan
dan tabel orders
dengan operasi natural join berikut ini:
SELECT pelanggan.nama, orders.order_id FROM pelanggan,orders WHERE pelanggan.pelanggan_id=orders.pelanggan_id ORDER BY pelanggan.nama;
akan mempunyai hasil yang sama dengan hasil dari operasi INNER JOIN
berikut:
SELECT pelanggan.nama, orders.order_id FROM pelanggan INNER JOIN orders ON pelanggan.pelanggan_id=orders.pelanggan_id ORDER BY pelanggan.nama;
Pada database MySQL, penggunaan klausa INNER JOIN
bisa cukup dituliskan sebagai JOIN
saja,sehingga query di atas bisa juga dituliskan sebagai berikut
SELECT pelanggan.nama, orders.order_id FROM pelanggan JOIN orders ON pelanggan.pelanggan_id=orders.pelanggan_id ORDER BY pelanggan.nama;
Penggunaan klausa ON
dengan nama field yang sama antar ruas kiri dan kanan (ON pelanggan.pelanggan_id=orders.pelanggan_id
; dimana keduanya memakai field bernama pelanggan_id
dikedua ruasnya) dapat dituliskan dengan fungsi USING()
sebagai berikut:
SELECT pelanggan.nama, orders.order_id FROM pelanggan INNER JOIN orders USING(pelanggan_id) ORDER BY pelanggan.nama;
Semua query tersebut akan menghasilkan data sebagai berikut:
+----------------+----------+ | nama | order_id | +----------------+----------+ | Ahmad Subagyo | 3 | | Benny Sihaloho | 1 | +----------------+----------+ 2 row in set (0.00 sec)
LEFT JOIN
Query LEFT JOIN
digunakan untuk menampilkan semua data yang ada pada tabel di ruas kiri yang direlasikan, dengan suatu kondisi tertentu, meskipun data tersebut tidak terdapat di tabel yang berada di ruas kanan
Perintah JOIN berikutnya yang juga sering dipakai adalah LEFT JOIN
atau secara lengkapnya dituliskan sebagai LEFT OUTER JOIN
. Pada operasi LEFT JOIN
, semua data yang ada di tabel pada ruas kiri akan ditampilkan meskipun tidak ada data relasinya di tabel sebelah kanan. Dengan kata lain, sekiranya data pada tabel ruas kanan tidak ada maka akan ditampilkan sebagai data dengan nilai NULL
Sebagai contoh, dari data yang kita miliki, ingin ditampilkan data seluruh nama pelanggan dan order id-nya, yang mana jika tidak ada data order untuk pelanggan tersebut di tabel order
maka data nama pelanggan yang diambil dari tabel pelanggan
tetap ditampilkan, sedangkan order_id
-nya bernilai NULL. Query yang bisa kita buat untuk maksud tersebut dengan klausa LEFT OUTER JOIN
adalah sebagai berikut:
SELECT pelanggan.nama, orders.order_id FROM pelanggan LEFT OUTER JOIN orders ON pelanggan.pelanggan_id=orders.pelanggan_id ORDER BY pelanggan.nama;
Klausa LEFT OUTER JOIN
bisa cukup dituliskan sebagai LEFT JOIN
saja, sehingga query tersebut dapat dtuliskan menjadi:
SELECT pelanggan.nama, orders.order_id FROM pelanggan LEFT JOIN orders ON pelanggan.pelanggan_id=orders.pelanggan_id ORDER BY pelanggan.nama;
Seperti halnya yang sudah dijelaskan sebelumnya, pemakaian klausa ON
dengan nama field pada kedua ruas sama (dalam hal ini ON pelanggan.pelangggan_id=orders.pelanggan_id
menggunakan field pelanggan_id
di kedua ruasnya untuk relasinya); dapat digantikan dengan menggunakan fungsi USING()
sebagai berikut:
SELECT pelanggan.nama, orders.order_id FROM pelanggan LEFT JOIN orders USING(pelanggan_id) ORDER BY pelanggan.nama; +-----------------+----------+ | nama | order_id | +-----------------+----------+ | Ahmad Subagyo | 3 | | Benny Sihaloho | 1 | | Citra Mahardika | NULL | +-----------------+----------+ 3 rows in set (0.00 sec)
RIGHT JOIN
Query RIGHT JOIN
digunakan untuk menampilkan semua data yang ada pada tabel di ruas kanan yang direlasikan, dengan suatu kondisi tertentu, meskipun data tersebut tidak terdapat di tabel yang berada di ruas kiri
SELECT orders.order_id, pegawai.nama_depan FROM orders RIGHT OUTER JOIN pegawai ON orders.pegawai_id=pegawai.pegawai_id ORDER BY orders.order_id; SELECT orders.order_id, pegawai.nama_depan FROM orders RIGHT JOIN pegawai ON orders.pegawai_id=pegawai.pegawai_id ORDER BY orders.order_id; SELECT orders.order_id, pegawai.nama_depan FROM orders RIGHT JOIN pegawai USING(pegawai_id) ORDER BY orders.order_id; +----------+------------+ | order_id | nama_depan | +----------+------------+ | NULL | Desi | | 2 | Edwin | | 3 | Friza | +----------+------------+ 3 rows in set (0.01 sec)
FULL JOIN*
Query FULL JOIN
digunakan untuk menampilkan semua data yang ada pada tabel di ruas kiri maupun kanan yang direlasikan, dengan suatu kondisi tertentu, meskipun data tersebut tidak terdapat di tabel yang berada di ruas yang berlawanan
Bentuk operasi JOIN yang lain adalah FULL OUTER JOIN. Dalam MySQL sebenarnya tidak dikenal adanya operasi ini namun kita bisa menyimulasikan operasi ini dengan menggabungkan LEFT JOIN, RIGHT JOIN dan operasi UNION.
-- * -- SELECT pelanggan.nama, orders.order_id -- FROM pelanggan -- FULL OUTER JOIN orders -- ON pelanggan.pelanggan_id=orders.pelanggan_id -- ORDER BY pelanggan.nama; -- * -- SELECT pelanggan.nama, orders.order_id -- FROM pelanggan -- FULL JOIN orders -- ON pelanggan.pelanggan_id=orders.pelanggan_id -- ORDER BY pelanggan.nama; SELECT pelanggan.nama, orders.order_id FROM pelanggan LEFT JOIN orders ON pelanggan.pelanggan_id=orders.pelanggan_id UNION SELECT pelanggan.nama, orders.order_id FROM pelanggan RIGHT JOIN orders ON pelanggan.pelanggan_id=orders.pelanggan_id ORDER BY nama; +-----------------+----------+ | nama | order_id | +-----------------+----------+ | NULL | 2 | | Ahmad Subagyo | 3 | | Benny Sihaloho | 1 | | Citra Mahardika | NULL | +-----------------+----------+ 4 rows in set (0.00 sec)