#doitwithsql

Beberapa hari yang lalu, saya mengikuti salah satu program dari komunitas PHP Indonesia, yaitu PHP Indonesia For Student. Banyak manfaat yang bisa saya dapatkan semenjak mengikuti program ini. Ya, salah satunya adalah mendapatkan ilmu melalui kuliah telegram. Kuliah telegram ini bisa diikuti setiap hari lho! Dan tiap hari akan membahas tema yang berbeda-beda.  Dan tema kuliah telegram kemarin mengusung tema #DoItWithSQL. Dan sudah pasti materi yang dibahas di perkuliahan kali ini adalah SQL. Salah satu skill yang sangat penting untuk seorang developer. Ya, postingan kali ini adalah hasil dari kuliah telegram #DoItWithSQL kemarin. Ok, kita mulai!

Ya, seperti yang sudah disebutkan sebelumnya, tema kuliah telegram ini adalah #DoItWithSQL. Dan pemateri keren yang sudah meluangkan waktu di tengah kesibukannya untuk menyampakan materi adalah Om Nur Hidayat. Beliau adalah seorang developer di PT Cronos Studio Indonesia. Dan berikut ini adalah rangkuman Kuliah Telegram #DoItWithSQL.

***

Sebagai seorang developer harus tahu semua teknologi, walaupun sedikit, namun tetaplah punya satu atau dua spesialisasi tertentu. Salah satunya adalah skill SQL. Pemahaman yang baik tentang structured query language (SQL) menjadi hal yang amat sangat penting karena SQL adalah bahasa yang kita gunakan untuk berkomunikasi dengan database.

Sayangnya kenyataan berkata lain, peningkatan skill SQL menjadi prioritas paling akhir karena kebanyakan programmer terlalu fokus pada hal lain yang lebih seksi, seperti:

- meningkatkan skill bahasa pemrograman tertentu,

- membuat user interface yang atraktif dan user-friendly,

- membuat aplikasi yang bisa berjalan di database apapun

Akibatnya muncul kelucuan-kelucuan seperti berikut ini:

#Kelucuan Pertama, kebanyakan programmer menjadi kurang piawai meracik SQL. Pengetahuan tentang SQL statement hanya sebatas perintah SELECT, INSERT, UPDATE, dan DELETE sederhana saja tanpa memanfaatkan JOIN. Sehingga SQL statement yang dibuat terkesan asal jadi.

#Kelucuan Kedua, kebanyakan programmer tidak memanfaatkan kemampuan canggih yang dimiliki database engine yang digunakan. Sebagai contoh adalah hanya memanfaatkan database sebagai data store saja, dan proses pengolahan data seluruhnya dilakukan di level aplikasi. Padahal seharusnya pemrosesan data bisa diserahkan ke database dengan memanfaatkan aggregate functions, analytical/windowing functions, dan stored procedures.

#Kelucuan Ketiga, membawa gaya pemrograman prosedural saat memproses data. sebagai contoh untuk memproses data besar masih menggunakan looping (perulangan) di level aplikasi, akibatnya kinerja aplikasi semakin menurut seiring dengan bertambah banyaknya data. Padahal seharusnya saat kita memproses data di database harus menggunakan paradigma berpikir set (himpunan) dan bulk processing, bukan cara prosedural.

Untuk mengatasi semua #kelucuan di atas, kita sebagai developer harus paham perintah SQL luar dalem 😊. Untuk itu kita perlu membedah isi perintah SQL untuk memahami anatomi-nya.

[1] Anatomi SQL

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:

[a] Statement, atau biasa disebut sebagai query, adalah keseluruhan perintah itu sendiri.

[b] Clause, adalah potongan perintah yang diawali kata kunci SQL tertentu, seperti....

- SELECT cluase,

- SET clause,

- UPDATE clause,

- WHERE clause,

- JOIN clause

dan sebagainya.

[c] Predicate, adalah sebuah kalimat matematika berupa perbandingan antara sebuah field dengan field lain, atau perbandingan antara sebuah field dengan nilai tertentu.

[d] Expression, adalah sebuah kalimat matematika yang menghasilkan sebuah nilai tertentu, atau sebuah konstanta nilai tertentu, misalkan string 'OK', atau NOW()+10.

Jika sama2 kita bedah perintah ini

---------

SELECT emp_code, emp_name

FROM employees

WHERE salary >= 3000000

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

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.

Dalam sebuah SQL SELECT, setiap clause memiliki fungsinya sendiri sendiri..... kita ketahui bahwa konsep RDBMS menyimpan data dalam bentuk baris dan kolom. Jika tabel di database kita representasikan seperti kotak di gambar di atas, maka FROM clause menentukan "kotak" mana yang ingin kita baca.

[a] SELECT clause menentukan kolom mana yang kita ambil datanya, di gambar direpresentasikan kolom berwarna merah.

[b] WHERE clause menentukan baris mana yang ingin kita ambil datanya..... peratikan pada gambar di atas direpresentasikan sebagai baris berwarna biru.

[c] 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 WHERE clause, maka akibatnya adalah semua row di tabel bersangkutan akan diubah

.......... coba bayangkan jika yang dijalankan adalah perintah DELETE tanpa WHERE clause.. lenyaplah semua data. 😅

OK..... sampai di sini masih bisa dipahami ya?

Kita akan lanjut ke topik berikutnya kalau gak ada pertanyaan atau komentar tentang #AnatomiSQL

[2] Join

Kadang-kadang 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 jumlah kolom harus sama dan tipe datanya juga harus sama.

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. Bedanya 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.

[3] Variasi SQL Join

Ini pengkategorian dari saya saja, ada 4 variasi Standard JOIN:

- INNER JOIN (JOIN)

- LEFT OUTER JOIN (LEFT JOIN)

- RIGHT OUTER JOIN (RIGHT JOIN)

- FULL OUTER JOIN (FULL JOIN)

]

Dan Ada 3 macam variasi Advanced 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)

Khusus MySQL (dan MariaDB), sampai saat ini belum support FULL JOIN.

Seperti yang sudah disebutkan dalam Intro, 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.

[*] INNER JOIN adalah query yang paling sederhana, paling umum, dan paling mudah dipahami. Query ini akan mengembalikan semua record dalam tabel kiri (tabel A) yang memiliki record yang cocok di tabel kanan (tabel B).

[*] Query LEFT JOIN akan mengembalikan semua record dalam tabel kiri (tabel A) meskipun record di tabel A tersebut tidak memiliki record yang bersesuaian di tabel kanan (tabel B). Dan semua record yang bersesuaian dari tabel kanan akan muncul.

[*] Query RIGHT JOIN akan mengembalikan semua record dalam tabel kanan (tabel B) meskipun record di tabel B tersebut tidak memiliki record yang bersesuaian di tabel kiri (tabel A). Semua record yang bersesuaian dari tabel kanan akan muncul.

[*] Query FULL JOIN akan mengembalikan semua record yang ada di kedua table tersebut, dan semua record yang bersesuaian akan digabungkan menjadi satu record.

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".

Untuk sintaks masing2 JOIN bisa dipelajari [di artikel ini].

Lalu ada yang bertanya, "beda FULL JOIN dengan INNER JOIN dimananya yak? Lalu, bolehkah hanya menggunakan JOIN saja? Tanpa ada embel-embel INNER?".

Lengkapnya:

INNER JOIN bisa disingkat JOIN.

LEFT OUTER JOIN bisa disingkat LEFT JOIN.

RIGHT OUTER JOIN bisa disingkat RIGHT JOIN.

FULL OUTER JOIN bisa disingkat FULL JOIN.

Mengenai sintaks detil dan contohnya bisa dilihat di link yang saya berikan di atas.

...

Mudah-mudahan materi di atas tidak menjemukan.... karena kita akan masuk ke topik yang makin mendorong... eh... menarik.

Lalu ada yang bertanya lagi. Kalau yang sudah dibaca di link yang sudah disebutkan sebelumya, dibagian INNER JOIN dijelaskan bahwa:

INNER JOIN

Query ini akan mengembalikan semua record dalam tabel kiri (tabel A) yang memiliki record yang cocok di tabel kanan (tabel B)

FULL JOIN

mengembalikan semua record yang ada di kedua table tersebut, dan semua record yang bersesuaian akan digabungkan menjadi satu record.

Inner join itu nampilin isi tabel A yang bersesuaian dengan tabel B.

Sedangkan full join nampilin semua tabel A digabung dengan semua tabel B.

...

[4] Rule Of Thumb

RuleOfThumb From Tom Kyte, Oracle Database Evangelist:

1. Use single SQL statement whenever possible

2. Use PL/SQL or Stored Procedure

3. Use Java (or other programming language)

4. Rethink why you want to do it (refine your approach)

Terjemahan bebasnya:

1. Sebisa mungkin gunakan satu perintah SQL

2. Jika gak bisa, gunakanlah stored procedure

3. Jika masih gak bisa, gunakan bahasa Java (atau PHP)

4. Jika masih gak bisa juga, berarti ada yang salah nih

Lalu, seperti apa contoh penggunaan #RuleOfThumb? Di kuliah telegram kali ini om Nur memberikan tantangan, yaitu: Gunakan #RuleOfThumb Tampilkan angka 1 sampai dengan 100, 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".

Dan setelah diskusi yang sangat menarik, inilah solusi dengan menggunakan #MySQL:

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 Subquery tx select @row := @row+1 baris from information_schema.columns join ( select @row := 0 ) rx limit 100

Digunakan untuk men-generate table berisikan angka 1 sampai 100.

Dan ini adalah solusi dengan menggunakan #PosgreSQL :

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

Perhatikan bahwa di postgres ada fungsi khusus untuk men-generate angka yang kita inginkan, sehingga query jauh lebih simple.

***

Gimana? Menarik bukan? Sebagai seorang developer, kita memang harus tahu semua teknologi. Walaupun sedikit, namun tetaplah kita harus memiliki satu atau dua spesialisasi tertentu. Salah satunya adalah skill SQL. Pemahaman yang baik tentang structured query language (SQL) menjadi hal yang amat sangat penting karena SQL adalah bahasa yang kita gunakan untuk berkomunikasi dengan database. Dengan memahami SQL, kita dapat mengurangi kelucuan-kelucuan saat berinteraksi dengan database dan membuat program yang lebih baik lagi.