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)