Pada kesempatan kali ini kita akan membahas query untuk menampilkan data dari beberapa tabel MySQL. Pada tutorial ini sobat juga akan belajar bagaimana menggunakan JOIN pada MySQL.
Ketika menampilkan data yang disimpan dari database, seringnya kita akan mengambil data dari beberapa tabel untuk diolah sehingga menjadi satu keluaran, misal pada laporan penjualan.
Pada model database relasional RDBMS termasuk MySQL, bentuk pengambilan data ini dapat dilakukan dengan mudah karena masing masing tabel saling berelasi/berhubungan, bentuk hubungan ini bermacam macam, bisa one-to-one relationship, one-to-many, dan many-to-many
Persiapan
Sebelum melangkah lebih jauh, kita perlu menyiapkan tabel yang akan kita gunakan dalam tutorial ini.
Pada contoh kali ini, saya menggunakan empat tabel, yaitu: tabel pelanggan, produk, transaksi, dan transaksi_detail. struktur dan hubungan keempat tabel tersebut tampak seperti pada gambar berikut:
Dari keempat tabel tersebut, kita hanya menggunakan beberapa diantaranya. Adapun contoh datanya adalah sebagai berikut:
Tabel barang:
+-----------+-------------+-------------+---------+------+
| id_barang | id_kategori | nama_barang | harga | stok |
+-----------+-------------+-------------+---------+------+
| 1 | 1 | RAM | 230000 | 4 |
| 2 | 1 | Mainboard | 1250000 | 7 |
| 3 | 1 | Mouse | 80000 | 6 |
| 4 | 3 | Mousepad | 35000 | 3 |
| 5 | 3 | Keyboard | 80000 | 5 |
+-----------+-------------+-------------+---------+------+Tabel pelanggan:
+--------------+---------+-------------------+ | id_pelanggan | nama | email | +--------------+---------+-------------------+ | 1 | Alfa | alfa@yahoo.com | | 2 | Beta | beta@yahoo.com | | 3 | Charlie | charlie@gmail.com | | 4 | Delta | delta@gmail.com | +--------------+---------+-------------------+
Tabel penjualan:
+--------------+--------------+---------------+-----------------+ | id_transaksi | id_pelanggan | tgl_transaksi | total_transaksi | +--------------+--------------+---------------+-----------------+ | 1 | 1 | 2017-02-22 | 230000 | | 2 | 3 | 2017-02-22 | 195000 | | 3 | 2 | 2017-01-01 | 1710000 | | 4 | 1 | 2017-02-04 | 310000 | | 5 | NULL | 2017-02-10 | 80000 | +--------------+--------------+---------------+-----------------+
I. JOIN Pada MySQL
Untuk menggabungkan tabel pada MySQL, kita gunakan klausa JOIN. Pada MySQL terdapat dua macam bentuk join, yaitu INNER JOIN, LEFT OUTER JOIN, dan RIGHT OUTER JOIN. Format penulisannya adalah sebagai berikut:
SELECT nama_kolom
FROM tabel
INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN tabel ON kondisiSelain menggunakan klausa ON untuk mendefinisikan kondisi, kita dapat menggunakan klausa USING, format penulisannya adalah:
SELECT nama_kolom
FROM tabel
INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN tabel USING(nama_kolom)Klausa USING ini akan menggunakan nama kolom yang ada di dalam tanda kurung untuk menghubungkan kedua tabel, kolom ini harus ada pada tabel yang ingin dihubungkan dan harus memiliki nama yang sama.
Biasanya kolom yang berhubungan didefinisikan sebagai Primary Key dan Foreign Key, namun tidak masalah jika keduanya bukan primary key maupun foreign key.
II. INNER atau CROSS JOIN
Cara pertama untuk menggabungkan tabel adalah menggunakan inner join. Dengan inner join, tabel akan digabungkan berdasarkan data yang sama, yang ada pada kedua tabel, jika di gambarkan dalam bentuk diagram venn, bentuk inner join seperti tampak pada gambar berikut:
Pada MySQL, penulisan INNER JOIN dapat dilakukan dengan dua cara yaitu (1) menggunakan klausa INNER JOIN (2) menggunakan klausa CROSS JOIN (3) cukup menggunakan klausa JOIN saja. Sobat bebas menggunakan keduanya asal konsisten, saya pribadi lebih prefer menggunakan JOIN saja, karena lebih simpel.
Sebagai contoh kita akan menampilkan data pelanggan yang melakukan pesanan, query yang kita jalankan:
SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
JOIN penjualan pn ON pl.id_pelanggan = pn.id_pelangganJika menggunakan klausa USING, maka query akan berbentuk seperti berikut:
SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
JOIN penjualan pn USING(id_pelanggan)Hasil:
+--------------+---------+---------------+-----------------+ | id_pelanggan | nama | tgl_transaksi | total_transaksi | +--------------+---------+---------------+-----------------+ | 1 | Alfa | 2017-02-22 | 230000 | | 3 | Charlie | 2017-02-22 | 195000 | | 2 | Beta | 2017-01-01 | 1710000 | | 1 | Alfa | 2017-02-04 | 310000 | +--------------+---------+---------------+-----------------+
Pada contoh diatas, terlihat bahwa
- Pelanggan dengan nama Delta tidak muncul, hal ini disebabkan pelanggan tersebut tidak pernah melakukan transaksi.
- Transaksi dengan id_trx 5 tidak muncul, karena transaksi tersebut memiliki nilai id_transaksi
NULL, sehingga tidak terhubung ke pelanggan manapun.
II. OUTER JOIN
Cara kedua untuk menggabungkan tabel pada MySQL adalah menggunakan outer join. Pada outer join, data pada salah satu tabel akan ditampilkan semua, sedangkan data pada tabel yang lain hanya akan ditampilkan jika data tersebut ada pada tabel pertama.
Pada MySQL, OUTER JOIN dibagi menjadi dua, yaitu LEFT OUTER JOIN dan RIGHT OUTER JOIN.
1 LEFT OUTER JOIN
Pada LEFT OUTER JOIN, semua data pada tabel sebelah kiri akan ditampilkan, sedangkan data pada tabel disebelah kanan hanya akan ditampilkan jika data terkait pada tabel tersebut muncul di tabel sebelah kiri.
Jika di gambarkan dalam bentuk diagram venn, bentuk LEFT OUTER JOIN tampak pada gambar berikut:
LEFT OUTER JOIN dapat ditulis menggunakan dua cara, yaitu (1) dengan klausa LEFT OUTER JOIN, (2) cukup dengan klausa LEFT JOIN saja, sobat bebas memilih salah satu yang penting konsisten, saya sendiri prefer menggunakan bentuk kedua karena lebih simpel.
Contoh kita tampilkan semua data pelanggan beserta data transaksinya, jalankan query berikut:
SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
LEFT JOIN penjualan USING(id_pelanggan)Hasil:
+--------------+---------+---------------+-----------------+ | id_pelanggan | nama | tgl_transaksi | total_transaksi | +--------------+---------+---------------+-----------------+ | 1 | Alfa | 2017-02-22 | 230000 | | 3 | Charlie | 2017-02-22 | 195000 | | 2 | Beta | 2017-01-01 | 1710000 | | 1 | Alfa | 2017-02-04 | 310000 | | 4 | Delta | NULL | NULL | +--------------+---------+---------------+-----------------+
Pada contoh diatas, terlihat bahwa dengan LEFT JOIN, data pada tabel sebelah kiri, yaitu tabel pelanggan akan ditampilkan semua, sedangkan data pada tabel sebelah kanan hanya akan ditampilkan jika nilai kolom id_pelanggan nya muncul pada tabel pertama, yaitu id_pelanggan 1, 2, dan 3
2 RIGHT OUTER JOIN
Kebalikan dari LEFT OUTER JOIN, pada RIGHT OUTER JOIN, data pada tabel sebelah kanan akan ditampilkan semua, sedangkan data pada sebelah kiri hanya ditampilkan jika data terkait pada tabel tersebut muncul pada tabel sebelah kanan.
Jika digambarkan dalam bentuk diagram venn, maka, bentuk right outer join akan tampak seperti gambar berikut:
Sama seperti LEFT OUTER JOIN, RIGHT OUTER JOIN juga dapat ditulis menggunakan dua cara, yaitu ditulis secara utuh atau cukup RIGHT JOIN saja. Saya sendiri lebih prefer menggunakan RIGHT JOIN saja.
Contoh kita tampilkan semua data transaksi beserta data pelanggannya, jalankan query berikut:
SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
RIGHT JOIN penjualan USING(id_pelanggan)Hasil:
+--------------+---------+--------------+---------------+-----------------+ | id_pelanggan | nama | id_transaksi | tgl_transaksi | total_transaksi | +--------------+---------+--------------+---------------+-----------------+ | 1 | Alfa | 1 | 2017-02-22 | 230000 | | 1 | Alfa | 4 | 2017-02-04 | 310000 | | 2 | Beta | 3 | 2017-01-01 | 1710000 | | 3 | Charlie | 2 | 2017-02-22 | 195000 | | NULL | NULL | 5 | 2017-02-10 | 80000 | +--------------+---------+--------------+---------------+-----------------+
Pada contoh diatas, terlihat bahwa semua data pada tabel disebelah kanan, yaitu tabel penjualan akan ditampilkan semua, sedangkan pada tabel sebelah kiri hanya ditampilkan yang data id_pelanggan nya muncul pada tabel penjualan.
Perlukah RIGHT JOIN?
Jika kita teliti lebih lanjut, sebenarnya right join hanya memindah posisi tabel, dari kiri ke kanan, contoh query pada right join dapat kita ubah dengan menjadi LEFT JOIN dengan mengubah posisi tabel, perhatikan contoh berikut:
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM penjualan pl
LEFT JOIN pelanggan USING(id_pelanggan)Perhatikan pada contoh diatas, kita balik posisi tabel penjualan dan tabel pelanggan. Jika query terebut dijalankan, hasil yang kita peroleh adalah:
+--------------+---------+--------------+---------------+-----------------+ | id_pelanggan | nama | id_transaksi | tgl_transaksi | total_transaksi | +--------------+---------+--------------+---------------+-----------------+ | 1 | Alfa | 1 | 2017-02-22 | 230000 | | 1 | Alfa | 4 | 2017-02-04 | 310000 | | 2 | Beta | 3 | 2017-01-01 | 1710000 | | 3 | Charlie | 2 | 2017-02-22 | 195000 | | NULL | NULL | 5 | 2017-02-10 | 80000 | +--------------+---------+--------------+---------------+-----------------+
Perhatikan bahwa hasil diatas sama persis dengan hasil pada contoh right join, jadi kesimpulannya, agar memudahkan, cukup gunakan salah satu bentuk outer join saja, LEFT JOIN atau RIGHT JOIN, saya sendiri prefer menggunakan LEFT JOIN.
III. IMPLISIT JOIN
Sejauh ini, kita menampilkan data dari beberapa tabel MySQL dengan menggunakan klausa JOIN.
Selain menggunakan klausa JOIN, terdapat satu cara lagi untuk menggabungkan tabel MySQL, yaitu menggunakan implisit join, disebut implisit join karena kita tidak menggunakan klausa JOIN, pada implisit join, kriteria hubungan antar tabel di definisikan pada klausa WHERE.
Sebagai contoh, mari kita gabungkan tabel pelanggan dan penjualan, jalankan query berikut:
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM pelanggan pl, penjualan pn
WHERE pl.id_pelanggan = pn.id_pelangganHasil yang kita peroleh:
+--------------+---------+--------------+---------------+-----------------+ | id_pelanggan | nama | id_transaksi | tgl_transaksi | total_transaksi | +--------------+---------+--------------+---------------+-----------------+ | 1 | Alfa | 1 | 2017-02-22 | 230000 | | 3 | Charlie | 2 | 2017-02-22 | 195000 | | 2 | Beta | 3 | 2017-01-01 | 1710000 | | 1 | Alfa | 4 | 2017-02-04 | 310000 | +--------------+---------+--------------+---------------+-----------------+
Perhatikan bahwa hasil tersebut sama persis dengan hasil pada contoh INNER JOIN, sehingga dapat disimpulkan bahwa implisit join = inner join.
Implisit join mensyaratkan kedua tabel memiliki data yang sama ( WHERE pl.id_pelanggan = pn.id_pelanggan ), sehingga implisit join ini hanya berlaku pada INNER JOIN, dan tidak bisa digunakan untuk OUTER JOIN.
Implisit JOIN ini merupakan cara lama ketika pertama kali standar SQL dibuat, setelah muncul standar yang lebih baru (SQL2) maka mulai digunakanlah klausa JOIN. Saya sendiri prefer menggunakan klausa JOIN karena lebih mudah dibaca dan dipahami, terutama hubungan antara tabel yang digabungkan.
Pada bentuk klausa JOIN, hubungan antar tabel dinyatakan pada klausa ON atau USING, sedangkan filter datanya dilakukan pada klausa WHERE, misal:
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM pelanggan pl
LEFT JOIN penjualan pn USING (id_pelanggan)
WHERE pl.id_pelanggan = 2 OR pl.id_pelanggan = 1sedangkan pada implisit JOIN, hubungan antar tabel dan filter datanya, semua didefinisikan pada klausa WHERE, misal:
SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM pelanggan pl, penjualan pn
WHERE pl.id_pelanggan = pn.id_pelanggan
AND (pl.id_pelanggan = 2 OR pl.id_pelanggan = 1)Demikian pembahasan mengenai cara menampilkan data dari beberapa tabel MySQL, semoga bermanfaat.
