Temu Kembali Data (Data Retrieval) Menggunakan SELECT

Bagian ketiga dari rangkaian artikel tentang Konsep Database Relasional Pada bagian kedua (Membuat Model Basis Data) kita sudah membahas bagaimana menganalisa dan membuat design model basis data (database model) mulai dari model konsep (conceptual model), model logis (logical model), sampai menjadi model fisik (physical model). Pada artikel ini kita akan fokus pada anatomi dan penggunaan perintah SQL SELECT — atau biasa disebut sebagai query — untuk melakukan proses temu kembali data (data retrieval). Kita akan menggunakan database PostgreSQL dan pgAdmin3 dalam artikel ini, namun tidak ada halangan untuk mempraktekkannya menggunakan database MySQL, karena secara umum sintaks SQL di semua RDBMS sama. ###Anatomi Perintah SQL SELECT (SQL Anatomy) Perintah SQL memiliki struktur dasar yang harus kita pahami agar kita bisa membuat sebuat perintah SQL yang baik dan benar. Perintah SQL terdiri dari beberapa bagian. Terminologi berikut ini tidak perlu dihapal tapi harus diketahui dan dipahami, yaitu Statement Clause Predicate Expression <div style="clear:both"></div> Statement, atau biasa disebut sebagai query, adalah keseluruhan perintah itu sendiri, sedangkan Clause, adalah potongan perintah yang diawali kata kunci SQL tertentu, seperti berikut ini, SELECT cluase, SET clause, UPDATE clause, WHERE clause, JOIN clause dan sebagainya. Predicate, adalah sebuah kalimat matematika berupa perbandingan antara sebuah field dengan field lain, atau perbandingan antara sebuah field dengan nilai tertentu. Expression, adalah sebuah kalimat matematika yang menghasilkan sebuah nilai tertentu, atau sebuah konstanta nilai tertentu, misalkan string 'OK' atau perhitungan seperti ini NOW()+10 Jadi jika sama2 kita bedah perintah ini <pre>SELECT emp_code, emp_name FROM employees WHERE salary >= 3000000</pre> Maka, ini adalah SELECT clause, SELECT emp_code, emp_name Ini adalah FORM clause, FROM employees Ini adalah WHERE clause WHERE salary >= 3000000 Ini adalah predicate salary >= 3000000 Dan ini adalah expression 3000000 ###Pemetaan Perintah SQL SELECT Variasi paling banyak dan akan sering kita gunakan adalah perintah SQL SELECT, karena perintah SQL SELECT ini akan sangat membantu kita dalam membuat sebuah report yang paling rumit sekalipun. Dan dalam sebuah SQL SELECT, setiap clause memiliki fungsinya sendiri sendiri..... Kita ketahui bersama bahwa konsep RDBMS menyimpan data dalam bentuk baris dan kolom. Jika tabel di database kita representasikan seperti kotak di gambar di bawah, maka <div style="clear:both"></div> FROM clause menentukan "kotak" mana yang ingin kita baca SELECT clause menentukan kolom mana yang kita ambil datanya, di gambar direpresentasikan kolom berwarna merah WHERE clause menentukan baris mana yang ingin kita ambil datanya..... peratikan pada gambar di atas direpresentasikan sebagai baris berwarna biru Dan jika kita menggabungkan ketika clause tersebut SELECT ... FROM ... WHERE .... maka kita akan mendapatkan data yang berwarna hijau Jadi.... hati-hati ketika kita menjalankan perintah UPDATE mytable SET mycolumn = NULL .......... tanpa menggunakan WHERE clause, maka akibatnya adalah semua row di tabel bersangkutan akan diubah, coba bayangkan jiika yang dijalankan adalah perintah DELETE tanpa WHERE clause..... yakinlah bahwa semua data tersebut akan lenyap dan kita akan dimarahi boss habis-habisan.... hehehe. ###JOIN vs UNION Setelah kita melewati proses analisa dan desain database, dan kemudian melakukan proses normalisasi, maka data kita akan tersimpan dalam beberapa buah table yang terpisah. Untuk menggabungkannya kembali menjadi sebuah informasi yang bermakna maka kita perlu melakukan salah satu dari dua proses ini, JOIN, atau UNION. <div style="clear:both"></div> Kadang2 ada yang bertanya apa bedanya antara JOIN dengan UNION dan kapan menggunakannya? Jika kita mempunyai dua buah table, satu berwarna biru, dan satu lagi berwarna merah, maka JOIN akan menghasilkan table baru dengan kolom yang merupakan gabungan dari kedua tabel tersebut, jadi yang bertambah adalah kolom-nya Sedangkan UNION justru menambahkan baris row, menggabungkan isi kedua tabel tersebut, dengan syarat derajat kedua buah table tersebut haruslah sama, bahasa bodohnya, jumlah kolom dan tipe datanya kedua table tersebut harus sama. Intinya, JOIN menambah jumlah kolom/field, sedangkan UNION menambah jumlah row. UNION biasanya digunakan untuk menggabungkan beberapa tabel untuk dibuatkan sebuah laporan tertentu, contoh paling simple adalah jika kita membuat aplikasi inventory, kemudian menyimpan transaksi barang masuk dan transaksi barang keluar ke dalam dua tabel yang berbeda, maka..... UNION jadi solusi saat kita perlu membuat laporan kartu stok Lalu apa bedanya UNION dengan UNION ALL....? Perbedaannya adalah UNION memastikan tidak ada row yang duplikat, sedangkan UNION ALL memperbolehkan adanya row duplikat. Pada saat proses UNION, RDBMS memastikan tidak ada row yang duplikat dengan cara mengurutkan result set, dan satu-persatu menghapus row duplikat dari result set. Jadi..... ketika kita sudah yakin, haqqul yakin, bahwa tidak ada data duplikat dalam dua buah table yang ingin kita gabungkan, maka gunakan saja UNION ALL.... secara performance akan lebih baik karena RDBMS tidak perlu melakukan sorting untuk menghapus row duplikat Menggabungkan dua atau lebih table menggunakan JOIN cukup sederhana, namun ada sejumlah variasi JOIN yang sebaiknya kita ketahui. seperti berikut ini, INNER JOIN (JOIN) LEFT OUTER JOIN (LEFT JOIN) RIGHT OUTER JOIN (RIGHT JOIN) FULL OUTER JOIN (FULL JOIN) LEFT EXCLUDING JOIN (LEFT JOIN exclude INNER JOIN) RIGHT EXCLUDING JOIN (RIGHT JOIN exclude INNER JOIN) FULL EXCLUDING JOIN (FULL JOIN exclude INNER JOIN) Sekedar informasi, bahwa sampai saat artikel ini ditulis, khusus MySQL (dan MariaDB), sampai saat ini belum mendukung sintaks FULL JOIN. Dan seperti yang sudah sempat saya sebutkan di artikel sebelumnya, bahwa paradigma berpikir di database adalah "set" atau himpunan, maka representasi paling pas untuk menggambarkan proses JOIN antar tabel adalah dengan menggunakan diagram venn yang dulu kita pelajadi waktu masih imut-imut. Lebih mendalam tentang JOIN, silakan pelajari artikel tentang Representasi SQL JOIN secara Visual. Lalu bagaimana penggunaannya? Kapan kita pakai INNER JOIN, LEFT JOIN, RIGHT JOIN, atau FULL JOIN? Kasus yang bisa diselesaikan dengan INNER JOIN misalnya "tampilkan semua mahasiswi yang mengambil kuliah di semester ini" Kasus yang bisa diselesaikan dengan LEFT JOIN misalnya "tampilkan semua mahasiswi yang mengambil kuliah di semester ini, termasuk mahasiswi yang cuti di semester ini" Kasus yang bisa diselesaikan dengan RIGHT JOIN misalnya "tampilkan semua mata kuliah yang diambil mahasiswi di semester ini, termasuk mata kuliah yang tidak laku" Kasus yang bisa diselesaikan dengan FULL JOIN misalnya "tampilkan semua mahasiswi yang mengambil kuliah di semester ini, termasuk mahasiswi yang cuti dan mata kuliah yang tidak laku" ###SQL Rule of Thumb Ketika kita menggunakan RDBMS dalam membangun sebuah aplikasi, maka ada sebuah Rule of Thumb (Aturan Dasar) yang wajib diikuti. Rule of Thumb ini disusun oleh Tom Kyte seorang Oracle Database Evangelist, Aturannya adalah sebagai berikut Use single SQL statement whenever possible Use PL/SQL or Stored Procedure Use Java (or other programming language) Rethink why you want to do it (refine your approach) Terjemahan bebasnya Sebisa mungkin gunakan satu perintah SQL Jika gak bisa, gunakanlah stored procedure Jika masih gak bisa, gunakan bahasa Java (atau PHP) Jika masih gak bisa juga, berarti ada yang salah nih <div style="clear:both"></div> ###Tantangan Tampilkan angka 1 sampai dengan 100, dengan persyaratan sebagai berikut, namun setiap kelipatan 3 ubah angkanya menjadi kata "Rumah", setiap kelipatan 5 ubah angkanya menjadi kata "Sakit", dan setiap kelipatan 15 ubah angkanya menjadi kata "Rumah Sakit". Jika kita mengikuti SQL Rule Of Thumb di atas, maka kita harus dapat membuat solusinya dalam satu perintah SQL saja. Solusinya adalah kita harus dapat menemukan - atau membuat - sebuah set, atau himpunan, atau table yang berisikan minimal 100 anggota, atau row. Kemudian kita memberikan nomor mulai dari 1 sampai 100 kepada setiap member himpunan tersebut. Dan akhirnya kita gunakan logika percabangan CASE untuk menetukan apakah anggota himpunan tersebut merupakan kelipatan 3, 5, atau 15. Solusi menggunakan MySQL, kita menggunakan variable untuk mebuat nomor baris, adalah sebegai berikut <pre>select case when baris mod 15 = 0 then 'Rumah Sakit' when baris mod 3 = 0 then 'Rumah' when baris mod 5 = 0 then 'Sakit' else baris end hasil from ( select @row := @row+1 baris from information_schema.columns join ( select @row := 0 ) rx limit 100 ) tx</pre> Subquery tx digunakan untuk men-generate table berisikan angka 1 sampai 100 <pre> select @row := @row+1 baris from information_schema.columns join ( select @row := 0 ) rx limit 100</pre> Dan Solusi menggunakan PostgreSQL, jauh lebih sederhana, adalah sebagai berikut <pre>select case when baris %15 = 0 then 'Rumah Sakit' when baris %3 = 0 then 'Rumah' when baris %5 = 0 then 'Sakit' else to_char(baris,'999') end hasil from ( select generate_series(1,100) baris ) tx</pre> Perhatikan bahwa di PostgreSQL ada fungsi khusus untuk men-generate angka yang kita inginkan, sehingga query jauh lebih simple dan sederhana. ###Simpulan Semoga setelah mempelajari artikel ini, teman-teman bisa memahami Struktur dan Anatomi sebuah perintah SQL, dan mengetahui kapan sebaiknya penggunaan JOIN yang tepat, sehingga kita sebagai developer bisa membuat sebuah aplikasi database yang berkualitas. Artikel selanjutnya masih akan membahas SQL SELECT namun lebih canggih lagi dengan memanfaatkan fungsi Aggregate, Windowing, dan Analitical Function. .