MODUL MENGGUNAKAN BAHASA PEMROGRAMAN SQL TINGKAT LANJUT MATERI : VIEW DAN JOIN
A. JOIN Join adalah penggabungan table yang dilakukan melalui kolom / key tertentu yang memiliki nilai terkait untuk mendapatkan satu set data dengan informasi lengkap. Perintah JOIN dalam SQL digunakan untuk menampilkan data pada table yang saling berhubungan atau berelasi. Artinya kita dapat menampilkan data dalam beberapa table hanya dengan satu kali perintah
JOIN Pada MySQL
Untuk menggabungkan tabel pada MySQL, kita gunakan klausa JOIN. Pada MySQL terdapat beberapa 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 kondisi Selain 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.
INNER JOIN
INNER JOIN adalah tipe join yang akan kita bahas pertama. Tipe join ini akan mengambil semua row dari table asal dan table tujuan dengan kondisi nilai key yang terkait saja, dan jika tidak maka row tersebut tidak akan muncul. Inner Join merupakan perintah untuk menampilkan semua data yang mempunyai nilai sama. Contoh: SELECT * FROM karyawan INNER JOIN gaji ON karyawan.karyawan_id=gaji.karyawan_id; Query diatas akan menghasilakn output seperti berikut:
Coba Kita perhatikan jumlah record yang ditampilkan! Record yang ditampilkan sebanyak 5 record. Karena INNER JOIN hanya memperhitungkan kondisi key yang terkait antara table karyawan dengan table gaji. Sedangkan karyawan dengan karyawan_id=’006’ tidak ditampilkan, karena tidak terkait dengan table gaji. Jika dibuat diagram venn-nya akan terlihat seperti gambar berikut:
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 JOIN
LEFT JOIN atau biasa juga dikenal dengan LEFT OUTER JOIN merupakan perintah join untuk menampilkan semua data sebelah kiri dari table yang di joinkan dan menampilkan data sebelah kanan yang cocok dengan kondisi join. Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis. Contoh: SELECT * FROM karyawan LEFT JOIN gaji ON karyawan.karyawan_id=gaji.karyawan_id; Query diatas akan menghasilkan output seperti berikut:
Coba Kita perhatikan jumlah record yang ditampilkan! Record yang ditampilkan sebanyak 6 record. Karena LEFT JOIN akan menampilkan semua table sebelah kiri dari kondisi join yaitu table karyawan. Semua data pada table karyawan akan ditampilkan, meskipun tidak ada kecocokan key pada table gaji. Jika dibuat diagram venn-nya akan terlihat seperti gambar berikut:
Selain kondisi diatas, LEFT JOIN juga bisa menampilkan data yang hanya kondisi key pada table tamu ( foreign key ) kosong ( NULL). Contoh: SELECT * FROM karyawan LEFT JOIN gaji ON karyawan.karyawan_id=gaji.karyawan_id WHERE gaji.karyawan_id IS NULL; Query diatas akan menghasilkan output seperti berikut:
Data yang ditampilkan hanya 1 record. Hal ini dikarenakan, hanya ada satu data yang belum memiliki kecocokan key pada table tamu. Untuk mempermudah kita memahami perbedaan antara kedua LEFT JOIN ini coba perhatikan diagram venn berikut:
Dengan melihat perbedaan dari diagram venn tersebut, maka Kita akan mudah memahami bagaimana left join ini bekerja. Ingat LEFT JOIN ini sangat penting untuk kita pahami, karena disaat Kita mulai mengerjakan project yang cukup kompleks, maka kita akan banyak berkutat dengan left join ini. Contoh pada kasus diatas, hanya dengan memanfaatkan left join kita bisa menampilkan semua data karyawan yang sudah ada gajinya dan siapa yang belum ada gajinya. Selain itu kita juga dapat menampilkan semua data karyawan yang belum ada gajinya dengan fungsi LEFT JOIN WHERE NULL
2 RIGHT JOIN
Kebalikan dari LEFT JOIN adalah RIGHT JOIN, atau biasa juga dikenal dengan RIGHT OUTER JOIN. RIGHT JOIN akan menampilkan semua data yang ada di table sebelah kanan dan mencari kecocokan key pada table sebelah kiri. Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis pada table sebelah kiri. Contoh: SELECT * FROM gaji RIGHT JOIN karyawan ON gaji.karyawan_id=karyawan.karyawan_id; Query diatas akan menampilkan output seperti gambar be rikut:
Pada output diatas, kita dapat melihat bahwa terdapat NULL pada table sebelah kiri. Hal ini dikarenakan tidak ditemukan kecocokan key diantara kedua table. Untuk lebih mudah memahaminya, perhatikan diagram venn berikut:
Selain kondisi diatas, RIGHT JOIN juga bisa menampilkan data yang hanya kondisi key pada table tamu ( foreign key ) kosong ( NULL). Contoh: SELECT * FROM gaji RIGHT JOIN karyawan ON gaji.karyawan_id=karyawan.karyawan_id WHERE gaji.karyawan_id IS NULL; Query diatas akan menghasilkan output seperti gambar berikut:
Data yang ditampilkan hanya 1 record. Hal ini dikarenakan, hanya ada satu data yang belum memiliki kecocokan key pada table tamu. Untuk mempermudah kita memahami perbedaan antara kedua RIGHT JOIN ini coba perhatikan diagram venn berikut:
Perhatikan diagram venn diatas, dan bandingkan dengan diagram venn sebelumnya.
B. VIEW View adalah perintah query yang disimpan pada database dengan suatu nama tertentu, sehingga bisa digunakan setiap saat untuk melihat data tanpa menuliskan ulang query tersebut. Di dalam MySQL, View dapat didefenisikan sebagai ‘tabel virtual’. Tabel ini bisa berasal dari tabel lain, atau gabungan dari beberapa tabel. Tujuan dari pembuatan VIEW adalah untuk kenyamanan (mempermudah penulisan query), untuk keamanan (menyembunyikan beberapa kolom yang bersifat rahasia), atau dalam beberapa kasus bisa digunakan untuk mempercepat proses menampilkan data (terutama jika kita akan menjalankan query tersebut secara berulang). Keuntungan VIEW : 1. Membatasi akses data 2. Menyediakan data yang independen 3. Menampilkan view yang berbeda-beda dengan data yang sama 4. Memudahkan query yang kompleks
Sebagai contoh, misalkan kita ingin menampilkan nama dosen yang berdomisi di Jakarta, maka kita bisa menggunakan query berikut: mysql> SELECT NIP, nama_dosen, alamat FROM daftar_dosen WHERE alamat = 'Jakarta'; +------------+--------------+---------+ | NIP | nama_dosen | alamat | +------------+--------------+---------+ | 0576431001 | M. Siddiq | Jakarta | | 1080432007 | Arif Budiman | Jakarta | +------------+--------------+---------+ 2 rows in set (0.00 sec) Misalkan query tersebut akan dijalankan setiap beberapa detik (diakses dari website yang sibuk), pada setiap permintaan data, MySQL server harus melakukan pemrosesan untuk mencari seluruh dosen yang memiliki alamat di Jakarta. Selain itu, dengan menggunakan VIEW kita bisa menyembunyikan beberapa kolom dari tabel daftar_dosen (tabel sumbernya). Cara Penggunaan VIEW di dalam MySQL
Untuk membuat View di dalam MySQL, kita tinggal menggunakan format dasar sebagai berikut. Sintak dasar untuk membuat view di MySQL: CREATE VIEW nama_view AS SELECT kolom_1, kolom_2, kolom_n FROM nama_table
WHERE kondisi; Selanjutnya VIEW bisa diakses seperti tabel ‘biasa’. Agar lebih jelas, langsung saja kita praktek menggunakan tabel daftar_dosen dari database mahasiswa: mysql> CREATE VIEW dosen_jakarta AS SELECT NIP, nama_dosen, alamat FROM daftar_dosen WHERE alamat = 'Jakarta'; Query OK, 0 rows affected (0.17 sec)
mysql> SELECT * FROM dosen_jakarta; +------------+--------------+---------+ | NIP | nama_dosen | alamat | +------------+--------------+---------+ | 0576431001 | M. Siddiq | Jakarta | | 1080432007 | Arif Budiman | Jakarta | +------------+--------------+---------+ 2 rows in set (0.15 sec) Dalam query diatas, kita membuat sebuah VIEW bernama dosen_jakarta. Kita juga menyembunyikan kolom no_hp dari tabel asli daftar_dosen. Untuk mengakses data yang terdapat di VIEW, cukup menggunakan query SELECT: mysql> SELECT * FROM dosen_jakarta; +------------+--------------+---------+ | NIP | nama_dosen | alamat | +------------+--------------+---------+ | 0576431001 | M. Siddiq | Jakarta | | 1080432007 | Arif Budiman | Jakarta | +------------+--------------+---------+ 2 rows in set (0.18 sec) Sekarang, pada setiap pemanggilan VIEW, MySQL Server tidak perlu memfilter hasil pencarian, namun cukup memanggil tabel virtual. Hal ini akan mempercepat proses tampilan data. VIEW juga berfungsi sama seperti layaknya tabel ‘biasa’, sebagai contoh, kita bisa melakukan query berikut: mysql> SELECT nama_dosen FROM dosen_jakarta WHERE NIP = '1080432007'; +--------------+ | nama_dosen | +--------------+ | Arif Budiman | +--------------+ 1 row in set (0.20 sec) Meng-Update View
Kita dapat meng-update view sesuai dengan kebutuhan Kita seperti menambahkan kolom pada view ataupun menambahkan filter pada Where Clause di view tersebut. Untuk mengupdate view, Kita bisa menggunakan perintah CREATE OR REPLACE VIEW. Di bawah ini adalah sintak dasar untuk mengupdate view yang sudah ada di MySQL: CREATE OR REPLACE VIEW nama_view AS SELECT kolom_1, kolom_2, kolom_n FROM nama_tabel
WHERE kondisi;
Sebagai Latihan, coba Kita modifikasi view V_KARYAWAN_IT yang semula menampilkan semua jenis kelamin, dengan menambahkan filter HANYA karyawan yang berjenis kelamin laki-laki saja.
mysql> CREATE OR REPLACE VIEW V_KARYAWAN_IT AS SELECT nik, nama, gender "Jenis Kelamin", gaji_pokok+tunjangan "Gaji Bersih" FROM karyawan WHERE department = 'IT' AND gender = 'L';
Query OK, 0 rows affected (0.04 sec) Menghapus View (Drop View)
Di bawah ini adalah sintak dasar untuk menghapus view di MySQL DROP VIEW nama_view; Contoh: DROP VIEW V_KARYAWAN_IT;
