![Ebook - Killer Trik Query MySQL [PDF]](https://pdfs.asia/img/200x200/ebook-killer-trik-query-mysql.jpg)
15 0 4 MB
EKSKLUSIF
KILLER TRIK QUERY MySQL Kupas Tuntas Teknik Olah Data Dengan Query MySQL
SQL Agus Prawoto Hadi Killer Trik Query MySQL
i
Killer Trik Query MySQL – Kupas Tuntas Teknik Olah Data Dengan Query MySQL Penulis : Agus Prawoto Hadi Edisi : I (Pertama) Terbit : Juli 2017 Dimensi : 14,5 cm x 21 cm Jumlah Halaman: xiv + 347 Hak cipta ada di penulis, dilarang menyalin dan mempublikasikan sebagian maupun keseluruhan isi dari buku ini tanpa ijin penulis
Kata Pengantar Alhamdulillah, penulis panjatkan kehadirat Allah S.W.T, karena
dengan
rahmat
dan
hidayah-Nya
penulis
dapat
menyelesaikan buku "Killer Trik Query MySQL – Mengungkap Rahasia Master SQL.". Ketika bekerja dengan aplikasi, kita tidak bisa lepas dari penyajian data dari database. Bentuk penyajian ini beragam, mulai dari yang paling sederhana hingga yang kompleks sesuai dengan informasi yang ingin disajikan. Hal ini, bagi sebagian orang sulit dilakukan karena minimnya referensi yang tersedia. Buku ini akan memberikan pemahaman kepada Anda bagaimana menyusun query dengan benar sehingga ketika menghadapi berbagai permasalahan terkait penyajian data, Anda akan langsung tahu bagaimana cara menyelesaikannya Penulis menyadari bahwa buku ini masih jauh dari sempurna, untuk itu penulis menerima segala kritik dan saran yang membangun untuk perbaikan di versi berikutnya. Kritik dan saran dapat dikirim ke alamat email penulis di [email protected] Akhir kata semoga buku ini dapat membawa manfaat bagi pembaca. Kudus, Juli 2017
Penulis
Killer Trik Query MySQL
iii
Istilah Penting Statemen, Klausa, dan Keyword Pada buku ini, Anda akan sering menemui beberapa istilah terkait dengan query SQL diantaranya adalah: Statemen, Klausa, dan terkadang keyword, apa beda ketiganya?
Statemen Statemen adalah sebuah Query SQL utuh yang dapat dieksekusi dengan baik. Sebuah statemen biasanya diakhiri dengan tanda titik koma (;). Tanda ini disebut delimiter. Jika query hanya terdiri dari satu statemen, penggunaan delimiter ini bersifat opsional, sedangkan jika query terdiri dari lebih dari satu statemen, delimiter ini harus digunakan. Umumnya Query terdiri hanya satu statemen, namun pada kondisi tertentu, Query dapat terdiri dari lebih dari satu statemen. Contoh statemen: statemen SELECT, statemen INSERT, dll
Klausa Klausa merupakan bagian bagian tertentu dari statemen, sehingga cakupnnya nya lebih sempit, misal klausa FROM, klausa SELECT, dst
Keyword Keyword merupakan kata yang digunakan oleh MySQL yang mewakili fungsi tertentu, misal COUNT, SELECT, SUM, dll. Dalam buku ini, terkadang kita menyebut keyword dengan klausa.
iv
Istilah Penting
Perbedaan ketiga istilah diatas dapat dilihat pada gambar berikut ini:
Gambar: Ilustrasi Statemen, Klausa, dan Keyword
Dengan memahami istilah tersebut diatas, Anda akan lebih mudah untuk memahami pembahasan yang ada di dalam buku ini
Killer Trik Query MySQL
v
Coding Style Ketika menulis syntax SQL, kita bebas menggunakan gaya masing masing, tidak ada standar pola penulisan yang baku, namun demikian, agar syntax mudah dibaca dan dipahami, maka sangat disarankan untuk menuliskannya dengan rapi. Agar syntax SQL terlihat rapi,
gunakan spasi, tab, dan enter
secukupnya, sehingga dengan mudah dibedakan mana statemen, klausa, dan keyword. Didalam query SQL, kita bebas menggunakan spasi dan line break dimana saja, karena ketika query tersebut dieksekusi, spasi dan line break (enter/baris baru) akan diabaikan. Contoh penulisan syntax yang baik: 1. 2. 3. 4. 5. 6. 7. 8.
SELECT b.kd_barang, b.nama_barang, SUM(p.total_trx) AS total_pnjualan FROM barang AS b LEFT JOIN penjualan_detail AS pd USING(kd_barang) LEFT JOIN penjualan AS p USING(id_trx) WHERE YEAR(tgl_trx) = 2017 GROUP BY kd_barang
Pada contoh query diatas, perhatikan penggunaan tab, spasi , dan line break. Perhatikan juga penggunaan tab pada klausa selain keyword utama (SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT, dan HAVING) Khusus penulisan nama kolom pada klausa SELECT, jika nama kolom dipisahkan dengan baris, maka ada dua cara punisan koma, yaitu (1) koma dibelakang nama kolom, seperti pada contoh diatas, dan (2) koma di depan nama kolom seperti contoh berikut:
vi
Coding Style
1. 2. 3. 4.
SELECT b.kd_barang , b.nama_barang , SUM(p.total_trx) AS total_pnjualan FROM ...
Kedua cara diatas sering digunakan oleh para pengembang SQL, cara pertama telihat lebih rapi, namun ketika ingin menghilangkan baris terakhir, kita juga harus menghapus koma diatasnya, karena sebelum klausa FROM tidak boleh ada koma, sedangkan cara kedua, kita hanya perlu menghapus kolom yang ingin dihilangkan, tidak perlu khawatir kelebihan koma. Perhatikan ilustrasi berikut:
Gambar: Ilustrasi gaya penulisan klausa SELECT
Agar familiar dengan keduanya, pada buku ini kita akan menggunakan keduanya secara bergantian.
Killer Trik Query MySQL
vii
Source Code Buku ini disertai file dump database yaitu tutorial_trik.sql, silakan load file tersebut menggunakan aplikasi database manager seperti HeidiSQL atau phpMyAdmin. Setelah di load, akan terbentuk database tutorial_trik, di sepanjang buku ini, kita akan sering menggunakan database tersebut untuk latihan. Selain file sql, juga disertakan file .php. File ini merupakan source code script PHP yang digunakan pada pembahasan BAB 15 Table Reporting Dengan PHP dan MySQL:. Untuk menggunakannya, silakan letakkan file di dalam folder htdocs kemudian akses file tersebut melalui browser. File php tersebut menggunakan konfigurasi koneksi server: localhost, user: root, password: '', jika konfigurasi server MySQL yang Anda gunakan berbeda, edit tiap tiap file .php sebelum dijalankan
viii
Source Code
Daftar Isi Kata Pengantar ............................................................................................................. iii Istilah Penting ................................................................................................................ iv Coding Style..................................................................................................................... vi Source Code.................................................................................................................. viii Daftar Isi ........................................................................................................................... ix BAB 1 Statemen SELECT............................................................................................. 1 1.1. Syntax Statemen SELECT .............................................................................. 1 1.2. Operator Aritmetika........................................................................................ 9 1.3. Operator Perbandingan ............................................................................. 12 1.4. Nilai NULL ....................................................................................................... 12 1.5. Operator OR dan AND ................................................................................. 14 BAB 2 Kunci Menguasai SELECT .......................................................................... 21 2.1. Memahami Urutan Eksekusi..................................................................... 21 2.2. Konsekuensi Urutan Eksekusi ................................................................. 33 2.3. Identifikasikan Bentuk Tabel ................................................................... 39 2.4. Pendefinisian Kolom Baru ......................................................................... 43 2.5. Eksekusi Baris................................................................................................. 45 2.6. Pembatas Tampilan Data ........................................................................... 46 2.7. Poin Penting..................................................................................................... 46 BAB 3 Ekspresi Logika.............................................................................................. 49 3.1. Ekspresi IF ........................................................................................................ 49 Killer Trik Query MySQL
ix
3.2. Ekspresi CASE ................................................................................................. 52 BAB 4 Menguasai JOIN.............................................................................................. 57 4.1. JOIN...................................................................................................................... 58 4.2. USING atau ON ................................................................................................ 59 4.3. LEFT JOIN.......................................................................................................... 60 4.4. RIGHT JOIN ....................................................................................................... 61 4.5. Hal Penting Tentang JOIN .......................................................................... 63 BAB 5 Menguasai Fungsi Agregasi ...................................................................... 67 5.1. Memahami Cara Kerja Fungsi Agragasi ............................................... 67 5.2. MAX, MIN, dan AVG....................................................................................... 70 5.3. COUNT dan SUM ............................................................................................ 73 5.4. SUM Dengan IF ............................................................................................... 75 5.5. COUNT Dengan CASE ................................................................................... 80 5.6.
Penggabungan Fungsi Agregasi Dengan Ekspresi Logika .... 84
5.7. Studi Kasus: COUNT IF ................................................................................ 90 BAB 6. Menguasai Fungsi Scalar ........................................................................ 103 6.1. Memahami Cara Kerja Fungsi Scalar...................................................103 6.2. Fungsi String..................................................................................................105 6.3. Fungsi Date Time .........................................................................................108 6.4. Fungsi Beda Waktu .....................................................................................115 6.5. Fungsi Numeric ............................................................................................118 BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika ................. 123
x
Daftar Isi
7.1. Fungsi Scalar Pada WHERE..................................................................... 123 7.2. Fungsi Scalar Pada GROUP BY ............................................................... 125 7.3. Studi Kasus ..................................................................................................... 127 BAB 8 Mempertajam Agregasi dan JOIN ........................................................ 133 8.1. Studi Kasus #1 .............................................................................................. 133 8.2. Studi Kasus #2 .............................................................................................. 137 8.3. Studi Kasus #3 .............................................................................................. 142 8.4. Studi Kasus #4 .............................................................................................. 147 8.5. Studi Kasus #5 .............................................................................................. 152 BAB 9 Menguasai SUBQUERY............................................................................ 159 9.1. Subquery Sebagai Kolom ......................................................................... 159 9.2 Subquery Sebagai Data .............................................................................. 162 BAB 10 Menguasai UNION ................................................................................... 171 10.1. Memahami UNION dan UNION ALL .................................................. 172 10.2. Studi Kasus #1 ........................................................................................... 184 10.3. Studi Kasus #2 ........................................................................................... 190 10.4. Studi Kasus #3 ........................................................................................... 194 BAB 11 Menguasai WITH ROLLUP ................................................................... 197 11.1. Memahami Cara Kerja WITH ROLLUP............................................. 197 11.2. Mengganti Nilai NULL ............................................................................. 201 11.3. ORDER BY Pada WITH ROLLUP ......................................................... 203 11.4. Studi kasus................................................................................................... 208
Killer Trik Query MySQL
xi
BAB 12. Menguasai Variable ............................................................................... 211 12.1. Memahami Variable .................................................................................211 12.2. Single Statement........................................................................................213 12.3. Studi Kasus #1............................................................................................216 12.4. Studi Kasus #2............................................................................................217 BAB 13 Menguasai DATE TIME ......................................................................... 221 13.1. Studi Kasus #1............................................................................................221 13.2. Studi Kasus #2............................................................................................225 13.3. Studi Kasus #3............................................................................................227 13.4. Studi Kasus #4............................................................................................232 13.5. Studi Kasus #5............................................................................................239 BAB 14 Table Reporting ....................................................................................... 243 14.1. Table Report I .............................................................................................243 14.2. Table Report II ...........................................................................................252 14.3. Table Report III..........................................................................................262 14.4. Keterbatasan ...............................................................................................272 BAB 15 Table Reporting Dengan PHP dan MySQL .................................... 275 15.1. Query MySQL Dengan PHP ...................................................................275 15.2. Table Reporting I ......................................................................................279 15.3. Table Reporting II .....................................................................................295 15.3. Table Reporting III ...................................................................................306 15.4. Table Reporting IV ...................................................................................321
xii
Daftar Isi
15.5. Lebih Lanjut…............................................................................................. 346 Next Step ..................................................................................................................... 347
Killer Trik Query MySQL
xiii
Halaman ini sengaja dikosongkan Jagowebdev.com
xiv
Daftar Isi
BAB 1 Statemen SELECT Statemen SELECT digunakan untuk mengambil data dari database. Statemen ini merupakan statemen utama SQL karena sebagian besar query yang kita lakukan adalah pengambilan data, disamping itu statemen ini memiliki variasi paling banyak dibanding statemen lain seperti INSERT dan UPDATE. Pada BAB ini kita akan membahas syntax dasar statemen SELECT beserta variasinya, dengan memahami topik ini, Anda akan dapat dengan mudah memahami pembahasan pada bab bab berikutnya.
1.1. Syntax Statemen SELECT Statemen SELECT memiliki struktur sebagai berikut: SELECT ... FROM ... [WHERE ...] [GROUP BY ...] [ORDER BY ...] [HAVING ...] [LIMIT ...]
Penulisan klausa diatas harus urut mulai dari atas ke bawah. Keyword SELECT dan FROM wajib ada, sedangkan keyword lain (yang ada didalam tanda kurung siku [] ) bersifat opsional. Contoh penggunaan statemen SELECT: misal kita memiliki tabel mhs dengan data sebagai berikut: +------+---------+---------------+------------+ | nim | nama | jenis_kelamin | kd_jurusan | +------+---------+---------------+------------+ | 001 | Alfa | L | J002 | | 002 | Beta | P | J002 |
Killer Trik Query MySQL
1
| 003 | Charlie | P | J001 | | 004 | Delta | L | J001 | | 005 | Erdhi | L | J001 | | 006 | Farah | P | J002 | | 007 | Gisel | P | J002 | | 008 | Haris | L | NULL | +------+---------+---------------+------------+
Untuk mengambil semua kolom, kita gunakan tanda asterik (*) misal: 1.
SELECT * FROM mhs
Hasil: +------+---------+---------------+------------+ | nim | nama | jenis_kelamin | kd_jurusan | +------+---------+---------------+------------+ | 001 | Alfa | L | J002 | | 002 | Beta | P | J002 | | 003 | Charlie | P | J001 | | 004 | Delta | L | J001 | | 005 | Erdhi | L | J001 | | 006 | Farah | P | J002 | | 007 | Gisel | P | J002 | | 008 | Haris | L | NULL | +------+---------+---------------+------------+
Untuk menampilkan kolom tertentu, kita tulis nama kolom tersebut pada klausa SELECT, misal: 1.
SELECT nama, jenis_kelamin FROM mhs;
Hasil +---------+---------------+ | nama | jenis_kelamin | +---------+---------------+ | Alfa | L | | Beta | P | | Charlie | P | | Delta | L | | Erdhi | L | | Farah | P | | Gisel | P |
2
BAB 1 Statemen SELECT
| Haris | L | +---------+---------------+
Usahakan hanya mengambil kolom yang diperlukan, hindari penggunaan asterik. Hal ini merupakan "Best Practice" untuk meningkatkan efisiensi performa sql. WHERE Klausa WHERE digunakan untuk memfilter data yang ingin diambil, misal kita akan mengambil data siswa dengan jenis kelamin laki-laki, query yang kita jalankan adalah sebagai berikut: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. WHERE jenis_kelamin = "L"
Hasil: +-------+---------------+ | nama | jenis_kelamin | +-------+---------------+ | Alfa | L | | Delta | L | | Erdhi | L | | Haris | L | +-------+---------------+
Ketika menjalankan klausa WHERE, MySQL akan memeriksa baris pada tabel satu persatu sehingga pada tabel dengan jumlah baris banyak, proses ini akan memakan waktu cukup lama, untuk mengatasinya, gunakan teknik indexing (penggunaan index) - tidak di bahas pada buku ini-. GROUP BY GROUP BY digunakan untuk mengelompokkan baris berdasarkan kolom tertentu.
Killer Trik Query MySQL
3
Penggunaan keyword ini selalu bersamaan dengan fungsi agregsi seperti COUNT, SUM, MIN, MAX, dan AVG Contoh: 1. 2. 3. 4.
SELECT kd_jurusan, COUNT(kd_jurusan) FROM mhs WHERE jenis_kelamin = "L" GROUP BY kd_jurusan
Pada
contoh
diatas,
data
dihitung
(fungsi
COUNT)
dan
dikelompokkan berdasarkan kolom kd_jurusan. Jika Anda belum paham mengenai fungsi COUNT, tidak usah khawatir, kita akan membahasnya secara detail pada bab berikutnya. Penting diperhatikan bahwa sebelum menjalankan GROUP BY, terlebih dahulu MySQL akan mengurutkan data pada kolom yang ada pada klausa GROUP BY secara ascending, (hal ini penting untuk diingat) Sebagai contoh, jika kita jalankan query diatas, maka hasil yang kita peroleh adalah: +------------+-------------------+ | kd_jurusan | COUNT(kd_jurusan) | +------------+-------------------+ | NULL | 0 | | J001 | 2 | | J002 | 1 | +------------+-------------------+
Pada tabel diatas terlihat bahwa data diurutkan berdasarkan kolom kd_jurusan secara ascending
4
BAB 1 Statemen SELECT
ORDER BY Keyword ORDER BY digunakan untuk mengurutkan data baik secara ascending (dari kecil ke besar) maupun descending (dari besar ke kecil) Keyword ini akan dijalankan setelah MySQL menjalankan klausa GROUP BY (jika ada) yang artinya setelah data dikelompokkan, contoh: 1. 2. 3. 4. 5.
SELECT kd_jurusan, COUNT(kd_jurusan) FROM mhs WHERE jenis_kelamin = "L" GROUP BY kd_jurusan ORDER BY kd_jurusan DESC
Hasil: +------------+-------------------+ | kd_jurusan | COUNT(kd_jurusan) | +------------+-------------------+ | J002 | 1 | | J001 | 2 | | NULL | 0 | +------------+-------------------+
Pada contoh diatas, data diurutkan berdasarkan kolom kd_jurusan secara descending (dari besar ke kecil). Jika ingin diurutkan secara ascending, hilangkan keyword DESC atau ganti dengan ASC Selain bersama dengan GROUP BY, keyword ORDER BY juga dapat digunakan tanpa GROUP BY, misal: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. ORDER BY nama
Killer Trik Query MySQL
5
Hasil: +---------+---------------+ | nama | jenis_kelamin | +---------+---------------+ | Alfa | L | | Beta | P | | Charlie | P | | Delta | L | | Erdhi | L | | Farah | P | | Gisel | P | | Haris | L | +---------+---------------+
Pada contoh diatas, data diurutkan berdasarkan kolom nama secara ascending. Dengan ORDER BY, kita dapat mengurutkan data dengan kriteria lebih dari satu kolom, misal: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. ORDER BY jenis_kelamin, nama
Hasil: +---------+---------------+ | nama | jenis_kelamin | +---------+---------------+ | Alfa | L | | Delta | L | | Erdhi | L | | Haris | L | | Beta | P | | Charlie | P | | Farah | P | | Gisel | P | +---------+---------------+
Pada contoh diatas, data diurutkan secara ascending berdasarkan kolom jenis_kelamin, selanjutnya pada jenis kelamin yang sama, data pada kolom nama diurutkan secara ascending.
6
BAB 1 Statemen SELECT
Untuk mengubah menjadi descending, kita dapat menambahkan keyword DESC pada masing masing kolom, misal: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. ORDER BY jenis_kelamin DESC, nama DESC
Hasil: +---------+---------------+ | nama | jenis_kelamin | +---------+---------------+ | Gisel | P | | Farah | P | | Charlie | P | | Beta | P | | Haris | L | | Erdhi | L | | Delta | L | | Alfa | L | +---------+---------------+
HAVING Keyword having sama seperti keyword WHERE, bedanya HAVING dapat dijalankan setelah klausa GROUP BY sedangkan WHERE tidak, contoh: 1. 2. 3. 4. 5.
SELECT kd_jurusan, COUNT(kd_jurusan) FROM mhs WHERE jenis_kelamin = "L" GROUP BY kd_jurusan HAVING kd_jurusan = "J002"
Hasil: +------------+-------------------+ | kd_jurusan | COUNT(kd_jurusan) | +------------+-------------------+ | J002 | 1 | +------------+-------------------+
Killer Trik Query MySQL
7
Having dijalankan setelah tabel diolah sehingga kita dapat menggunakannya untuk memfilter data hasil olahan, misal: 1. 2. 3. 4. 5.
SELECT kd_jurusan, COUNT(kd_jurusan) AS jml_mhs FROM mhs WHERE jenis_kelamin = "L" GROUP BY kd_jurusan HAVING jml_mhs > 0
Hasil: +------------+-------------------+ | kd_jurusan | COUNT(kd_jurusan) | +------------+-------------------+ | J001 | 2 | | J002 | 1 | +------------+-------------------+
LIMIT Keyword LIMIT digunakan untuk membatasi jumlah baris yang ingin ditampilkan, misal: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. LIMIT 5;
Hasil: +---------+---------------+ | nama | jenis_kelamin | +---------+---------------+ | Alfa | L | | Beta | P | | Charlie | P | | Delta | L | | Erdhi | L | +---------+---------------+
Pada contoh diatas, data diambil 5 teratas. Secara default LIMIT akan menghitung data mulai dari baris pertama, kita dapat menentukan
8
BAB 1 Statemen SELECT
mulai baris keberapa data diambil dengan menambahkan parameter offset, misal: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. LIMIT 3, 5;
Hasil: +-------+---------------+ | nama | jenis_kelamin | +-------+---------------+ | Delta | L | | Erdhi | L | | Farah | P | | Gisel | P | | Haris | L | +-------+---------------+
Pada contoh query diatas, nilai offsetnya adalah 3 Penghitungan offset dimulai dari 0, sehingga, pada contoh diatas, karena kita mengambil sebanyak 5 data dan dimulai dari offset ke 3 (LIMIT 3, 5) maka data diambil mulai data ke-4, jika ingin mengambil mulai dari data pertama, gunakan offset 0 (LIMIT 0, 5) ataucukup ditulis LIMIT 5
1.2. Operator Aritmetika Kolom pada klausa SELECT dapat diisi berbagai nilai termasuk ekspresi dengan operasi aritmetika seperti (x, /, + , dan - ), misal kita memiliki tabel penjualan_detail dengan data sebagai berikut: +----+--------+-----------+------+--------------+--------+ | id | id_trx | id_barang | qty | harga_satuan | diskon | +----+--------+-----------+------+--------------+--------+ | 1 | 1 | 9 | 1 | 46800 | 0 | | 2 | 1 | 4 | 1 | 34800 | 0 | | 3 | 1 | 6 | 2 | 33800 | 0.1 | | 4 | 2 | 9 | 1 | 46800 | 0 |
Killer Trik Query MySQL
9
| 5 | 2 | 10 | 2 | 39800 | 0 | +----+--------+-----------+------+--------------+--------+
Selanjutnya kita tampilkan data id_barang, qty, dan harga_satuan selain itu kita tampilkan data sub_total dengan mengalikan nilai pada kolom qty dengan harga_satuan. Query yang kita jalankan: 1. 2.
SELECT id, qty, harga_satuan, qty * harga_satuan AS sub_total FROM penjualan_detail
Hasil yang kita peroleh: +----+------+--------------+-----------+ | id | qty | harga_satuan | sub_total | +----+------+--------------+-----------+ | 1 | 1 | 46800 | 46800 | | 2 | 1 | 34800 | 34800 | | 3 | 2 | 33800 | 67600 | | 4 | 1 | 46800 | 46800 | | 5 | 2 | 39800 | 79600 | +----+------+--------------+-----------+
Pada query diatas, pada klausa SELECT terdapat keyword AS. Keyword ini digunakan untuk memberi nama alias pada kolom, yang pada contoh diatas, nama kolom menjadi sub_total. Jika tidak menggunakan kolom alias, maka nama kolom adalah qty * harga_satuan Kolom alias dapat ditulis dengan atau tanpa keyword AS, jika tanpa keyword AS, pemberian nama kolom alias cukup diberi jarak spasi, misal: qty * harga_satuan sub_total. Saya sendiri lebih memilih menggunakan keyword AS karena mudah diidentifikasi terutama pada query yang kompleks. Selanjutnya kita tambahkan diskon untuk mendapatkan nilai sub_total dengan formula qty x harga_satuan – diskon. Query yang kita jalankan
10
BAB 1 Statemen SELECT
1. 2. 3. 4. 5. 6.
SELECT id, qty, harga_satuan , qty * harga_satuan AS sub_total , diskon , qty * harga_satuan * diskon AS nilai_diskon , qty * harga_satuan - ( qty * harga_satuan * diskon ) AS neto FROM penjualan_detail
Hasil yang kita peroleh: +----+------+--------------+-----------+--------+------------------+-------------------+ | id | qty | harga_satuan | sub_total | diskon | nilai_diskon | neto | +----+------+--------------+-----------+--------+------------------+-------------------+ | 1 | 1 | 46800 | 46800 | 0 | 0 | 46800 | | 2 | 1 | 34800 | 34800 | 0 | 0 | 34800 | | 3 | 2 | 33800 | 67600 | 0.1 | 6760.00010073185 | 60839.99989926815 | | 4 | 1 | 46800 | 46800 | 0 | 0 | 46800 | | 5 | 2 | 39800 | 79600 | 0 | 0 | 79600 | +----+------+--------------+-----------+--------+------------------+-------------------+
Pada contoh diatas, kolom nilai_diskon dan neto memiliki nilai desimal (nilai di belakang koma) yang cukup panjang, untuk itu kita perlu menyederhanakannya menggunakan fungsi ROUND, kita ubah query nya menjadi: 1. 2. 3. 4. 5. 6.
SELECT id, qty, harga_satuan , qty * harga_satuan AS sub_total , diskon , ROUND(qty * harga_satuan * diskon) AS nilai_diskon , ROUND(qty * harga_satuan - ( qty * harga_satuan * diskon )) AS neto FROM penjualan_detail
Hasil yang kita peroleh: +----+------+--------------+-----------+--------+--------------+-------+ | id | qty | harga_satuan | sub_total | diskon | nilai_diskon | neto | +----+------+--------------+-----------+--------+--------------+-------+ | 1 | 1 | 46800 | 46800 | 0 | 0 | 46800 | | 2 | 1 | 34800 | 34800 | 0 | 0 | 34800 | | 3 | 2 | 33800 | 67600 | 0.1 | 6760 | 60840 | | 4 | 1 | 46800 | 46800 | 0 | 0 | 46800 | | 5 | 2 | 39800 | 79600 | 0 | 0 | 79600 | +----+------+--------------+-----------+--------+--------------+-------+
Killer Trik Query MySQL
11
Ketika menggunakan operator aritmetika, jika diperlukan tambahkan tanda kurung, terutama jika melibatkan operator plus atau minus (+ -) dengan kali atau bagi (* /). Dengan tanda kurung, ekspresi yang ada di dalam tanda kurung akan di eksekusi terlebih dahulu.
1.3. Operator Perbandingan Operator perbandingan digunakan untuk membandingkan dua nilai atau ebih. Operator ini sering digunakan pada klausa WHERE. Operator perbangingan yang sering digunakan adalah sama dengan ( = ) dan tidak sama dengan ( != ). Pada contoh sebelumnya kita telah menggunakan operator ini untuk mengambil data mahasiswa dengan jenis kelamin Laki Laki: 1. SELECT nama, jenis_kelamin 2. FROM mhs 3. WHERE jenis_kelamin = "L"
Selain sama dengan dan tidak sama dengan, kita juga dapat menggunakan operator > (lebih besar), < (lebih kecil), >= (lebih besar atau sama dengan), 50000 ORDER BY total DESC
Maka pasti kita akan mendapati error: ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
Sudah tahu kan kenapa muncul error seperti itu? Yup. Karena klausa FROM dan WHERE akan di eksekusi pertama kali dan pada tahap itu, hanya tersedia kolom tabel penjualan_detail dan tidak ada kolom total
36
BAB 2 Kunci Menguasai SELECT
Solusinya bagaimana? Mau tidak mau kita harus menggunakan kolom yang ada yaitu kolom qty dan harga_satuan Jalankan query berikut: 1. 2. 3. 4.
SELECT id, qty, harga_satuan, qty * harga_satuan AS total FROM penjualan_detail WHERE qty * harga_satuan > 50000 ORDER BY total DESC
Hasilnya adalah sebagai berikut: +----+------+--------------+--------+ | id | qty | harga_satuan | total | +----+------+--------------+--------+ | 5 | 2 | 55000 | 110000 | | 3 | 2 | 45000 | 90000 | | 1 | 1 | 76000 | 76000 | | 4 | 1 | 70000 | 70000 | +----+------+--------------+--------+
Mungkin anda bertanya…., bagaimana data bisa diambil hanya yang total nya diatas 50.000? Lihat kembali BAB I, ketika MySQL mengeksekusi klausa WHERE, maka setiap baris pada tabel akan dievaluasi satu per satu dan hanya diambil baris yang memenuhi kriteria pada klausa WHERE Pada
query
diatas,
MySQL
akan
mengalikan
qty
dengan
harga_satuan di setiap baris pada tabel penjualan_detail dan hanya diambil baris yang hasil perkalian nya diatas 50.000
Catatan 2: Fungsi Agregasi Lagi lagi terdapat hal di luar “pakem” dan lagi lagi terkait fungsi agregasi, yaitu jika kolom alias tersebut terbentuk dari fungsi
Killer Trik Query MySQL
37
agregasi, maka kolom tersebut baru terbentuk setelah klausa GROUP BY selesai dieksekusi. Kenapa? Karena fungsi agregasi dieksekusi bersama dengan klausa GROUP BY (seperti yang telah kita bahas pada bagian sebelumnya) sehingga kolom tersebut baru terbentuk setelah klausa GROUP BY selesai dieksekusi. Dengan demikian, kita tidak dapat menggunakannya pada klausa GROUP BY, melainkan mulai klausa ORDER BY dan seterusnya, contoh: 1. SELECT id_trx, id_pelanggan, SUM(total_trx) AS total 2. FROM penjualan 3. GROUP BY total
Jika query tersebut dijalankan maka kita akan mendapatki pesan error: SQL Error (1056): Can't group on 'total'
Pada contoh diatas, karena kolom total merupakan kolom alias dan baru terbentuk setelah klausa GROUP BY selesai dieksekusi, maka tidak dapat digunakan pada klausa GROUP BY Kasus seperti ini jarang sekali terjadi, sehingga tidak perlu difikirkan terlalu menadalam cukup dijadikan pengetahuan saja.
38
BAB 2 Kunci Menguasai SELECT
2.3. Identifikasikan Bentuk Tabel Pada bagian ini kita akan membahas lebih dalam bagaimana mengidentifikasi bentuk tabel hasil eksekusi klausa FROM Bentuk tabel ini sangat penting karena akan menentukan tabel awal yang akan digunakan pada tahap berikutnya, untuk itu kita perlu meng identifikasikan bentuk tabel ini dengan benar Pada bagian sebelumnya telah sedikit dibahas bagaimana bentuk tabel hasil klausa FROM ini, pada pembahasan tersebut bentuk tabel berupa tabel riil yaitu tabel penjualan_detail (FROM penjualan_detail) Pada banyak kasus, kita perlu menggunakan dua atau lebih tabel, dan karena hasil tabel pada klausa FROM ini harus berupa satu tabel, maka kita harus menggabungkan tabel tabel tersebut menjadi satu. Terkait penggabungan ini, terdapat dua bentuk yang dapat digunakan yaitu menggunakan JOIN dan UNION, keduanya akan kita bahas pada BAB tersendiri. Pertanyaan selanjutnya, kapan menggunakan JOIN dan kapan menggunakan UNION? Untuk memutuskan apakah menggunakan JOIN atau UNION, perhatikan kondisi berikut: 1. JOIN. Gunakan JOIN jika kolom pada tabel hasil klausa FROM terdapat kolom dari tabel yang digabungkan, atau bisa juga jika kedua tabel saling berhubungan (ada key antar tabel - foreign key) 2. UNION. Gunakan UNION jika tabel hasil klausa FROM memiliki kolom yang sama dengan tabel yang digabungkan atau jika kita
Killer Trik Query MySQL
39
perlu data pada kedua tabel namun keduanya tidak saling berhubungan (tidak ada key antar tabel). Praktik di lapangan kita akan sering menggunakan JOIN dan jarang (hampir tidak pernah) menggunakan UNION. Selanjutnya mari kita belajar dari contoh… Kali ini kita akan menggunakan JOIN Note: Jika Anda belum memahami join, tidak masalah, cukup ikuti saja dulu pembahasannya. Misal kita memiliki dua buah tabel, yaitu tabel penjualan dan pelanggan sebagai berikut: penjualan +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 0 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+ pelanggan +--------------+---------+-----------+---------+ | id_pelanggan | nama | alamat | id_staf | +--------------+---------+-----------+---------+ | 1 | Alfa | Jakarta | 1 | | 2 | Beta | Semarang | 1 | | 3 | Charlie | Surabaya | 2 | | 4 | Delta | Surakarta | 3 | +--------------+---------+-----------+---------+
Selanjutnya kita ingin menampilkan data penjualan beserta nama pelanggannya dengan output sebagai berikut: +------+------------+-----------+ | nama | tgl_trx | total_trx | +------+------------+-----------+ | Alfa | 2017-03-02 | 192000 |
40
BAB 2 Kunci Menguasai SELECT
| Alfa | 2017-03-10 | 186000 | | Beta | 2017-04-05 | 110000 | | Beta | 2016-11-10 | 256000 | | NULL | 2017-04-10 | 259000 | +------+------------+-----------+
Bagaimana querynya? Sesuai dengan prinsip yang telah kita bahas, maka pertama kita buat gabungan tabel melalui klausa FROM, selanjutnya kita ambil kolom melalui klausa SELECT.
Pertama… Karena kita menggunakan lebih dari satu tabel, maka kita perlu identifikase tabel output… Pada tabel output terdapat kolom nama yang berasal dari tabel pelanggan, dan kolom tgl_trx dan total_trx yang berasal dari tabel penjualan. Nah karena kolom pada tabel output ada pada kedua tabel, maka kita harus menggabungkan kedua tabel tersebut. Selanjutnya menggunakan JOIN atau UNION? Jika kita perhatikan, kolom yang akan kita tampilkan adalah kolom nama, tgl_trx, dan total_trx ketiga kolom tersebut ada pada
gabungan kedua tabel, maka tabel hasil klausa FROM harus memuat ketiga kolom tersebut, selain itu, kedua tabel juga saling berhubungan pada kolom id_pelanggan, oleh karena itu kita gunakan JOIN Mari kita gabungkan kedua tabel menggunakan JOIN, jalankan query berikut:
Killer Trik Query MySQL
41
1. 2. 3.
SELECT * FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan);
Hasilnya adalah: +--------------+--------+------------+-----------+------+----------+---------+ | id_pelanggan | id_trx | tgl_trx | total_trx | nama | alamat | id_staf | +--------------+--------+------------+-----------+------+----------+---------+ | 1 | 1 | 2017-03-02 | 192000 | Alfa | Jakarta | 1 | | 1 | 2 | 2017-03-10 | 186000 | Alfa | Jakarta | 1 | | 2 | 4 | 2017-04-05 | 110000 | Beta | Semarang | 1 | | 2 | 5 | 2016-11-10 | 256000 | Beta | Semarang | 1 | | 0 | 3 | 2017-04-10 | 259000 | NULL | NULL | NULL | +--------------+--------+------------+-----------+------+----------+---------+
Kedua… Selanjutnya kita definisikan kolom pada klausa SELECT, sehingga querynya menjadi: 1. 2. 3.
SELECT nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan);
Hasil: +------+------------+-----------+ | nama | tgl_trx | total_trx | +------+------------+-----------+ | Alfa | 2017-03-02 | 192000 | | Alfa | 2017-03-10 | 186000 | | Beta | 2017-04-05 | 110000 | | Beta | 2016-11-10 | 256000 | | NULL | 2017-04-10 | 259000 | +------+------------+-----------+
Bagaimana alur eksekusi querynya? Alurnya Sama persis dengan yang telah kita bahas bada bagian sebelumnya. Langsung saja, perhatikan ilustrasi berikut:
42
BAB 2 Kunci Menguasai SELECT
Gambar 2. 4 Ilustrasi alur eksekusi statemen SELECT yang terdapat klausa JOIN
Pada ilustrasi diatas, terlihat bahwa MySQL membuat temporary tabel yang berisi gabungan tabel penjualan dan pelanggan, temporary tabel ini perlu dibuat karena tabel hasil klausa FROM merupakan hasil dari penggabungan tabel, sehingga tidak mungkin ada di database.
2.4. Pendefinisian Kolom Baru Setelah kita memahami bagaimana statemen SELECT dieksekusi dan bagaimana mendefinisikan tabel pada klausa FROM, selanjutnya kita
Killer Trik Query MySQL
43
harus dapat mengidentifikasi kolom pada tabel output yang akan kita tampilkan. Pendefinisian kolom ini sangat bervariasi tergantung kondisi dilapangan, kolom bisa berupa existing column (kolom yang sudah ada - kolom yang berasal dari tabel hasil klausa FROM), maupun kolom baru yang berasal dari nilai skalar, string, fungsi, ekspresi, dll. Yang terpenting adalah bagaimanapun rumitnya penghitungan yang dilaukan, semua kolom pada tabel output harus ada pada klausa SELECT, perhatikan ilustrasi berikut:
Gambar 2.5. Ilustrasi Jenis Kolom Pada Klausa SELECT
Pada ilustrasi diatas terlihat bahwa setiap yang kita tulis pada statemen select (dengan pemisah tanda koma) akan selalu menghasilkan kolom, baik nilai skalar (nilai 1) ekspresi berupa operasi aritmatika, maupun nama kolom tabel hasil klausa FROM. Berdasarkan hal tersebut diatas, dapat disimpulkan bahwa jika kita ingin menambahkan kolom yang tidak ada pada
44
BAB 2 Kunci Menguasai SELECT
tabel hasil klausa FROM, maka kita harus mendefinisikannya pada klausa SELECT
2.5. Eksekusi Baris Selanjutnya, perlu dipahami juga bahwa setiap baris pada tabel yang terbentuk oleh kalusa FROM akan dieksekusi sesuai dengan apa yang di definisikan pada klausa SELECT. Hal ini sesuai dengan prinsip yang telah kita pelajari bahwa ketika mengeksekusi klausa SELECT, maka jika diperlukan, MySQL akan membuat kolom baru, nah isi dari kolom baru ini merupakan hasil eksekusi dari setiap baris yang ada pada tabel hasil klausa FROM Perhatikan ilustrasi berikut:
Gambar 2.6. Ilustrasi Eksekusi Klausa SELECT
Pada gambar diatas terlihat bahwa untuk menghasilkan nilai pada kolom output (kolom baru), setiap baris yang ada pada tabel penjualan_detail akan diseksekusi sesuai dengan yang ada pada klausa SELECT.
Killer Trik Query MySQL
45
2.6. Pembatas Tampilan Data Konsep terakhir adalah mengetahui hal-hal yang menyebabkan data tidak ditampilkan semua. Maksudnya apa? Secara default, ketika mengeksekusi statemen
SELECT, maka
semua baris pada tabel hasil akan ditampilkan, kecuali jika terdapat klausa: 1. WHERE; 2. GROUP BY; 3. LIMIT; dan 4. Fungsi agregasi pada klausa SELECT. Sebenarnya tidak ada konsep baru disini, karena dengan mengetahui arti dari klausa diatas (telah dibahas pada BAB I) otomatis akan tahu bahwa ada pembatasan data yang ditampikan. Namun demikian, hal ini perlu ditekankan lagi, karena pada kasus yang rumit, kita bingung dan ragu apa yang harus dilakukan.
2.7. Poin Penting Dari berbagai pembahasan diatas, dapat disimpulkan bahwa terdapat beberapa hal penting yang perlu dipahami yaitu: 1. Memahami urutan eksekusi query. Urutannya adalah: FROM, WHERE, SELECT, GROUP BY, ORDER BY, HAVING, dan LIMIT 2. Ketika
menyusun
query,
pertama
tama
kita
perlu
mendefinisikan tabel yang akan terbentuk pada eksekusi klausa
46
BAB 2 Kunci Menguasai SELECT
FROM, jika data melibatkan lebih dari satu tabel, gabungkan tabel tersebut menjadi satu menggunakan JOIN atau UNION. 3. Jika ada kolom baru yang tidak ada pada tabel hasil eksekusi klausa FROM, definisikan kolom tersebut pada klausa SELECT. 4. Kolom baru tersebut terbentuk setelah klausa SELECT selesai dieksekusi, sehingga baru
dapat digunakan pada klausa
GROUP BY, dst… Khusus untuk kolom hasil fungsi Agregasi, seperti SUM(), dan COUNT(), maka kolom ini baru terbentuk setelah klausa GROUP BY selesai dieksekusi, sehingga baru dapat digunakan mulai klausa ORDER BY, dst… 5. Ketika mengeksekusi klausa SELECT dan WHERE maka setiap baris pada tabel hasil eksekusi klausa FROM akan dieksekusi.
Killer Trik Query MySQL
47
Halaman ini sengaja dikosongkan Jagowebdev.com
48
BAB 2 Kunci Menguasai SELECT
BAB 3 Ekspresi Logika MySQL menyediakan dua bentuk ekspresi logika, yaitu ekspresi IF dan CASE. Kedua ekspresi ini memiliki fungsi yang sama yaitu digunakan untukk menguji suatu ekspresi, bedanya IF hanya bisa digunakan unntuk menguji satu ekspresi, sedangkan CASE dapat menguji lebih dari satu ekspresi
3.1. Ekspresi IF Ekspresi logika IF berbentuk fungsi (menggunakan tanda kurung) dan hanya dapat digunakan untuk menguji satu ekspresi. Satu ekspresi artinya jika ekspresi benar maka jalankan statemen tertentu jika salah jalankan statemen lain,
dalam bahasa
pemorograman umum tampak seperti ini: 1. if (ekspresi) { 2. ... 3. } else { 4. ... 5. }
Contoh statemen dengan lebih dari satu ekspresi (tidak bisa menggunakan fungsi IF): 1. if (ekspresi) { 2. ... 3. } elseif (ekspresi_lain) { 4. ... 5. }
Pada MySQL, format penulisan fungsi IF adalah sebagai berikut:
Killer Trik Query MySQL
49
IF(ekspresi, true, false)
Bagian true akan dieksekusi jika ekspresi bernilai benar dan bagian false dieksekusi jika ekspresi bernilai salah. Fungsi ini sangat powerfull dan sebagian besar query yang mengandung logika dapat diselesaikan menggunakan fungsi ini, untuk itu kuasai fungsi ini dengan baik. Fungsi ini dapat berbentuk nested IF, yaing artinya IF didalam IF, bentuknya seperti ini: IF(ekspresi, true, IF(ekspresi_false, true, false))
Kita tidak membahas lebih jauh mengenai bentuk ini karena akan membingungkan, selain itu, lebih mudah menggunkaan ekspresi CASE dari pada nested IF.
Memahami eksekusi fungsi IF Seperti yang telah kita bahas pada bab Kunci Menguasai SELECT, ketika mengeksekusi klausa SELECT, MySQL akan memeriksa satu per satu baris pada tabel yang terbentuk dari klausa FROM. Demikian juga dengan fungsi IF, jika pada statemen SELECT terdapat fungsi ini, maka fungsi ini akan dieksekusi sebanyak baris yang ada pada tabel hasil klausa FROM Agar lebih jelas, misal kita memiliki tabel buku_pinjam sebagai berikut: +-----------+---------+-------------+------------+-------------+ | id_pinjam | id_buku | id_peminjam | tgl_pinjam | tgl_kembali | +-----------+---------+-------------+------------+-------------+ | 1 | 1 | 001 | 2017-05-13 | 0000-00-00 | | 2 | 2 | 002 | 2017-05-10 | 0000-00-00 |
50
BAB 3 Ekspresi Logika
| 3 | 3 | 001 | 2017-05-12 | 2017-05-15 | | 4 | 4 | 003 | 2017-05-14 | 0000-00-00 | | 5 | 3 | 004 | 2017-05-12 | 2017-05-14 | +-----------+---------+-------------+------------+-------------+
Selanjutnya kita akan menampilkan data buku beserta status pengembalian dengan output tabel sebagai berikut: +---------+------------+-------------+---------+ | id_buku | tgl_pinjam | tgl_kembali | Kembali | +---------+------------+-------------+---------+ | 1 | 2017-05-13 | 0000-00-00 | Belum | | 2 | 2017-05-10 | 0000-00-00 | Belum | | 3 | 2017-05-12 | 2017-05-15 | Sudah | | 4 | 2017-05-14 | 0000-00-00 | Belum | | 3 | 2017-05-12 | 2017-05-14 | Sudah | +---------+------------+-------------+---------+
Query yang kita jalankan adalah sebagai berikut: 1. SELECT id_buku 2. , tgl_pinjam 3. , tgl_kembali 4. , IF(tgl_kembali = "0000-00-00", "Belum", "Sudah") AS Kembali 5. FROM buku_pinjam
Bagaimana alurnya? Sesuai dengan yang telah kita pelajari, pertama MySQL akan memilih tabel sesuai yang ada pada klausa FROM yaitu tabel buku_pinjam. Selanjutnya MySQL akan mengeksekusi klausa SELECT dan memeriksa apakah perlu membuat kolom baru. Jika ada kolom baru, maka setiap baris tabel hasil klausa FROM akan dieksekusi untuk mendapatkan nilai kolom baru tersebut. Terakhir, MySQL akan menampilkan tabel output sesuai dengan kolom yang didefinisikan pada klausa SELECT Perhatikan ilustrasi berikut:
Killer Trik Query MySQL
51
Gambar 3.1. Ilustrasi Eksekusi Fungsi IF
3.2. Ekspresi CASE Ekspresi Case memiliki fungsi sama dengan fungsi IF yaitu untuk menguji suatu ekspresi, bedanya, case dapat digunakan untuk menguji lebih dari satu ekspresi. Dalam bahasa pemrograman umum, bentuk ekspresi case sama seperti ini: 1. if (ekspresi) { 2. Hasil1 3. } elseif (ekspresi2) {
52
BAB 3 Ekspresi Logika
4. Hasil2 5. } elseif (ekspresi3) { 6. Hasil3 7. } else { 8. Hasil Else 9. }
Format penulisan ekspresi CASE adalah sebagai berikut: 1. SELECT kolom, 2. CASE 3. WHEN ekspresi1 THEN Hasil1 4. WHEN ekspresi2 THEN Hasil2 5. ... 6. [ELSE Hasil Else] 7. END 8. FROM nama_tabel
Banyaknya ekspresi yang dapat diuji oleh ekspresi CASE tidak terbatas. Pada query diatas, keyword CASE, WHEN, THEN, dan END wajib ada sedangkan keyword ELSE bersifat opsional sehingga tidak wajib ada. Contoh format ekspresi CASE dengan bentuk minimalis adalah sebagai berikut: 1. SELECT kolom, 2. CASE WHEN ekspresi THEN Hasil END 3. FROM nama_tabel
Ekspresi Case ini powerfull untuk menguji kondisi yang kompleks yang tidak bisa diselesaikan dengan fungsi IF atau ribet jika menggunakan nested IF Contoh ekspresi CASE dengan satu ekspresi: misal kita tampilkan status peminjaman buku dengan kriteria sebagai berikut:
Killer Trik Query MySQL
53
Jika belum kembali, status: Belum kembali
Jika sudah kembali, status: Kembali dalam ... hari
Query yang kita jalankan adalah: 1. SELECT id_buku, 2. tgl_pinjam, 3. tgl_kembali, 4. CASE 5. WHEN tgl_kembali = "0000-00-00" THEN "Belum Kembali" 6. ELSE CONCAT("Kembali dalam " 7. , tgl_kembali - tgl_pinjam, " Hari") 8. END AS status 9. FROM buku_pinjam
Hasil: +---------+------------+-------------+----------------------+ | id_buku | tgl_pinjam | tgl_kembali | status | +---------+------------+-------------+----------------------+ | 1 | 2017-05-13 | 0000-00-00 | Belum Kembali | | 2 | 2017-05-10 | 0000-00-00 | Belum Kembali | | 3 | 2017-05-12 | 2017-05-15 | Kembali dalam 3 Hari | | 4 | 2017-05-14 | 0000-00-00 | Belum Kembali | | 3 | 2017-05-12 | 2017-05-14 | Kembali dalam 2 Hari | +---------+------------+-------------+----------------------+
Karena hanya ada satu ekspresi, maka query diatas dapat diganti dengan fungsi IF sebagai berikut: 1. SELECT id_buku, tgl_pinjam, tgl_kembali 2. , IF( tgl_kembali = "0000-00-00" 3. , "Belum Kembali" 4. , CONCAT("Kembali dalam " 5. , tgl_kembali - tgl_pinjam, " Hari" 6. ) 7. ) AS status 8. FROM buku_pinjam
Contoh lebih dari satu ekspresi: kita akan menampilkan status stok barang, misal kita memiliki tabel barang sebagai berikut:
54
BAB 3 Ekspresi Logika
+-----------+-------------+------+-------+ | kd_barang | nama_barang | stok | harga | +-----------+-------------+------+-------+ | 1 | Mouse | 14 | 76000 | | 2 | Flashdisk | 15 | 55000 | | 3 | Mousepad | 17 | 35000 | | 4 | Keyboard | 12 | 80000 | | 5 | Kabel VGA | 7 | 45000 | +-----------+-------------+------+-------+
Selanjutnya kita akan menampilkan nama barang, jumlah stoknya, dan status nya dengan kriteria jika jumlah stok = 0, maka stok habis, jika stok > 0 dan < 10 sedikit, stok >= 10 dan 0 AND stok < 10 THEN "Sedikit" 6. WHEN stok >= 10 AND stok < 15 THEN "Cukup" 7. ELSE "Banyak" 8. END AS status_tok 9. FROM barang
Hasil yang kita peroleh: +-------------+------+------------+ | nama_barang | stok | status_tok | +-------------+------+------------+ | Mouse | 14 | Cukup | | Flashdisk | 15 | Banyak | | Mousepad | 17 | Banyak | | Keyboard | 12 | Cukup | | Kabel VGA | 7 | Sedikit | +-------------+------+------------+
Killer Trik Query MySQL
55
Halaman ini sengaja dikosongkan Jagowebdev.com
56
BAB 3 Ekspresi Logika
BAB 4 Menguasai JOIN Pada
MySQL
dan
RDBMS
pada
umumnya,
kita
dapat
menggabungkan dua atau lebih tabel menjadi satu, bentuk penggabungan tersebut dapat berbentuk horizontal maupun vertikal. Bentuk horizontal diperoleh menggunakan JOIN, sedangkan bentuk vertikal menggunakan UNION. Pada bab II. Kunci Menguasai SELECT, telah disinggung sedikit mengenal JOIN, pada bab ini, kita akan membahasnya lebih dalam. Apa itu JOIN? Seperti
artinya
yaitu
gabungan,
join
dalam
SQL
berarti
menggabungkan tabel, bentuk penggabungannya adalah horizontal, ilustrasinya adalah sebagai berikut:
Gambar 4. 1. Ilustrasi JOIN
Killer Trik Query MySQL
57
Pada MySQL, terdapat tiga jenis join yaitu JOIN, LEFT JOIN, dan RIGHT JOIN Join ini sangat powerful dan dalam model database relasional (RDBMS), join menjadi alat yang ampuh untuk memecahkan berbagai permasalahan query SQL yang melibatkan banyak tabel. So…, kita harus benar benar menguasainya.
4.1. JOIN Pada join, tabel digabungkan kemudian dihasilkan tabel baru yang berisi data yang hanya ada pada kedua tabel. Contoh kita memiliki tabel mhs dan mhs_jurusan sebagai berikut: mhs +------+---------+---------------+------------+ | nim | nama | jenis_kelamin | kd_jurusan | +------+---------+---------------+------------+ | 001 | Alfa | L | J002 | | 002 | Beta | P | J002 | | 003 | Charlie | P | J001 | | 004 | Delta | L | J001 | | 005 | Erdhi | L | J001 | | 006 | Farah | P | J002 | | 007 | Gisel | P | J002 | | 008 | Haris | L | NULL | +------+---------+---------------+------------+ mhs_jurusan +------------+--------------+ | kd_jurusan | nama_jurusan | +------------+--------------+ | J001 | MANAJEMEN | | J002 | AKUNTANSI | | J003 | TEKNIK | +------------+--------------+
Selanjutnya kita gabungkan kedua tabel tersebut menggunakan INNER JOIN dengan query sebagai berikut:
58
BAB 4 Menguasai JOIN
1. SELECT * 2. FROM mhs 3. JOIN mhs_jurusan USING (kd_jurusan)
Hasil: +------------+------+---------+---------------+--------------+ | kd_jurusan | nim | nama | jenis_kelamin | nama_jurusan | +------------+------+---------+---------------+--------------+ | J002 | 001 | Alfa | L | AKUNTANSI | | J002 | 002 | Beta | P | AKUNTANSI | | J001 | 003 | Charlie | P | MANAJEMEN | | J001 | 004 | Delta | L | MANAJEMEN | | J001 | 005 | Erdhi | L | MANAJEMEN | | J002 | 006 | Farah | P | AKUNTANSI | | J002 | 007 | Gisel | P | AKUNTANSI | +------------+------+---------+---------------+--------------+
Pada contoh diatas, kita menggabungkan data menggunakan data kolom nim. Dari tabel yang dihasilkan, terlihat bahwa mahasiswa dengan nama Haris tidak ditampilkan, karena kode jurusan nya yaitu NULL tidak ada pada tabel mhs_jurusan. Demikian juga dengan Jurusan teknik, jurusan ini tidak ditampilkan karena kode jurusan J003 tidak ada pada tabel mhs
Note: Keyword JOIN dapat diganti dengan INNER JOIN atau
CROSS
JOIN.
Saya
sendiri
lebih
memilih
menggunakan keyword JOIN saja karena lebih simpel.
4.2. USING atau ON Pada contoh diatas, kondisi yang menghubungkan kedua tabel kita definisikan menggunakan keyword USING, keyword ini digunakan jika nama kolom yang menjadi penghubung ada pada kedua tabel, yang pada contoh kali ini kolom kd_jurusan.
Killer Trik Query MySQL
59
Selain USING, kita juga dapat menggunakan keyword ON, misal pada contoh diatas, jika menggunakan ON, maka klausa JOIN akan berubah menjadi ON mhs.kd_jurusan = mhs_jurusan.kd_jurusan Note: Saya pribadi sebisa mungkin menggunakan USING, jika terpaksanya tidak bisa, baru menggunakan ON
4.3. LEFT JOIN Pada left join, data pada tabel disebelah kiri klausa left join akan ditampilkan semua, sedangkan data pada tabel sebelah kanan ditampilkan hanya jika data tersebut ada pada tabel sebelah kiri Jika data pada tabel sebelah kiri tidak memiliki pasangan data dengan tabel di sebelah kanan, maka data pada tabel sebelah kanan akan benilai NULL Misal kita gabungkan tabel mhs dan mhs_jurusan dengan LEFT JOIN sebagai berikut: 1. SELECT * 2. FROM mhs 3. LEFT JOIN mhs_jurusan USING (kd_jurusan)
Hasil yang kita peroleh: +------------+------+---------+---------------+--------------+ | kd_jurusan | nim | nama | jenis_kelamin | nama_jurusan | +------------+------+---------+---------------+--------------+ | J001 | 003 | Charlie | P | MANAJEMEN | | J001 | 004 | Delta | L | MANAJEMEN | | J001 | 005 | Erdhi | L | MANAJEMEN | | J002 | 001 | Alfa | L | AKUNTANSI | | J002 | 002 | Beta | P | AKUNTANSI | | J002 | 006 | Farah | P | AKUNTANSI | | J002 | 007 | Gisel | P | AKUNTANSI | | NULL | 008 | Haris | L | NULL | +------------+------+---------+---------------+--------------+
60
BAB 4 Menguasai JOIN
Dari contoh diatas terlihat bahwa MySQL menampilkan data mahasiswa bernama Haris dan tidak menampilkan data jurusan teknik. Mengapa demikian? Karena seperti yang telah disebutkan sebelumnya, pada LEFT JOIN data pada tabel disebelah kiri, yaitu tabel mhs akan ditampilkan semua, sedangkan data dengan kode jurusan J003 yang ada pada tabel mhs_jurusan (tabel sebelah kanan) tidak ditampilkan karena tidak memiliki pasangan dengan data pada tabel mhs. Perhatikan bahwa karena Haris tidak memiliki data pada kolom kd_jurusan, maka kolom nama_jurusan (kolom tabel sebelah kanan) untuk Haris bernilai NULL
Note: Keyword LEFT JOIN dapat diganti dengan LEFT OUTER JOIN. Saya sendiri lebih memilih menggunakan keyword LEFT JOIN saja karena lebih simpel.
4.4. RIGHT JOIN Kebalikan dari left join, pada right join, semua data pada tabel disebelah kanan akan ditampilkan semua sedangkan data pada tabel disebelah kiri ditampilkan hanya jika data tersebut ada pada tabel disebelah kanan. Jika data pada tabel sebelah kanan tidak memiliki pasangan dengan tabel di sebelah kanan, maka data pada tabel sebelah kiri akan benilai NULL Sebagai contoh misal kita gabungkan tabel mhs dan mhs_jurusan menggunakan RIGHT JOIN sebagai berikut:
Killer Trik Query MySQL
61
1. SELECT * 2. FROM mhs 3. RIGHT JOIN mhs_jurusan USING (kd_jurusan)
Hasil yang kita peroleh: +------------+--------------+------+---------+---------------+ | kd_jurusan | nama_jurusan | nim | nama | jenis_kelamin | +------------+--------------+------+---------+---------------+ | J002 | AKUNTANSI | 001 | Alfa | L | | J002 | AKUNTANSI | 002 | Beta | P | | J001 | MANAJEMEN | 003 | Charlie | P | | J001 | MANAJEMEN | 004 | Delta | L | | J001 | MANAJEMEN | 005 | Erdhi | L | | J002 | AKUNTANSI | 006 | Farah | P | | J002 | AKUNTANSI | 007 | Gisel | P | | J003 | TEKNIK | NULL | NULL | NULL | +------------+--------------+------+---------+---------------+
Pada contoh diatas terlihat bahwa data pada tabel mhs_jurusan (tabel disebelah kanan) akan ditampilkan semua sedangkan pada tabel sebelah kiri yaitu tabel mhs data tanpa kode jurusan tidak ditampilkan, karena tidak memiliki pasangan dengan data pada tabel mhs Perhatikan sama seperti sebelumnya, karena jurusan Teknik tidak memiliki mahasiswa, maka kolom nim, nama, dan jenis_kelamin (kolom tabel sebelah kiri) untuk jurusan Teknik bernilai NULL
Tips: kita cukup menghafal JOIN dan LEFT JOIN saja. Kenapa? Karena RIGHT JOIN ini secara mudah dapat diganti dengan LEFT JOIN yaitu hanya dengan mengubah posisi tabel nya, misal pada contoh diatas kita ubah posisi tabel menjadi FROM mhs_jurusan LEFT JOIN mhs, hasil yang kita peroleh akan sama, sehingga untuk mempermudah pemahaman, sebaiknya cukup menggunakan LEFT JOIN saja.
62
BAB 4 Menguasai JOIN
4.5. Hal Penting Tentang JOIN Sebagaimana yang telah kita pelajari, JOIN, LEFT JOIN, dan RIGHT JOIN, ketiganya minimal akan menampilkan data yang cocok pada kedua tabel, sebagai tambahan, LEFT JOIN menampilkan semua data tabel disebelah kiri, dan RIGHT JOIN semua data tabel disebelah kanan Dengan demikian dapat disimpulkan bahwa pada intinya join akan selalu menampilkan data yang cocok pada kedua tabel Pada kondisi tertentu mungkin Anda akan terkejut dengan hasil yang diperoleh, misal kita memiliki tabel barang_masuk dan barang_keluar sebagai berikut: barang_masuk +------------+-----------+-------------+-----------+ | tgl_masuk | kd_barang | kd_supplier | jml_masuk | +------------+-----------+-------------+-----------+ | 2017-05-02 | 1 | 1 | 14 | | 2017-05-03 | 1 | 2 | 5 | | 2017-04-26 | 1 | 1 | 7 | +------------+-----------+-------------+-----------+ barang_keluar +------------+-----------+------------+ | tgl_keluar | kd_barang | jml_keluar | +------------+-----------+------------+ | 2017-05-11 | 1 | 13 | | 2017-05-12 | 1 | 4 | | 2017-04-27 | 1 | 5 | +------------+-----------+------------+
Jika kita lakukan join, baik JOIN, LEFT JOIN maupun RIGHT JOIN, data berikut ini akan muncul +-----------+------------+-----------+------------+------------+ | kd_barang | tgl_masuk | jml_masuk | tgl_keluar | jml_keluar | +-----------+------------+-----------+------------+------------+ | 1 | 2017-05-02 | 14 | 2017-05-11 | 13 | | 1 | 2017-05-03 | 5 | 2017-05-11 | 13 | | 1 | 2017-04-26 | 7 | 2017-05-11 | 13 |
Killer Trik Query MySQL
63
| 1 | 2017-05-02 | 14 | 2017-05-12 | 4 | | 1 | 2017-05-03 | 5 | 2017-05-12 | 4 | | 1 | 2017-04-26 | 7 | 2017-05-12 | 4 | | 1 | 2017-05-02 | 14 | 2017-04-27 | 5 | | 1 | 2017-05-03 | 5 | 2017-04-27 | 5 | | 1 | 2017-04-26 | 7 | 2017-04-27 | 5 | +-----------+------------+-----------+------------+------------+
Kenapa seperti itu? Seperti yang telah kita bahas, pada join, semua data yang ada pada kedua tabel (termasuk kombinasinya) akan ditampilkan. Perhatikan ilustrasi berikut:
Gambar 4.2 Ilustrasi Data Yang Ditampilkan Oleh Klausa JOIN
Dengan
memahami
karakteristik
tersebut,
maka
akan
memudahkan kita ketika memecahkan kasus yang melibatkan banyak tabel. Hasil tersebut diatas umumnya terjadi jika hubungan antar kedua tabel bersifat many to many yang artinya setiap data pada masing masing tabel memiliki hubungan lebih dari satu data pada tabel lain.
64
BAB 4 Menguasai JOIN
Ciri
hubungan
tersebut
adalah
nilai
pada
kolom
yang
berhubungan (pada contoh diatas kolom kd_barang) memiliki nilai yang sama lebih dari satu pada kedua tabel, pada contoh diatas, nilai kd_barang pada tabel barang_masuk maupun barang_keluar memiiki nilai yang sama (nilai 1) lebih dari satu.
Killer Trik Query MySQL
65
Halaman ini sengaja dikosongkan Jagowebdev.com
66
BAB 4 Menguasai JOIN
BAB 5 Menguasai Fungsi Agregasi Pada bab awal telah disinggung sedikit mengenai fungsi agregasi, yaitu fungsi SUM dan COUNT, pada bab ini kita akan membahasnya lebih dalam. Apa itu fungsi agregasi? Fungsi agregasi adalah fungsi yang dalam operasinya melibatkan seluruh baris pada suatu kolom, kenapa demikian? karena seperti namanya, agergat yang artinya mengumpulkan sesuatu (dalam hal ini nilai) menjadi satu. Fungsi agregasi yang sering digunakan adalah SUM, COUNT, MIN, MAX, dan AVG, kita akan bahas satu per beberapa sesaat kedepan.
5.1. Memahami Cara Kerja Fungsi Agragasi Seperti yang telah disebutkan sebelumnya, fungsi agregasi akan menggabungkan semua data (baris) pada suatu kolom menjadi satu, jika terdapat klausa GROUP BY, maka data tersebut akan dikelompokkan berdasarkan data yang sama pada kolom yang ada pada klausa GROUP BY. Jangan lupa untuk selalu menggunakan GROUP BY ketika menggunakan
fungsi
agregasi
kecuali
jika
ingin
menggabungkan semua data. Sebagai contoh: mengulang contoh pada bab sebelumnya, misal kita memiliki tabel penjualan sebagai berikut:
Killer Trik Query MySQL
67
+--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 0 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Selanjutnya kita tampilkan data id_pelanggan beserta jumlah transaksinya di tahun 2017, query yang kita jalankan: 1. 2. 3. 4.
SELECT id_pelanggan, COUNT(id_trx), SUM(total_trx) FROM penjualan WHERE YEAR(tgl_trx) = 2017 GROUP BY id_pelanggan
Hasilnya adalah sebagai berikut: +--------------+---------------+----------------+ | id_pelanggan | COUNT(id_trx) | SUM(total_trx) | +--------------+---------------+----------------+ | 0 | 1 | 259000 | | 1 | 2 | 378000 | | 2 | 1 | 110000 | +--------------+---------------+----------------+
Bagaimana proses fungsi eksekusi agregasinya? Pertama tama MySQL akan mengeksekusi klausa FROM dan diperoleh tabel penjualan Selanjutnya, MySQL akan mengeksekusi klausa SELECT. Karena terdapat fungsi agregasi COUNT dan SUM, maka kedua fungsi tersebut akan dieksekusi bersama sama dengan klausa GROUP BY Selanjutnya, pada klausa GROUP BY, data akan di kelompokkan berdasarkan kolom id_pelanggan (kolom yang ada pada klausa GROUP BY) kemudian MySQL mengeksekusi eksekusi fungsi COUNT dan SUM.
68
BAB 5 Menguasai Fungsi Agregasi
Terakhir, MySQL akan menampilkan tabel output dengan kolom sesuai dengan yang didefinisikan pada klausa SELECT Ilustrasinya adalah sebagai berikut:
Gamber 5.1. Ilustrasi Alur Query
Perhatikan bahwa pada contoh diatas, ketika menjalankan GROUP BY dan sebelum menjalankan fungsi agregasi COUNT dan SUM, tabel hasil klausa FROM akan diurutkan terlebih dahulu secara ascending berdasarkan kolom pada klausa GROUP BY yaitu kolom id_pelanggan.
Killer Trik Query MySQL
69
5.2. MAX, MIN, dan AVG MAX dan MIN Fungsi MAX digunakan untuk mengambil nilai tertinggi suatu kolom, sedangkan MIN untuk mengambil nilai terendah. Misal kita memiliki tabel barang sebagai berikut: +-----------+-------------+------+-------+ | id_barang | nama_barang | stok | harga | +-----------+-------------+------+-------+ | 1 | Mouse | 14 | 76000 | | 2 | Flashdisk | 15 | 55000 | | 3 | Mousepad | 17 | 35000 | | 4 | Keyboard | 12 | 80000 | | 5 | Kabel VGA | 5 | 45000 | +-----------+-------------+------+-------+
Selanjutnya kita akan menampilkan barang yang memiliki stok paling banyak dan stok paling sedikit. Query yang kita jalankan 1. 2.
SELECT MIN(stok), MAX(stok) FROM barang
Hasil yang kita peroleh: +-----------+-----------+ | MIN(stok) | MAX(stok) | +-----------+-----------+ | 5 | 17 | +-----------+-----------+
Penting diperhatikan bahwa ketika menggunakan fungsi agregasi, maka kita tidak dapat menyandingkan data pada kolom agregasi dengan data pada kolom non agregasi jika kolom non agregasi memiliki data yang tidak sama, atau tidak terdapat klausa GROUP BY Perhatikan contoh berikut:
70
BAB 5 Menguasai Fungsi Agregasi
1. SELECT nama_barang, MIN(stok), stok 2. FROM barang
Hasil: +-------------+-----------+------+ | nama_barang | MIN(stok) | stok | +-------------+-----------+------+ | Mouse | 7 | 14 | +-------------+-----------+------+
Pada contoh diatas, seharusnya stok paling kecil adalah Kabel VGA, bukan mouse. Hal ini terjadi karena ketika menjalankan fungsi aregasi, MySQL akan menggbungkan semua data kolom agregasi (kolom stok) dan mengambil baris paling atas untuk kolom non agregasi (kolom nama_barang dan stok) Pada contoh diatas kolom nama_barang dan stok (kolom paling kanan), data yang diambil adalah data pada baris paling atas yaitu Mouse dan 14. Sebagai "rule of thumb" jika ingin menggunakan kolom non agregasi, pastikan data pada semua baris pada kolom non agregasi isinya sama, atau pastikan kolom tersebut tercantum pada klausa GROUP BY
Lanjutan… Mari kita lanjutkan… berdasarkan pola diatas, untuk mencari nilai minimal dan maksimal, kita harus menggunakan cara lain, contoh query untuk mencari barang dengan stok terkecil: 1. 2. 3. 4.
SELECT nama_barang, stok FROM barang ORDER BY stok LIMIT 1
Killer Trik Query MySQL
71
Hasil: +-------------+------+ | nama_barang | stok | +-------------+------+ | Kabel VGA | 7 | +-------------+------+
Pada contoh diatas, kita urutkan data berdasarkan stok terkecil, kemudian kita ambil 1 baris teratas. Sekarang, Anda coba buat query untuk mendapatkan barang dengan nilai stok terbesar… bisa kan?
AVG AVG digunakan untuk mendapatkan nilai rata-rata dari suatu kolom, misal untuk menghitung rata-rata jumlah stok, kita gunakan query berikut: 1. SELECT AVG(stok) 2. FROM barang
Hasil: +-----------+ | AVG(stok) | +-----------+ | 13.0000 | +-----------+
Karena nilai AVG diperoleh dari hasil pembagian (total nilai baris/banyaknya baris), maka hasil bagi tersebut kemungkinan besar tidak bulat (ada pecahan) sehingga agar penghitungan lebih akurat, hasil fungsi AVG akan menyertakan 4 digit desimal, pada contoh diatas: 14 + 15 + 17 + 12 + 7 / 5 = 13.0000
72
BAB 5 Menguasai Fungsi Agregasi
Untuk membatasi banyaknya digit dibelakang koma, kita gunakan fungsi ROUND, misal kita bulatkan hasil diatas dengan dua digit dibelaang koma: 1. SELECT AVG(stok) 2. FROM barang
Hasil: +-----------------------+ | ROUND( AVG(stok), 2 ) | +-----------------------+ | 13.00 | +-----------------------+
5.3. COUNT dan SUM Pada bagian sebelumnya, kita sudah membahas sekilas mengenai kedua fungsi ini, pada bagian ini kita akan membahasnya lebih dalam. Pada MySQL, fungsi COUNT digunakan untuk menghitung banyaknya baris tidak termasuk baris yang memiliki nilai NULL, sedangkan SUM digunakan untuk menjumlahkan nilai semua baris. Ingat selalu bahwa fungsi COUNT tidak menghitung baris yang memiliki nulai NULL Sebagai contoh: kembali mengulang contoh sebelumnya, misal kita memilliki tabel penjualan dengan data sebagai berikut: +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-02-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 1 | 2017-04-10 | 259000 | | 4 | 2 | 2016-12-02 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Killer Trik Query MySQL
73
Selanjutnya kita tampilkan banyaknya penjualan dan jumlah penjualan untuk tahun 2017, query yang kita jalankan: 1. 2. 3. 4.
SELECT COUNT(id_trx) AS jml_trx, SUM(total_trx) AS total FROM penjualan WHERE YEAR(tgl_trx) = 2017
Hasil: +---------+--------+ | jml_trx | total | +---------+--------+ | 3 | 637000 | +---------+--------+
Selanjutnya, kita tampilkan data jumlah transaksi dan total nilai transaksi yang dikelompokkan per tahun. Query yang kita jalankan: 1. SELECT YEAR(tgl_trx) AS thn_transaksi 2. , COUNT(id_trx) AS jml_trx 3. , SUM(total_trx) AS total 4. FROM penjualan 5. GROUP BY YEAR(tgl_trx)
Hasil yang kita peroleh: +---------------+---------+--------+ | thn_transaksi | jml_trx | total | +---------------+---------+--------+ | 2016 | 2 | 366000 | | 2017 | 3 | 637000 | +---------------+---------+--------+
COUNT dan SUM adalah fungsi agregasi yang sangat sering digunakan,
untuk
itu
selalu
jadikan
prioritas ketika
menghadapi berbagai permasalahan SQL, ingat selalu: HITUNG ? ya COUNT, JUMLAH ? ya SUM
74
BAB 5 Menguasai Fungsi Agregasi
5.4. SUM Dengan IF Pada
pembahasan
diawal,
kita
telah
sedikit
menyinggung
penggunaan fungsi IF didalam fungsi agregasi COUNT. Kali ini kita akan
merefresh
kembali
pemahaman
kita
dengan
menggabungkannya dengan fungsi COUNT Contoh kita akan menghitung jumlah penjualan dan total nilai penjualan untuk tahun 2017 per item. Output tabel yang diinginkan adalah sebagai berikut: +-------------+------+-------------+-----------+--------+----------+ | nama_barang | stok | jml_terjual | jml_bruto | diskon | jml_neto | +-------------+------+-------------+-----------+--------+----------+ | Mouse | 14 | 2 | 146000 | 0 | 146000 | | Flashdisk | 15 | 1 | 110000 | 0 | 110000 | | Mousepad | 17 | 2 | 95000 | 0 | 95000 | | Keyboard | 12 | 1 | 160000 | 16000 | 144000 | | Kabel VGA | 7 | 2 | 135000 | 9000 | 126000 | +-------------+------+-------------+-----------+--------+----------+
Adapun tabel yang tersedia adalah sebagai berikut: barang +-----------+-------------+------+-------+ | id_barang | nama_barang | stok | harga | +-----------+-------------+------+-------+ | 1 | Mouse | 14 | 76000 | | 2 | Flashdisk | 15 | 55000 | | 3 | Mousepad | 17 | 35000 | | 4 | Keyboard | 12 | 80000 | | 5 | Kabel VGA | 7 | 45000 | +-----------+-------------+------+-------+ penjualan_detail +----+--------+-----------+------+--------------+--------+ | id | id_trx | id_barang | qty | harga_satuan | diskon | +----+--------+-----------+------+--------------+--------+ | 1 | 1 | 1 | 1 | 76000 | 0 | | 2 | 1 | 3 | 1 | 35000 | 0 | | 3 | 1 | 5 | 2 | 45000 | 0.1 | | 4 | 2 | 1 | 1 | 76000 | 0 | | 5 | 2 | 2 | 2 | 55000 | 0 | | 6 | 3 | 3 | 2 | 35000 | 0 | | 7 | 3 | 5 | 1 | 45000 | 0 |
Killer Trik Query MySQL
75
| 8 | 3 | 4 | 2 | 80000 | 0.1 | | 9 | 4 | 2 | 2 | 55000 | 0 | | 10 | 5 | 4 | 4 | 80000 | 0.2 | +----+--------+-----------+------+--------------+--------+ penjualan +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-02-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 1 | 2017-04-10 | 259000 | | 4 | 2 | 2016-12-02 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Keterangan: tabel penjualan detail merupakan rincian dari tiap item pada tabel penjualan. Bagaimana query SQL nya ? STOP !!! Sebelum melanjutkan, silakan Anda coba menyelesaikan dengan step by step cara yang telah kita bahas pada bagian Kunci Memahami SELECT. Practice make perfect… right? Oke, jika sudah, mari kita bahas query nya ya…
Pertama… Seperti biasa rule pertama yang akan kita lakukan adalah mendefinisikan bentuk tabel hasil dari klausa FROM dengan WHERE (jika ada), untuk dapat melakukannya kita perlu untuk menganalisa tabel output untuk menentukan tabel mana yang akan digunakan:
Pada tabel output terdapat kolom nama barang dan stok yang kita dapatkan dari tabel barang.
76
BAB 5 Menguasai Fungsi Agregasi
Kolom jml_terjual, jml_bruto, diskon, dan neto yang kita peroleh dari tabel penjualan_detail
Terdapat ketentuan bahwa yang data yang akan kita tampilkan adalah data tahun 2017. Data tahun ini kita peroleh dari data tanggal penjualan yang ada pada tabel penjualan
Dari hasil identifikasi diatas, maka tabel hasil klausa FROM harus melibatkan tabel barang, penjualan, dan penjualan_detail untuk itu kita harus menggabungkan ketiganya. Bagaimana model penggabungannya? JOIN atau UNION? Karena tabel output terdiri dari kolom yang ada pada tabel barang dan penjualan_detail, maka kita gunakan penggabungan horizontal yaitu menggunakan JOIN, selain itu ketiganya tabel tersebut juga saling berhubungan Selanjutnya, bagaimana urutan tabel nya? Mana tabel yang kita letakkan disebelah kiri? Urutan tabel dari yang pailng kiri adalah tabel barang, kenapa ? Ingat pada pembahasan tentang LEFT JOIN, agar data ditampilkan semua, tempatkan tabel disebelah kiri. Karena kali ini tujuan kita menampilkan data semua barang beserta data penjualannya, maka tujuan utama adalah menampilkan data barang, sehingga kita letakkan tabel barang disebelah kiri. Selanjutnya, kita lihat bentuk tabel hasil klausa FROM dengan menggabungkan ketiga tabel tersebut menggunakan klausa SELECT * . Jalankan query berikut: 1. SELECT *
Killer Trik Query MySQL
77
2. 3. 4. 5.
FROM barang LEFT JOIN penjualan_detail USING(kd_barang) LEFT JOIN penjualan USING (id_trx) WHERE YEAR(tgl_trx) = 2017
Hasilya adalah: +--------+-----------+-------------+------+-------+------+------+--------------+ | id_trx | id_barang | nama_barang | stok | harga | id | qty | harga_satuan | +--------+-----------+-------------+------+-------+------+------+--------------+ | 1 | 1 | Mouse | 14 | 76000 | 1 | 1 | 76000 | | 1 | 3 | Mousepad | 17 | 35000 | 2 | 1 | 35000 | | 1 | 5 | Kabel VGA | 7 | 45000 | 3 | 2 | 45000 | | 2 | 1 | Mouse | 14 | 76000 | 4 | 1 | 76000 | | 2 | 2 | Flashdisk | 15 | 55000 | 5 | 2 | 55000 | | 3 | 3 | Mousepad | 17 | 35000 | 6 | 2 | 35000 | | 3 | 5 | Kabel VGA | 7 | 45000 | 7 | 1 | 45000 | | 3 | 4 | Keyboard | 12 | 80000 | 8 | 2 | 80000 | | 4 | 2 | Flashdisk | 15 | 55000 | 9 | 2 | 55000 | | 5 | 4 | Keyboard | 12 | 80000 | 10 | 4 | 80000 | +--------+-----------+-------------+------+-------+------+------+--------------+ Lanjutan... +--------+--------------+------------+-----------+ | diskon | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 0 | 1 | 2017-02-02 | 192000 | | 0 | 1 | 2017-02-02 | 192000 | | 0.1 | 1 | 2017-02-02 | 192000 | | 0 | 1 | 2017-03-10 | 186000 | | 0 | 1 | 2017-03-10 | 186000 | | 0 | 1 | 2017-04-10 | 259000 | | 0 | 1 | 2017-04-10 | 259000 | | 0.1 | 1 | 2017-04-10 | 259000 | | 0 | 2 | 2016-12-02 | 110000 | | 0.2 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Dari tabel diatas sudah kebayangkan bagaimana nantinya klausa SELECT yang akan kita jalankan? Yup, mari kita cari jawabaannya…
Kedua… Langkah selanjutnya kita susun klausa SELECT
78
BAB 5 Menguasai Fungsi Agregasi
Ingat apakah kita akan mengelompokkan data? Jika ya gunakan fungsi agregasi dan klausa GROUP BY Selanjutnya, berdasarkan tabel hasil klausa FROM, kita identifikasi kolom pada klausa SELECT: 1. nama_barang. Kolom nama barang sudah ada sehingga tinggal kita masukkan dalam klausa SELECT 2. stok. Kolom stok juga sudah tersedia 3. jml_terjual. Untuk mendapatkan angka jumlah yang terjual, kita jumlahkan nilai pada kolom qty, untuk itu kita gunakan fungsi SUM(qty) 4. jml_bruto.
Jumlah
penjualan
bruto
diperoleh
dengan
menjumlahkan (SUM) semua nilai dari hasil perkalian antara qty dengan harga_satuan sehingga kita gunakan SUM(qty * harga_satuan) 5. diskon. Nilai diskon diperoleh dari penjumlahan semua baris dari hasil perkalian qty * harga_satuan * diskon, fungsi yang kkita gunakan SUM(qty * harga_satual * diskon) 6. jml_neto diperoleh dengan mengurangkan nilai pada angka 4 dengan angka 5. Selanjutnya apakah kita perlu mengelompokkan data? Pada contoh kali ini kita akan menampilkan data per item barang, sehingga data perlu dikelompokkan berdasarkan kd_barang (GROUP BY kd_barang) Hasil bentuk querynya adalah sebagai berikut:
Killer Trik Query MySQL
79
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
SELECT nama_barang , stok , SUM(qty) AS jml_terjual , SUM(qty * harga_satuan) AS jml_bruto , ROUND(SUM(qty * harga_satuan * diskon)) AS diskon , ROUND ( SUM(qty * harga_satuan) - SUM(qty * harga_satuan * diskon) ) AS jml_neto FROM barang LEFT JOIN penjualan_detail USING(id_barang) LEFT JOIN penjualan USING(id_trx) WHERE YEAR(tgl_trx) = 2017 GROUP BY id_barang
Hasil: +-------------+------+-------------+-----------+--------+----------+ | nama_barang | stok | jml_terjual | jml_bruto | diskon | jml_neto | +-------------+------+-------------+-----------+--------+----------+ | Mouse | 14 | 2 | 146000 | 0 | 146000 | | Flashdisk | 15 | 2 | 110000 | 0 | 110000 | | Mousepad | 17 | 3 | 95000 | 0 | 95000 | | Keyboard | 12 | 2 | 160000 | 16000 | 144000 | | Kabel VGA | 7 | 3 | 135000 | 9000 | 126000 | +-------------+------+-------------+-----------+--------+----------+
Selanjutnya coba Anda tambahkan query sehingga muncul baris TOTAL di bagian paling bawan. Bisa kan?
5.5. COUNT Dengan CASE Selain
fungsi
IF,
dalam
praktik,
kita
sering
menemukan
penggabungan fungsi agregasi (COUNT dan SUM) dengan ekspresi CASE dengan bentuk seperti berikut ini: 1. 2. 3. 4. 5. 6.
80
SELECT COUNT( CASE WHEN ... THEN ... END ) FROM ...
BAB 5 Menguasai Fungsi Agregasi
Bagian ini kita akan mengasah pemahaman anda tentang penggabungan kedua fungsi tersebut Sebagai contoh isal kita memiliki tabel dosen dan dosen_absen dengan data sebagai berikut: dosen +----------+------------+ | id_dosen | nama_dosen | +----------+------------+ | 1 | Abrar | | 2 | Bahrun | | 3 | Choirul | | 4 | Dirman | | 5 | Endri | +----------+------------+ dosen_absen +----------+------------+------------+ | id_dosen | tanggal | keterangan | +----------+------------+------------+ | 3 | 2017-08-13 | I | | 2 | 2016-12-10 | S | | 3 | 2017-05-13 | A | | 2 | 2017-03-02 | S | | 2 | 2017-05-13 | S | +----------+------------+------------+
Selanjutnya kita akan menampilkan rekap data ketidakhadiran dosen per semester dengan output sebagai berikut: +-------+----------+------------+------------+------------+ | tahun | id_dosen | nama_dosen | semester_1 | semester_2 | +-------+----------+------------+------------+------------+ | 2016 | 2 | Bahrun | 0 | 1 | | 2017 | 2 | Bahrun | 2 | 0 | | 2017 | 3 | Choirul | 1 | 1 | +-------+----------+------------+------------+------------+
Bagaimana Querynya? Dicoba dulu ya? Alurnya sama persis dengan bagian sebelumnya: SUM dengan IF
Killer Trik Query MySQL
81
Sudah? Baiklah, query versi saya adalah: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 14. 16. 17. 18.
SELECT YEAR(tanggal) AS tahun, id_dosen, nama_dosen, COUNT( CASE WHEN MONTH(tanggal) >= 1 AND MONTH(tanggal) = 7 AND MONTH(tanggal) = 1 AND MONTH(tanggal) = 4 AND MONTH(tanggal) = 7 AND MONTH(tanggal) = 10 AND MONTH(tanggal) SELECT SUBSTRING('abcde', 3, 2); +--------------------------+ | SUBSTRING('abcde', 3, 2) | +--------------------------+ | cd | +--------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING('abcde', 3); +-----------------------+
Killer Trik Query MySQL
105
| SUBSTRING('abcde', 3) | +-----------------------+ | cde | +-----------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING('abcde', -3, 2); +---------------------------+ | SUBSTRING('abcde', -3, 2) | +---------------------------+ | cd | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING('abcde', -3); +------------------------+ | SUBSTRING('abcde', -3) | +------------------------+ | cde | +------------------------+ 1 row in set (0.00 sec)
Contoh penerapan pada tabel: 1. SELECT SUBSTRING(nama_barang, 1, 4), stok 2. FROM barang;
Hasil: +------------------------------+------+ | SUBSTRING(nama_barang, 1, 4) | stok | +------------------------------+------+ | Mous | 14 | | Flas | 15 | | Mous | 17 | | Keyb | 12 | | Kabe | 7 | +------------------------------+------+
CONCAT dan CONCAT_WS Fungsi concat digunakan untuk menggabungkan ekspresi menjadi satu, ekspresi ini dapat berupa nilai skalar, string, nilai kolom, dll. Adapun format penulisannya adalah sebagai berikut
106
BAB 6. Menguasai Fungsi Scalar
CONCAT(ekspresi1, ekspresi2, ekspresi3, ...)
Contoh: SELECT CONCAT("Stok: ", 14);
Hasil: +----------------------+ | CONCAT('Stok: ', 14) | +----------------------+ | Stok: 14 | +----------------------+
Contoh penerapan pada tabel: 1. SELECT CONCAT(nama_barang, ", Stok: ", 14) 2. FROM barang
Hasil: +---------------------------------------+ | CONCAT(nama_barang, ", Stok: ", stok) | +---------------------------------------+ | Mouse, Stok: 14 | | Flashdisk, Stok: 15 | | Mousepad, Stok: 17 | | Keyboard, Stok: 12 | | Kabel VGA, Stok: 7 | +---------------------------------------+
Selain fungsi concat, MySQL juga menyediakan fungsi CONCAT_WS (CONCAT With Separator). Fungsi ini sama seperti CONCAT, bedanya argumen pertama dari fungsi ini akan digunakan sebagai separator, misal: 1. SELECT CONCAT_WS(", ", nama_barang, stok, harga) 2. FROM barang
Killer Trik Query MySQL
107
Hasil: +-------------------------------------------+ | CONCAT_WS(", ", nama_barang, stok, harga) | +-------------------------------------------+ | Mouse, 14, 76000 | | Flashdisk, 15, 55000 | | Mousepad, 17, 35000 | | Keyboard, 12, 80000 | | Kabel VGA, 7, 45000 | +-------------------------------------------+
6.3. Fungsi Date Time MySQL menyediakan berbagai fungsi yang dapat digunakan untuk memanipulasi tanggal dan waktu, diantaranya yang penting untuk diketahui
adalah
YEAR,
MONTH,
DAY,
DATE_FORMAT,
dan
STR_TO_DATE
Standar Format Tanggal Sebelum membahas fungsi terkait tanggal dan waktu, kita perlu memahami format tanggal standar yang digunakan oleh database. Standar format tanggal untuk database termasuk yang digunakan MySQL adalah
YYYY-MM-DD HH:MM:SS, misal: 2017-06-01
08:00:00. Untuk tanggal, separator yang digunakan bisa bermacam macam, yang penting urutnnya YYYY diikuti MM kemudian DD, misal: 2017/06/01, 2017,06,01, bisa juga tanpa separator misal: 20170601. Agar hasil dari fungsi tanggal dan waktu benar, maka format tanggal (termasuk tanggal pada kolom) harus standar sesuai yang telah kita bahas diatas.
YEAR, MONTH, DAY
108
BAB 6. Menguasai Fungsi Scalar
Sesuai terjemahannya, fungsi ini digunakan untuk mengambil Tahun, bulan, dan tanggal dari data tanggal. Digit bulan dan tanggal yang dihasilkan oleh fungsi ini tidak diawali dengan 0, seperti 1 bukan 01 Sebagai contoh misal kita memiliki tabel penjulan sebagai berikut: +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 1 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Selanjutnya kita tampilkan data tersebut dengan memisahkan antara tahun, bulan, dan tanggal, jalankan query berikut: 1. SELECT YEAR(tgl_trx) 2. , MONTH(tgl_trx) 3. , DAY(tgl_trx) 4. , total_trx 5. FROM penjualan
Hasil yang kita peroleh: +---------------+----------------+--------------+-----------+ | YEAR(tgl_trx) | MONTH(tgl_trx) | DAY(tgl_trx) | total_trx | +---------------+----------------+--------------+-----------+ | 2017 | 3 | 2 | 192000 | | 2017 | 3 | 10 | 186000 | | 2017 | 4 | 10 | 259000 | | 2017 | 4 | 5 | 110000 | | 2016 | 11 | 10 | 256000 | +---------------+----------------+--------------+-----------+
Killer Trik Query MySQL
109
DATE_FORMAT Fungsi DATE_FORMAT digunakan untuk mengubah tanggal format standar database (YYYY-MM-DD) menjadi bentuk lain. Adapun format penulisannya adalah: DATE_FORMAT(tanggal, format_tanggal)
Argumen format_tanggal berupa string yang mengandung karakter tertentu yang mewakili bentuk tanggal tertentu .Berikut ini format karakter yang penting untuk diketahui: Format
Deskripsi
Hari %W
Nama hari dalam satu minggu (Sunday s.d Saturday)
%w
Hari dalam satu minggu (0 = Minggu s.d. 6 = Sabtu)
%d
Hari dalam satu bulan dalam dua digit (00 s.d 31)
%e
Hari dalam satu bulan dalam satu-dua digit (0 s.d 31)
Bulan %M
Nama bulan penuh (January s.d December)
%c
Bulan dalam satu-dua digit angka (0 s.d 12)
%m
Bulan dalam dua digit angka (00 s.d 12)
Tahun %Y
Tahun dalam empat digit angka
%y
Tahun dalam dua digit angka
Jam %H
110
Jam dalam 24 jam dalam dua digit (00 .d 23)
BAB 6. Menguasai Fungsi Scalar
%k
Jam dalam 24 jam dalam satu-dua digit (0 s.d 23)
Menit %i
Menit dalam dua dua digit (00 s.d 59)
Detik %S
Detik dalam dua digit (00 s.d 59)
%s
Detik dalam satu digit (1s.d 59)
Catatan: Tanda persen (%) menandakan bahwa karakter yang mengikutinya adalah karakter khusus untuk format tanggal, sehingga kita wajib menyertakannya. Contoh: 1. SELECT tgl_trx 2. , DATE_FORMAT(tgl_trx, "%d-%m-%Y") AS tgl_trx_id 3. , total_trx 4. FROM penjualan
Hasil: +------------+------------+-----------+ | tgl_trx | tgl_trx_id | total_trx | +------------+------------+-----------+ | 2017-03-02 | 02-03-2017 | 192000 | | 2017-03-10 | 10-03-2017 | 186000 | | 2017-04-10 | 10-04-2017 | 259000 | | 2017-04-05 | 05-04-2017 | 110000 | | 2016-11-10 | 10-11-2016 | 256000 | +------------+------------+-----------+
Contoh lain, kita gunakan nama hari dan nama bulan 1. SET lc_time_names = "id_ID"; 2. SELECT tgl_trx 3. , DATE_FORMAT(tgl_trx, "%W, %e %M %Y") AS tgl_trx_id 4. , total_trx 5. FROM penjualan
Killer Trik Query MySQL
111
Hasil: +------------+-------------------------+-----------+ | tgl_trx | tgl_trx_id | total_trx | +------------+-------------------------+-----------+ | 2017-03-02 | Kamis, 2 Maret 2017 | 192000 | | 2017-03-10 | Jumat, 10 Maret 2017 | 186000 | | 2017-04-10 | Senin, 10 April 2017 | 259000 | | 2017-04-05 | Rabu, 5 April 2017 | 110000 | | 2016-11-10 | Kamis, 10 November 2016 | 256000 | +------------+-------------------------+-----------+
Perhatikan bahwa pada contoh diatas, kita ubah variabel lc_time_names menjadi Indonesia (id_ID) jika tidak, maka nama hari
dan bulan akan ditampilkan dalam bahasa Inggris.
STR_TO_DATE Fungsi STR_TO_DATE digunakan untuk mengubah format tanggal Non-Standard SQL menjadi format tanggal standar SQL. Fungsi ini merupakan fungsi yang penting dan sering digunakan untuk itu harus Anda kuasai. Kenapa perlu mengubah format tanggal menjadi format standar SQL? Sering kita temui format tanggal yang disimpan dalam database banyak yang tidak standar, ada
yang
dd/mm/yyyy, dd-mm-yyyy, dmyyyy dll. Nah, untuk dapat mengolah data tersebut menggunakan fungsi terkait date seperti DATE_FORMAT(), YEAR(), MONTH(), dan DAY(), maka data tersebut harus diubah ke dalam bentuk
standar SQL Format penulisan fungsi STR_TO_DATE adalah: STR_TO_DATE(string_date, format_tanggal)
112
BAB 6. Menguasai Fungsi Scalar
Argumen format_tanggal berupa string yang mengandung karakter tertentu yang mewakili bentuk tanggal tertentu. Karakter tertentu ini sama persis dengan yang digunakan pada fungsi DATE_FORMAT Argumen format_tanggal ini nantinya akan digunakan MySQL untuk membandingkan pola tanggal yang ada pada argumen pertama. Contoh: 1. SELECT STR_TO_DATE ('25/05/2017', '%d/%m/%Y') AS tanggal
Hasil: +------------+ | tanggal | +------------+ | 2017-05-25 | +------------+
Pada contoh diatas, MySQL akan men-scan tanggal satu per satu mulai dari kiri ke kanan kemudian mencocokkan dengan pola pada argumen 2. Format yang kita gunakan pada argumen ke 2 adalah %d/%m/%Y:
%d, karena tanggal pada argumen pertama berbentuk dua digit yaitu 05
%m karena bulan juga berbentuk dua digit, yaitu 03
%Y karena tahun berbentuk 4 digit, yaitu 2017
Kita gunakan separator slash ( / ) karena antara tanggal, bulan, dan tahun dipisahkan dengan separator tersebut
Jika diilustrasikan dengan gambar, akan tampak seperti gambar berikut:
Killer Trik Query MySQL
113
Gambar 6.2. Ilustrasi Fungsi STR_TO_DATE()
Jika kita salah dalam mengidentifikasi format tanggal, maka hasil yang diperoleh tidak sesuai harapan, misal kita keliru menulis %Y menjadi %y: SELECT STR_TO_DATE ('05/03/2017', '%d/%m/%y') AS tanggal
Maka hasil yang kita peroleh: +------------+ | tanggal | +------------+ | 2020-03-05 | +------------+
Dari contoh diatas terlihat bahwa tahun yang seharusnya 2017 menjadi 2020, hal ini karena format %y membaca data tahun sebanyak dua digit, sehingga, tahun 2017 akan dibaca tahun 20 kemudian dikonversi ke format standar SQL menjadi 2020. Jika format pada argumen ke 2 tidak sesuai dengan format pada argumen pertama (meskipun hanya satu format), maka akan menghasilkan NULL, misal: SELECT STR_TO_DATE ('05-03-2017', '%d-%M-%y') AS tanggal
Hasil:
114
BAB 6. Menguasai Fungsi Scalar
+---------+ | tanggal | +---------+ | NULL | +---------+
Query diatas menghasilkan NULL karena:
Pertama, MySQL akan mencari format %d dan ketemu di dua digit pertama (05)
Selanjutnya mencari tanda dash (-) dan ketemu yaitu setelah 05
MySQL melanjutkan dengan mencari format %M yang artinya nama bulan (Januari s.d December), namun tidak ketemu, karena karakter selanjutnya adalah 03, sehingga proses pencarian berhenti dan menghasilkan NULL
6.4. Fungsi Beda Waktu MySQL menyediakan fungsi untuk menghitung beda waktu, fungsi yang penting untuk dipahami adalah fungsi DATEDIFF dan TIMEDIFF
DATEDIFF Fungsi DATEDIFF() digunakan untuk mencari selisih tanggal dalam hari. Fungsi ini memiliki dua buah argumen yang wajib diisi, yaitu: (1) tanggal akhir dan (2) tanggal awal. Format penulisannya adalah: DATEDIFF (tanggal_akhir, tanggal_awal)
Contoh penggunaan: SELECT DATEDIFF("2017-05-30","2016-03-25") AS selisih
Hasil:
Killer Trik Query MySQL
115
+---------+ | selisih | +---------+ | 431 | +---------+
Contoh penggunaan pada tabel: Misal kita memiliki tabel buku_pinjam yang berisi data peminjaman buku dengan data sebagai berikut: +-----------+---------+-------------+------------+-------------+ | id_pinjam | id_buku | id_peminjam | tgl_pinjam | tgl_kembali | +-----------+---------+-------------+------------+-------------+ | 1 | 1 | 001 | 2017-05-13 | 0000-00-00 | | 2 | 2 | 002 | 2017-05-10 | 0000-00-00 | | 3 | 3 | 001 | 2017-05-12 | 2017-05-15 | | 4 | 4 | 003 | 2017-05-14 | 0000-00-00 | | 5 | 3 | 004 | 2017-05-12 | 2017-05-14 | +-----------+---------+-------------+------------+-------------+
Selanjutnya kita hitung lamanya peminjaman buku, jalankan query berikut: 1. SELECT id_buku 2. , DATEDIFF(tgl_kembali, tgl_pinjam) AS lama_pinjam 3 FROM buku_pinjam;
Hasil: +---------+-------------+ | id_buku | lama_pinjam | +---------+-------------+ | 1 | NULL | | 2 | NULL | | 3 | 3 | | 4 | NULL | | 3 | 2 | +---------+-------------+
116
BAB 6. Menguasai Fungsi Scalar
TIMEDIFF Fungsi TIMEDIFF digunakan untuk menghitung beda waktu. Format penulisannya adalah sebagai berikut: SELECT TIMEDIFF(waktu_akhir, waktu_awal)
Waktu_akhir dan waktu_awal harus memiliki format sama, misal jika waktu_akhir date time, seperti 2017-06-01 07:26:00, maka waktu_awal juga harus date time, jika hanya tima, misal 07:30:00, maka output yang diperoleh tidak sesuai yang diharapkan Contoh penggunaan: 1. SELECT TIMEDIFF("07:05:30", "07:05:01");
Hasil: +----------------------------------+ | TIMEDIFF("07:05:30", "07:05:01") | +----------------------------------+ | 00:00:29 | +----------------------------------+
Contoh penggunaan pada tabel. Misal kita memiliki tabel absen sebagai berikut: +----------+------------+---------------------+ | id_absen | id_pegawai | waktu_absen | +----------+------------+---------------------+ | 1 | 1 | 2017-05-14 07:22:59 | | 2 | 1 | 2017-05-14 07:27:12 | | 3 | 2 | 2017-05-14 07:29:25 | | 4 | 3 | 2017-05-14 07:31:01 | | 5 | 4 | 2017-05-14 07:59:07 | +----------+------------+---------------------+
Killer Trik Query MySQL
117
Selanjutnya kita hitung waktu keterlambatan dengan batas waktu absen 07:30, jalankan query berikut: 1. SELECT id_pegawai, TIMEDIFF(TIME(waktu_absen), "07:30:00") 2. FROM absensi
Hasil: +------------+-----------------------------------------+ | id_pegawai | TIMEDIFF(TIME(waktu_absen), "07:30:00") | +------------+-----------------------------------------+ | 1 | -00:07:01 | | 1 | -00:02:48 | | 2 | -00:00:35 | | 3 | 00:01:01 | | 4 | 00:29:07 | | 5 | 00:30:00 | +------------+-----------------------------------------+
Pada contoh diatas, kita gunakan fungsi TIME untuk mengambil waktu dari date time yang ada pada kolom waktu_absen
6.5. Fungsi Numeric MySQL menyediakan banyak fungsi untuk memanipulasi angka (numeric), tiga fungsi terpenting yang harus dipahami adalah ROUND, CEIL, dan FLOOR
ROUND Fungsi ROUND digunakan untuk membulatkan desimal (angka di belakang koma). Jika angka di belakang koma bernilai 5 atau lebih, maka akan dibulatkan ketas, jika kurang dari 5 maka akan dibulatkan ke bawah. Adapun format penulisannya adalah: ROUND(bilangan, jumlah_desimal)
118
BAB 6. Menguasai Fungsi Scalar
Argumen jumlah desimal menunjukkan banyaknya angka di belakang koma. Argumen ini bersifat opsional, jika tidak diisi, maka akan bernilai 0. Contoh: SELECT ROUND(2.49), ROUND(2.50), ROUND(2.7159, 2), ROUND(-2.50)
Hasil: +-------------+-------------+------------------+--------------+ | ROUND(2.49) | ROUND(2.50) | ROUND(2.7159, 2) | ROUND(-2.50) | +-------------+-------------+------------------+--------------+ | 2 | 3 | 2.72 | -3 | +-------------+-------------+------------------+--------------+
Contoh
penerapan
pada
tabel:
misal
kita
memiliki
tabel
penjualan_detail sebagai berikut: +-----------+-------------+-------+------------+ | kd_barang | nama_barang | harga | harga_jual | +-----------+-------------+-------+------------+ | 1 | Mouse | 76000 | 74000 | | 2 | Flashdisk | 55000 | 55000 | | 3 | Mousepad | 35000 | 30000 | | 4 | Keyboard | 80000 | 75000 | | 5 | Kabel VGA | 45000 | 45000 | +-----------+-------------+-------+------------+
Selanjutnya kita akan menghitung nilai diskon dalam rupiah. Query yang kita jalankan: 1. SELECT kd_barang, harga, harga_jual 2. , CONCAT( ROUND( (harga - harga_jual) / harga * 100 ) 3. , "%" 4. ) AS diskon 5. FROM barang;
Hasil: +-----------+-------+------------+--------+ | kd_barang | harga | harga_jual | diskon |
Killer Trik Query MySQL
119
+-----------+-------+------------+--------+ | 1 | 76000 | 74000 | 3% | | 2 | 55000 | 55000 | 0% | | 3 | 35000 | 30000 | 14% | | 4 | 80000 | 75000 | 6% | | 5 | 45000 | 45000 | 0% | +-----------+-------+------------+--------+
FLOOR dan CEIL Fungsi FLOOR() digunakan untuk melakukan pembulatan desimal ke bawah. Format penulisannya adalah: FLOOR(bilangan)
Contoh: SELECT FLOOR(2.9), FLOOR(-2.10), FLOOR(-2.90)
Hasil: +------------+--------------+--------------+ | FLOOR(2.9) | FLOOR(-2.10) | FLOOR(-2.90) | +------------+--------------+--------------+ | 2 | -3 | -3 | +------------+--------------+--------------+
Contoh penerapan fungsi ini adalah ketika kita mencari rata-rata nilai siswa, misal kita memiliki tabel nilai sebagai berikut: +----------+---------+-------+ | id_siswa | nama | nilai | +----------+---------+-------+ | 1 | Alfa | 83 | | 2 | Beta | 77 | | 3 | Charlie | 64 | | 4 | Delta | 73 | +----------+---------+-------+
Query yang kita jalankan: 1. SELECT AVG(nilai) AS rata_rata,
120
BAB 6. Menguasai Fungsi Scalar
2. FLOOR( AVG(nilai) ) AS floor_rata_rata 3. FROM nilai
Hasil: +-----------+-----------------+ | rata_rata | floor_rata_rata | +-----------+-----------------+ | 74.25 | 74 | +-----------+-----------------+
Pada contoh diatas, pertama-tama MySQL akan menjalankan fungsi AVG()
untuk menghitung rata-rata nilai, selanjutnya MySQL
menjalankan fungsi FLOOR() untuk melakukan pembulatan ke bawah. Kebalikan dari fungsi FLOOR(), fungsi CEIL() digunakan untuk melakukan pembulatan ke atas, misal kita ingin mencari nilai-ratarata dan membulatkannya ke atas, kita jalankan query berikut: 1. SELECT AVG(nilai) AS rata_rata, 2. FLOOR( AVG(nilai) ) AS floor_rata_rata, 3. CEIL( AVG(nilai) ) AS ceil_rata_rata 4. FROM nilai
Hasil yang kita peroleh: +-----------+-----------------+----------------+ | rata_rata | floor_rata_rata | ceil_rata_rata | +-----------+-----------------+----------------+ | 74.25 | 74 | 75 | +-----------+-----------------+----------------+
Killer Trik Query MySQL
121
Halaman ini sengaja dikosongkan Jagowebdev.com
122
BAB 6. Menguasai Fungsi Scalar
BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika Pada bab bab sebelumnya, kita telah membahas fungsi agregasi, fungsi scalar, dan ekspresi logika, pada bab ini, ketiganya akan kita bahas lebih dalam, karena topik tersebut sering digunakan dalam praktik.
7.1. Fungsi Scalar Pada WHERE Fungsi scalar dapat digunakan di berbagai tempat, selain pada klausa SELECT, fungsi ini sering digunakan pada klausa WHERE, misal kita akan menampilkan data penjualan bulan maret tahun 2017 pada tabel penjualan berikut ini: +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 1 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Query yang kita jalankan: 1. SELECT id_pelanggan, tgl_trx, total_trx 2. FROM penjualan 3. WHERE MONTH(tgl_trx) = 4 AND YEAR(tgl_trx) = 2017
Hasil:
Killer Trik Query MySQL
123
+--------------+------------+-----------+ | id_pelanggan | tgl_trx | total_trx | +--------------+------------+-----------+ | 1 | 2017-04-10 | 259000 | | 2 | 2017-04-05 | 110000 | +--------------+------------+-----------+
Penjelasan: Pada bagian awal telah di bahas bahwa ketika menjalankan klausa WHERE, MySQL akan memerika satu per satu baris tabel yang dihasilkan klausa FROM, jika baris tersebut memenuhi kriteria klausa WHERE, maka ambil baris tersebut. Perhatikan ilustrasi berikut:
Gambar 7.1 Ilustrasi Urutan Eksekusi Query
124
BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika
7.2. Fungsi Scalar Pada GROUP BY Selain pada WHERE, fungsi Scalar juga sering digunakan pada klausa GROUP BY, misal kita ambil data penjualan tahun 2017 dan kita kelompokkan data tersebut berdasarkan bulan transaksi. Query yang kita jalankan adalah: 1. 2. 3. 4.
SELECT MONTH(tgl_trx) AS bulan, SUM(total_trx) AS total FROM penjualan WHERE YEAR(tgl_trx) = 2017 GROUP BY bulan
Hasil: +-------+--------+ | bulan | total | +-------+--------+ | 3 | 378000 | | 4 | 369000 | +-------+--------+
Bagaimana alur dari query diatas? Seperti konsep eksekusi query: Pertama, MySQL akan mengeksekusi klausa FROM dan WHERE, sehingga akan terbentuk temporary tabel sebagai berikut: +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 1 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | +--------+--------------+------------+-----------+
Kedua,
MySQL
akan
mengeksekusi
klausa
SELECT,
dan
menambahkan kolom baru (jika ada), namun untuk fungsi agregasi, MySQL akan menjalankannya bersamaan dengan klausa GROUP BY,
Killer Trik Query MySQL
125
dengan demikian, MySQL akan membentuk temporary tabel lagi seperti ini: +--------+--------------+------------+-----------+-------+ | id_trx | id_pelanggan | tgl_trx | total_trx | bulan | +--------+--------------+------------+-----------+-------+ | 1 | 1 | 2017-03-02 | 192000 | 3 | | 2 | 1 | 2017-03-10 | 186000 | 3 | | 3 | 1 | 2017-04-10 | 259000 | 4 | | 4 | 2 | 2017-04-05 | 110000 | 4 | +--------+--------------+------------+-----------+-------+
Ketiga, MySQL akan menjalankan klausa GROUP BY bersama dengan fungsi agregasi SUM, kemudian mengambil kolom sesuai dengan statemen SELECT sehingga diperoleh hasil: +-------+--------+ | bulan | total | +-------+--------+ | 3 | 378000 | | 4 | 369000 | +-------+--------+
Perhatikan ilustrasi berikut:
126
BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika
Gambar 7.2 Ilustrasi Urutan Eksekusi Query
Jika tanpa kolom alias, maka query menjadi: 1. 2. 3. 4.
SELECT MONTH(tgl_trx), SUM(total_trx) AS total FROM penjualan WHERE YEAR(tgl_trx) = 2017 GROUP BY MONTH(tgl_trx)
7.3. Studi Kasus Dalam praktik, kita akan sering menemukan kondisi dimana kita harus menggunakan beberapa fungsi sekaligus, baik fungsi logika IF, Fungsi Agregasi, maupun Fungsi Scalar.
Killer Trik Query MySQL
127
Kondisi di lapangan bisa bermacam macam, namun yang terpenting Anda paham konsepnya, bagaimana query dieksekusi dan bagaimana tabel output dihasilkan. Mari kita lanjutkan…. Contoh kasus ini akan menguji pemahaman Anda mengenai fungsi logika IF, Fungsi Agregasi, dan Fungsi Scalar Tabel yang akan kita gunakan adalah tabel pelanggan dan tabel penjualan sebagai berikut: pelanggan +--------------+---------+-----------+---------+ | id_pelanggan | nama | alamat | id_staf | +--------------+---------+-----------+---------+ | 1 | Alfa | Jakarta | 1 | | 2 | Beta | Semarang | 1 | | 3 | Charlie | Surabaya | 2 | | 4 | Delta | Surakarta | 3 | +--------------+---------+-----------+---------+ penjualan +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 1 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Selanjutnya kita akan membuat resume total semua penjualan per pelanggan per bulan untuk tahun 2017 dengan format sebagai berikut: +--------------+------+---------+----------+--------+--------+ | id_pelanggan | nama | Januari | Februari | Maret | April | +--------------+------+---------+----------+--------+--------+ | 1 | Alfa | 0 | 0 | 378000 | 259000 | | 2 | Beta | 0 | 0 | 0 | 110000 | +--------------+------+---------+----------+--------+--------+
128
BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika
Silakan Anda coba, pasti bisa… !!! Tips: selalu gunakan konsep alur eksekusi query dan fungsi yang telah kita pelajari… Sudah… ? Oke, mari kita bahas…. Pertama… Pertama selalu kita buat klausa FROM nya terlebih dahulu dan jika perlu klausa WHERE Dengan menganalisa tabel output, maka dapat disimpulkan bahwa kita perlu menggunakan kedua tabel (tabel pelanggan dan tabel penjualan) selain itu, kita gunakan klausa WHERE untuk membatasi data yang diambil hanya data tahun 2017. Karena kita akan menggabungkan kedua tabel maka kita perlu menggunakan JOIN, dan pilihannya ada dua JOIN atau LEFT JOIN. Nah karena fokus kita adalah per penjualan maka kita akan menampilkan semua data penjualan, untuk itu kita gunakan LEFT JOIN dan kita letakkan tabel penjualan disebelah kiri Kita coba hasil penggabungannya dengan klausa SELECT * sebagai berikut: 1. SELECT * 2. FROM penjualan 3. LEFT JOIN pelanggan USING(id_pelanggan) 4. WHERE YEAR(tgl_trx) = 2017
Hasilnya adalah:
Killer Trik Query MySQL
129
+--------------+--------+------------+-----------+------+----------+---------+ | id_pelanggan | id_trx | tgl_trx | total_trx | nama | alamat | id_staf | +--------------+--------+------------+-----------+------+----------+---------+ | 1 | 1 | 2017-03-02 | 192000 | Alfa | Jakarta | 1 | | 1 | 2 | 2017-03-10 | 186000 | Alfa | Jakarta | 1 | | 2 | 4 | 2017-04-05 | 110000 | Beta | Semarang | 1 | | 0 | 3 | 2017-04-10 | 259000 | NULL | NULL | NULL | +--------------+--------+------------+-----------+------+----------+---------+
Sekarang kita cek, apakah semua transaksi sudah muncul? dan tahun transaksinya sudah 2017? Yub, benar, berarti penggabungan sudah benar… Kedua… Selanjutnya kita susun klausa SELECT Sebelumnya mari kita analisa kolom pada tabel output: 1. Kolom id_pelanggan dan nama pelanggan, keduanya sudah ada pada tabel hasil klausa FROM, tinggal kita ambil 2. Kolom Januari, Februari, Maret, dan April belum ada, maka kita buat kolom tersebut. Ingat bab Kunci Menguasai SELECT, untuk membuat kolom baru, kita definisikan kolom tersebut pada bagian SELECT Bagaimana kita mendapatkan penjualan bulan Januari, Februari, dst… ? Jika kita amati tabel hasil klausa FROM, untuk mendapatkan data bulan, kita harus mengambil bulan pada kolom tgl_trx, maka query yang kita perlukan adalah sebagai berikut: 1. SELECT id_pelanggan 2. , nama 3. , IF(MONTH(tgl_trx) = 1, total_trx, 0) AS Januari 4. , IF(MONTH(tgl_trx) = 2, total_trx, 0) AS Februari
130
BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika
5. , IF(MONTH(tgl_trx) = 3, total_trx, 0) AS Maret 6. , IF(MONTH(tgl_trx) = 4, total_trx, 0) AS April 7. FROM penjualan 8. LEFT JOIN pelanggan USING (id_pelanggan) 9. WHERE YEAR(tgl_trx) = 2017
Hasilnya adalah: +--------------+------+---------+----------+--------+--------+ | id_pelanggan | nama | Januari | Februari | Maret | April | +--------------+------+---------+----------+--------+--------+ | 1 | Alfa | 0 | 0 | 192000 | 0 | | 1 | Alfa | 0 | 0 | 186000 | 0 | | 2 | Beta | 0 | 0 | 0 | 110000 | | 0 | NULL | 0 | 0 | 0 | 259000 | +--------------+------+---------+----------+--------+--------+
Bagaimana itu bisa terjadi? Kita flash back lagi tentang fungsi IF, jika argumen paling kiri bernilai benar, maka eksekusi argumen bagian tengah, jika salah eksekusi argumen bagian paling kanan. Berikut ini ilustrasinya ( kolom Januari dan Februari dihilangkan untuk menyederhanakan tampilan ).
Gambar 7.3 Ilustrasi Eksekusi Query
Killer Trik Query MySQL
131
Ketiga… Selanjutnya kita jumlahkan kolom Januari, Februari, dst… untuk mendapatkan nilai total per bulan per pelanggan. Karena data dikelompokkan per pelanggan, jangan lupa untuk menambahkan klausa GROUP BY Bentuk query jadinya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
SELECT id_pelanggan , nama , SUM(IF(MONTH(tgl_trx) = 1, total_trx, , SUM(IF(MONTH(tgl_trx) = 2, total_trx, , SUM(IF(MONTH(tgl_trx) = 3, total_trx, , SUM(IF(MONTH(tgl_trx) = 4, total_trx, FROM penjualan LEFT JOIN pelanggan USING (id_pelanggan) WHERE YEAR(tgl_trx) = 2017 GROUP BY id_pelanggan
0)) 0)) 0)) 0))
AS AS AS AS
Januari Februari Maret April
Hasil: +--------------+------+---------+----------+--------+--------+ | id_pelanggan | nama | Januari | Februari | Maret | April | +--------------+------+---------+----------+--------+--------+ | 0 | NULL | 0 | 0 | 0 | 259000 | | 1 | Alfa | 0 | 0 | 378000 | 0 | | 2 | Beta | 0 | 0 | 0 | 110000 | +--------------+------+---------+----------+--------+--------+
Penjelasan: lihat pembahasan pada sub bab sebelumnya Fungsi Scalar Pada GROUP BY
132
BAB 7 Pendalaman Materi: Fungsi Agregasi, Skalar, Logika
BAB 8 Mempertajam Agregasi dan JOIN Untuk mempertajam pemahaman Anda tentang bagaimana Fungsi Agregasi dan klausa JOIN bekerja serta mengasah kemampuan Anda memecahkan permasalahan terkait penyusunan query SQL, mari kita bahas beberapa contoh kasus.
8.1. Studi Kasus #1 Sebagai latihan, kita coba dengan kasus sederhana… Misal kita memiliki tabel mhs yang berisi data mahasiswa dan tabel mhs_status yang berisi data kelulusan mahasiswa sebagai berikut: mhs +------+---------+---------------+------------+ | nim | nama | jenis_kelamin | kd_jurusan | +------+---------+---------------+------------+ | 001 | Alfa | L | J002 | | 002 | Beta | P | J002 | | 003 | Charlie | P | J001 | | 004 | Delta | L | J001 | | 005 | Erdhi | L | J001 | | 006 | Farah | P | J002 | | 007 | Gisel | P | J002 | +------+---------+---------------+------------+ mhs_status +------+-------------+ | nim | status | +------+-------------+ | 001 | LULUS | | 002 | TIDAK LULUS | | 003 | TIDAK LULUS | | 004 | LULUS | | 005 | TIDAK LULUS | | 006 | LULUS | | 007 | LULUS | +------+-------------+
Killer Trik Query MySQL
133
Selanjutnya kita akan menampilkan data kelulusan berdasarkan jenis kelamin dengan output sebagai berikut: +--------------+---+---+ | status_lulus | L | P | +--------------+---+---+ | LULUS | 2 | 2 | | TIDAK LULUS | 1 | 2 | | TOTAL | 3 | 4 | +--------------+---+---+
Bagaimana query nya? Seperti yang telah kita pelajari, pertama kita akan menyusun tabel pada klausa FROM untuk diolah lebih lanjut pada klausa SELECT. Jika kita perhatikan tabel output, maka kita perlu data jenis kelamin dan data kelulusan, keduanya ada di dua tabel yang berbeda, maka kita perlu menggabungkan kedua tabel tersebut. Penggabungan kedua tabel tersebut kita lakukan menggunakan JOIN, sudah tahu kan kenapa pakai JOIN? Selanjutnya, jenis JOIN yang kita gunakan adalah LEFT JOIN dan tabel di sebelah kiri adalah tabel mhs_status, kenapa? Karena kita ingin menampilkan semua status Mari kita tes bentuk tabel hasil penggabungan: 1. SELECT * 2. FROM mhs 3. LEFT JOIN mhs_status USING (nim)
Hasil: +------+-------------+---------+---------------+------------+ | nim | status | nama | jenis_kelamin | kd_jurusan | +------+-------------+---------+---------------+------------+ | 001 | LULUS | Alfa | L | J002 | | 002 | TIDAK LULUS | Beta | P | J002 |
134
BAB 8 Mempertajam Agregasi dan JOIN
| 003 | TIDAK LULUS | Charlie | P | J001 | | 004 | LULUS | Delta | L | J001 | | 005 | TIDAK LULUS | Erdhi | L | J001 | | 006 | LULUS | Farah | P | J002 | | 007 | LULUS | Gisel | P | J002 | +------+-------------+---------+---------------+------------+
Dari tabel tersebut, selanjutnya kita susun klausa SELECT sehingga menghasilkan output sesuai yang kita inginkan. Pada tabel output, terdapat kolom baru, yaitu L dan P, kolom ini tidak ada pada tabel hasil klausa FROM, untuk itu kita perlu membuatnya. Bagaimana membuatnya? Sebelum menambah kolom pada klaua SELECT, ingat kembali latihan sebelumnya, ketika tabel output berupa penghitungan baris, maka jika perlu kita "tarik mundur" bentuk tabel outputnya. Jika tabel output kita tarik mundur, kita akan memperoleh tabel seperti ini: +-------------+------+------+ | status | L | P | +-------------+------+------+ | LULUS | 1 | NULL | | TIDAK LULUS | NULL | 1 | | TIDAK LULUS | NULL | 1 | | LULUS | 1 | NULL | | TIDAK LULUS | 1 | NULL | | LULUS | NULL | 1 | | LULUS | NULL | 1 | +-------------+------+------+
Selanjutnya, kita tambahkan kolom L dan P pada klausa SELECT menggunakan fungsi IF. Fungsi IF ini nantinya digunakan untuk menguji nilai tiap baris pada kolom jenis kelamin, untuk mendapatkan nilai NULL dan 1. Querynya adalah sebagai berikut:
Killer Trik Query MySQL
135
1. SELECT status, 2. IF(jenis_kelamin = "L", 1, NULL) AS L, 3. IF(jenis_kelamin = "P", 1, NULL) AS P 4. FROM mhs 5. LEFT JOIN mhs_status USING (nim)
Hasil: +-------------+------+------+ | status | L | P | +-------------+------+------+ | LULUS | 1 | NULL | | TIDAK LULUS | NULL | 1 | | TIDAK LULUS | NULL | 1 | | LULUS | 1 | NULL | | TIDAK LULUS | 1 | NULL | | LULUS | NULL | 1 | | LULUS | NULL | 1 | +-------------+------+------+
Selanjutnya karena kita ingin menghitung baris, kita gunakan fungsi COUNT. Jangan lupa menambahkan klausa GROUP BY status karena kita ingin mengelompokkannya berdasarkan statusnya. Bentuk query jadinya adalah sebagai berikut: 1. 2. 3. 4. 5. 6.
SELECT status, COUNT(IF(jenis_kelamin = "L", 1, NULL)) AS L, COUNT(IF(jenis_kelamin = "P", 1, NULL)) AS P FROM mhs LEFT JOIN mhs_status USING (nim) GROUP BY status
Hasil: +-------------+---+---+ | status | L | P | +-------------+---+---+ | LULUS | 2 | 2 | | TIDAK LULUS | 1 | 2 | +-------------+---+---+
136
BAB 8 Mempertajam Agregasi dan JOIN
Hasil diatas sudah mendekati apa yang kita harapkan, tinggal menambahkan baris total. Selanjutnya, untuk menambah baris total, kita tambahkan klausa
WITH ROLLUP sebagai berikut: 1. 2. 3. 4. 5. 6. 7.
SELECT COALESCE(status, "TOTAL") AS status_lulus, COUNT(IF(jenis_kelamin = "L", 1, NULL)) AS L, COUNT(IF(jenis_kelamin = "P", 1, NULL)) AS P FROM mhs LEFT JOIN mhs_status USING (nim) GROUP BY status WITH ROLLUP
Hasil: +--------------+---+---+ | status_lulus | L | P | +--------------+---+---+ | LULUS | 2 | 2 | | TIDAK LULUS | 1 | 2 | | TOTAL | 3 | 4 | +--------------+---+---+
Pada contoh diatas, kita gunakan fungsi COALESCE untuk mengganti nilai NULL pada baris total dengan kata TOTAL. Fungsi COALESCE akan mencari nilai hingga ditemukan nilai bukan NULL, fungsi ini sama dengan fungsi IFNULL(), misal IFNULL(status, "TOTAL"), bedanya fungsi IFNULL hanya dapat
menerima dua argumen, sedangkan COALESCE bisa lebih dari satu, misal COALESCE(status, nama, "TOTAL")
8.2. Studi Kasus #2 Pada latihan kedua ini kita akan menampilkan data nama barang beserta jumlah yang masuk ke gudang selama bulan Mei 2017. Tabel yang akan kita gunakan adalah sebagai berikut:
Killer Trik Query MySQL
137
barang +-----------+-------------+------+-------+ | kd_barang | nama_barang | stok | harga | +-----------+-------------+------+-------+ | 1 | Mouse | 14 | 76000 | | 2 | Flashdisk | 15 | 55000 | | 3 | Mousepad | 17 | 35000 | | 4 | Keyboard | 12 | 80000 | | 5 | Kabel VGA | 7 | 45000 | +-----------+-------------+------+-------+ barang_masuk +------------+-----------+-------------+-----------+ | tgl_masuk | kd_barang | kd_supplier | jml_masuk | +------------+-----------+-------------+-----------+ | 2017-05-02 | 1 | 1 | 14 | | 2017-05-03 | 1 | 2 | 5 | | 2017-05-04 | 2 | 2 | 13 | | 2017-05-04 | 3 | 1 | 4 | | 2017-05-05 | 4 | 3 | 10 | | 2017-04-26 | 1 | 1 | 7 | +------------+-----------+-------------+-----------+
Selanjutnya kita tampilkan semua nama_barang beserta jumlah barang masuk untuk tiap tiap jenis barang dengan tampilan output sebagai berikut: +-------------+-----------+ | nama_barang | jml_masuk | +-------------+-----------+ | Mouse | 19 | | Flashdisk | 13 | | Mousepad | 4 | | Keyboard | 10 | | Kabel VGA | NULL | +-------------+-----------+
Silakan dicoba ya…. Sudah… ? Oke, mari kita bahas…
138
BAB 8 Mempertajam Agregasi dan JOIN
Pertama… Seperti biasa, mari kita analisa data pada tabel output. Pada tabel tersebut kita akan menampilkan data nama barang dan total jml_masuk. Keduanya ada di tabel barang dan barang_masuk, untuk itu mari kita gabungkan kedua tabel tersebut: 1. SELECT * 2. FROM barang 3. LEFT JOIN barang_masuk USING(kd_barang)
Hasil yang kita peroleh: +-----------+-------------+------+-------+------------+-------------+-----------+ | kd_barang | nama_barang | stok | harga | tgl_masuk | kd_supplier | jml_masuk | +-----------+-------------+------+-------+------------+-------------+-----------+ | 1 | Mouse | 14 | 76000 | 2017-05-02 | 1 | 14 | | 1 | Mouse | 14 | 76000 | 2017-05-03 | 2 | 5 | | 2 | Flashdisk | 15 | 55000 | 2017-05-04 | 2 | 13 | | 3 | Mousepad | 17 | 35000 | 2017-05-04 | 1 | 4 | | 4 | Keyboard | 12 | 80000 | 2017-05-05 | 3 | 10 | | 1 | Mouse | 14 | 76000 | 2017-04-26 | 1 | 7 | | 5 | Kabel VGA | 7 | 45000 | NULL | NULL | NULL | +-----------+-------------+------+-------+------------+-------------+-----------+
Kedua… Selanjutnya kita definisikan kolom pada klausa SELECT. Jika memperhatikan tabel output, maka kolom yang akan kita gunakan adalah nama_barang dan jml_masuk (total per jenis barang) selain itu, data yang ditampilkan hanya data bulan mei 2017 Maka, query yang kita perlukan adalah: 1. 2. 3. 4. 5.
SELECT nama_barang, SUM(jml_masuk) jml_masuk FROM barang LEFT JOIN barang_masuk USING(kd_barang) WHERE (YEAR(tgl_masuk) = 2017 AND MONTH(tgl_masuk) = 5) GROUP BY kd_barang
Killer Trik Query MySQL
139
Perhatikan bahwa pada query diatas kita tambahkan klausa WHERE untuk mendapatkan data bulan Mei 2017. Hasil yang kita peroleh adalah: +-------------+-----------+ | nama_barang | jml_masuk | +-------------+-----------+ | Mouse | 19 | | Flashdisk | 13 | | Mousepad | 4 | | Keyboard | 10 | +-------------+-----------+
Yes, behasil..…
Eitss, Tapi tunggu dulu….. Adakah anda melihat keanehan disana? Yup, ternyata terdapat data barang yang hilang, yaitu kabel VGA. Kenapa bisa begitu? Coba perhatikan tabel penggabungan pada bagian pertama. Ternyata kita dapati bahwa untuk kabel VGA, data pada kolom barang_masuk (tgl_masuk, kd_supplier, dan jml_masuk) nilainya NULL semua. Selanjutnya, pada klausa WHERE kita beri filter tahun dan bulan pada tgl_masuk yang mengakibatkan data tgl_masuk yang bernilai NULL tidak masuk dalam kriteria sehingga data tersebut tidak ditampilkan. Bagaimana mengatasinya? Caranya, kita tambahkan kriteria pada klausa WHERE sehingga data dengan tgl_masuk NULL ikut masuk, ubah querynya menjadi seperti ini:
140
BAB 8 Mempertajam Agregasi dan JOIN
1. 2. 3. 5. 6. 7. 8.
SELECT nama_barang, SUM(jml_masuk) jml_masuk FROM barang LEFT JOIN barang_masuk USING(kd_barang) WHERE (YEAR(tgl_masuk) = 2017 AND MONTH(tgl_masuk) = 5) OR tgl_masuk IS NULL GROUP BY kd_barang
Hasilnya adalah: +-------------+-----------+ | nama_barang | jml_masuk | +-------------+-----------+ | Mouse | 19 | | Flashdisk | 13 | | Mousepad | 4 | | Keyboard | 10 | | Kabel VGA | NULL | +-------------+-----------+
Yes… berhasil Sekali lagi, untuk menguji nilai NULL, kita tidak bisa menggunakan operator = atau != seperti tgl_masuk = NULL melainkan kita harus menggunakan IS atau IS NOT Perhatikan pada contoh diatas, kita menggunakan tanda kurung pada klausa WHERE, kenapa? Ingat kembali pembahasan tentang operator OR dan AND. Jika tanpa tanda kurung, maka kondisi yang dievaluasi adalah:
1. Tahun 2017 dan bulan 5:
YEAR(tgl_masuk)
=
2017
AND
MONTH(tgl_masuk)
2. Dan bulan 5 atau tgl_masuk NULL: MONTH(tgl_masuk) = 5) OR tgl_masuk IS NULL
Sedangkan dengan tanda kurung, filter menjadi:
Killer Trik Query MySQL
141
1. Tahun 2017 dan bulan 5 atau tgl_masuk NULL Ingat kembali, pada operator AND dan OR, maka yang pertama dievaluasi
adalah AND baru kemudian OR,
namun jika ada tanda kurung, maka pertama kali yang dievaluasi adalah yang ada di dalam tanda kurung Pada klausa WHERE jika menggunakan operator AND dan OR bersama sama, selalu gunakan tanda kurung untuk membuat prioritas kondisi yang ingin dievaluasi.
8.3. Studi Kasus #3 Untuk menguji pemahaman Anda tentang apa yang telah kita bahas sejauh ini, pada studi kasus kali ini kita akan menggunakan tiga tabel sekaligus yaitu tabel mhs, mhs_status, dan mhs_jurusan sebagai berikut: mhs +------+---------+---------------+------------+ | nim | nama | jenis_kelamin | kd_jurusan | +------+---------+---------------+------------+ | 001 | Alfa | L | J002 | | 002 | Beta | P | J002 | | 003 | Charlie | P | J001 | | 004 | Delta | L | J001 | | 005 | Erdhi | L | J001 | | 006 | Farah | P | J002 | | 007 | Gisel | P | J002 | | 008 | Haris | L | NULL | +------+---------+---------------+------------+ mhs_status +------+-------------+ | nim | status | +------+-------------+ | 001 | LULUS | | 002 | TIDAK LULUS | | 003 | TIDAK LULUS | | 004 | LULUS | | 005 | TIDAK LULUS | | 006 | LULUS |
142
BAB 8 Mempertajam Agregasi dan JOIN
| 007 | LULUS | +------+-------------+ mhs_jurusan +------------+--------------+ | kd_jurusan | nama_jurusan | +------------+--------------+ | J001 | MANAJEMEN | | J002 | AKUNTANSI | | J003 | TEKNIK | +------------+--------------+
Selanjutnya kita akan menampilkan data status (LULUS dan TIDAK LULUS) yang dikelompokkan berdasarkan jurusan dan jenis kelamin, tabel outputnya adalah sebagai berikut: +-------------+-------------+-------------+-------------+-------------+ | status | manajemen_l | manajemen_p | akuntansi_l | akuntansi_p | +-------------+-------------+-------------+-------------+-------------+ | LULUS | 1 | 0 | 1 | 2 | | TIDAK LULUS | 1 | 1 | 0 | 1 | +-------------+-------------+-------------+-------------+-------------+
Sebelum lanjut, sebagiknya Anda coba terlebih dahulu sambil berlatih, paling membutuhkan waktu 5-10 menit, practice make perfect, right? Sudah…? Oke, mari kita bahas….
Pertama… Seperti prinsip yang telah kita bahas, pertama kita analisa tabel output. Pada tabel tersebut terdapat data jumlah jenis kelamin, status, dan jurusan, ketiganya ada di ketiga tabel, untuk itu, kita perlu menggabungkan ketiga tabel. Kali ini jenis penggabungan yang kita gunakan adalah LEFT JOIN dan kita tempatkan tabel mhs di paling kiri. Mari kita tes hasil penggabungan menggunakan statemen SELECT sebagai berikut
Killer Trik Query MySQL
143
1. 2. 3. 4.
SELECT * FROM mhs LEFT JOIN mhs_status USING (nim) LEFT JOIN mhs_jurusan USING (kd_jurusan)
Hasil yang kita peroleh: +------------+------+---------+---------------+-------------+--------------+ | kd_jurusan | nim | nama | jenis_kelamin | status | nama_jurusan | +------------+------+---------+---------------+-------------+--------------+ | J001 | 003 | Charlie | P | TIDAK LULUS | MANAJEMEN | | J001 | 004 | Delta | L | LULUS | MANAJEMEN | | J001 | 005 | Erdhi | L | TIDAK LULUS | MANAJEMEN | | J002 | 001 | Alfa | L | LULUS | AKUNTANSI | | J002 | 002 | Beta | P | TIDAK LULUS | AKUNTANSI | | J002 | 006 | Farah | P | LULUS | AKUNTANSI | | J002 | 007 | Gisel | P | LULUS | AKUNTANSI | | NULL | 008 | Haris | L | NULL | NULL | +------------+------+---------+---------------+-------------+--------------+
Apakah sudah terbayang query SELECT yang akan kita jalankan? Sudah kan? Ya, karena kondisi pada kasus #3 ini mirip dengan kasus #2
Kedua… Ok, mari kita susun kolom pada klausa SELECT nya. Jika kita analisa tabel output, terdapat kolom baru yaitu manajemen_l, namajemen_p, akuntansi_l, dan akuntansi_p. Selain itu kita akan menghitung jumlah baris, untuk itu, tabel yang kita perlukan adalah: +-------------+-------------+-------------+-------------+-------------+ | status | manajemen_l | manajemen_p | akuntansi_l | akuntansi_p | +-------------+-------------+-------------+-------------+-------------+ | TIDAK LULUS | NULL | 1 | NULL | NULL | | LULUS | 1 | NULL | NULL | NULL | | TIDAK LULUS | 1 | NULL | NULL | NULL | | LULUS | NULL | NULL | 1 | NULL | | TIDAK LULUS | NULL | NULL | NULL | 1 | | LULUS | NULL | NULL | NULL | 1 | | LULUS | NULL | NULL | NULL | 1 | | NULL | NULL | NULL | NULL | NULL | +-------------+-------------+-------------+-------------+-------------+
144
BAB 8 Mempertajam Agregasi dan JOIN
Bagaimana querynya? Masih ingat kaidah penggunaan fungsi IF ? Nah, kali ini sama seperti kasus #2, kita perlu menguji nilai baris satu per satu apakah:
Nama jurusan manajemen dan jenis kelamin L, jika ya, beri nilai 1, jika tidak, beri nilai NULL dan letakkan pada kolom pertama
Nama jurusan manajemen dan jenis kelamin P, jika ya, beri nilai 1, jika tidak, beri nilai NULL dan letakkan pada kolom kedua
Nama jurusan akuntansi dan jenis kelamin L, jika ya, beri nilai 1, jika tidak, beri nilai NULL dan letakkan pada kolom ketiga
Nama jurusan akuntansi dan jenis kelamin P, jika ya, beri nilai 1, jika tidak, beri nilai NULL dan letakkan pada kolom keempat
Setelah kita tahu alur logikanya, mari kita susun querynya… Querynya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.
SELECT status, IF( jenis_kelamin = "L" AND kd_jurusan , 1, NULL) AS manajemen_l, IF( jenis_kelamin = "P" AND kd_jurusan , 1, NULL) AS manajemen_p, IF( jenis_kelamin = "L" AND kd_jurusan , 1, NULL) AS akuntansi_l, IF( jenis_kelamin = "P" AND kd_jurusan , 1, NULL) AS akuntansi_p FROM mhs_status LEFT JOIN mhs USING (nim) LEFT JOIN mhs_jurusan USING (kd_jurusan)
= "J001" = "J001" = "J002" = "J002"
Selanjutnya kita tambahkan fungsi COUNT dan klausa GROUP BY status sebagai berikut: 1. 2.
SELECT status, COUNT(IF( jenis_kelamin = "L" AND kd_jurusan = "J001"
Killer Trik Query MySQL
145
3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.
, 1, NULL)) AS manajemen_l, COUNT(IF( jenis_kelamin = "P" AND kd_jurusan = "J001" , 1, NULL)) AS manajemen_p, COUNT(IF( jenis_kelamin = "L" AND kd_jurusan = "J002" , 1, NULL)) AS akuntansi_l, COUNT(IF( jenis_kelamin = "P" AND kd_jurusan = "J002" , 1, NULL)) AS akuntansi_p FROM mhs_status LEFT JOIN mhs USING (nim) LEFT JOIN mhs_jurusan USING (kd_jurusan) GROUP BY status
Query diatas sama denan studi kasus #2, bedanya kita hanya menambahkan kondisi pada fungsi IF dengan AND jd_jurusan... dan kita join kan tabel mhs_jurusan
Hasil yang kita peroleh adalah: +-------------+-------------+-------------+-------------+-------------+ | status | manajemen_l | manajemen_p | akuntansi_l | akuntansi_p | +-------------+-------------+-------------+-------------+-------------+ | NULL | 0 | 0 | 0 | 0 | | LULUS | 1 | 0 | 1 | 2 | | TIDAK LULUS | 1 | 1 | 0 | 1 | +-------------+-------------+-------------+-------------+-------------+
Yes! berhasil Tapi tunggu, apakah Anda melihat keanehan? Ya!!, ternyata ada status dengan nilai NULL !! Mengapa demikian? Hal ini disebabkan karena terdapat data mahasiswa yang tidak ada di tabel mhs_status masih ingat kaidah pada LEFT JOIN kan? Bagaimana mengatasinya? Caranya tinggal dibalik posisi tabel nya, sehingga tabel mhs_status berada di sebelah kiri dan tabel mhs berada di sebelah kanan, sehingga querynya menjadi:
146
BAB 8 Mempertajam Agregasi dan JOIN
1. 2. 3. 4. 5.
SELECT ... FROM mhs_status LEFT JOIN mhs USING (nim) LEFT JOIN mhs_jurusan USING (kd_jurusan) GROUP BY status
Ingat kembali: Karena kita selalu menggunakan LEFT JOIN, maka tempatkan tabel yang ingin kita tampilkan semua datanya di sebelah kiri. Pada contoh diatas karena kita ingin menampilkan semua status, maka kita tempatkan tabel mhs_status di paling kiri
Selain cara diatas, terdapat cara lain untuk menghilangkan baris NULL yaitu menggunakan klausa WHERE sebagai berikut: 1. 2. 3. 4. 5. 6.
SELECT ... FROM mhs LEFT JOIN mhs_status USING (nim) LEFT JOIN mhs_jurusan USING (kd_jurusan) WHERE status IS NOT NULL GROUP BY status
Pada contoh diatas, kita menggunakan IS NOT NULL Namun demikian, agar selalu konsisten, selalu tempatkan tabel yang ingin ditampilkan semua datanya di sebelah kiri. Ingat kembali: NULL tidak bisa menggunakan operator apapun, hanya bisa menggunakan IS NULL atau IS NOT NULL
8.4. Studi Kasus #4 Pada studi kasus kali ini, kita akan menguji pemahaman Anda mengenai fungsi agregasi, fungsi IF, dan Join.
Killer Trik Query MySQL
147
Misal dari tabel mahasiswa yang digunakan pada studi kasus #3, yaitu mhs, mhs_jurusan, dan mhs_status kita akan membuat resume jumlah mahasiswa yang lulus dan yang tidak lulus yang dikelompokkan per jurusan, beserta persentasenya. Tabel output yang kita inginkan adalah sebagai berikut: +--------------+------------------+-------+-------------+ | Nama Jurusan | Jumlah Mahasiswa | Lulus | Tidak Lulus | +--------------+------------------+-------+-------------+ | MANAJEMEN | 3 | 1 | 2 | | AKUNTANSI | 4 | 3 | 1 | | TEKNIK | 0 | 0 | 0 | +--------------+------------------+-------+-------------+
Bagaimana querynya?
Pertama… Kita buat hubungan ketiga tabel menggunakan JOIN dan kita tempatkan tabel mhs_jurusan di paling kiri, kenapa? Karena data nama jurusan akan kita tampilkan semua. Selanjutnya kita tes hasil penggabungan nya, jalankan query berikut: 1. 2. 3. 4.
SELECT * FROM mhs_jurusan LEFT JOIN mhs USING (kd_jurusan) LEFT JOIN mhs_status USING (nim)
Hasil: +------+------------+--------------+---------+---------------+-------------+ | nim | kd_jurusan | nama_jurusan | nama | jenis_kelamin | status | +------+------------+--------------+---------+---------------+-------------+ | 001 | J002 | AKUNTANSI | Alfa | L | LULUS | | 002 | J002 | AKUNTANSI | Beta | P | TIDAK LULUS | | 003 | J001 | MANAJEMEN | Charlie | P | TIDAK LULUS | | 004 | J001 | MANAJEMEN | Delta | L | LULUS | | 005 | J001 | MANAJEMEN | Erdhi | L | TIDAK LULUS | | 006 | J002 | AKUNTANSI | Farah | P | LULUS | | 007 | J002 | AKUNTANSI | Gisel | P | LULUS | | NULL | J003 | TEKNIK | NULL | NULL | NULL | +------+------------+--------------+---------+---------------+-------------+
148
BAB 8 Mempertajam Agregasi dan JOIN
Kedua… Kedua, kita susun kolom pada klausa SELECT, namun sebelumnya kita analisa terlebih dahulu kolom pada tabel output dengan memperhatikan tabel hasil eksekusi klausa FROM: 1. Nama Jurusan. Kolom ini sudah ada pada tabel hasil klausa FROM, sehingga tinggal kita tuliskan pada klausa SELECT 2. Jumlah Mahasiswa. Kolom ini belum ada, sehingga kita perlu membuatnya. Nilai kolom ini kita dapatkan dengan menghitung banyaknya NIM 3. Lulus. Kolom ini juga belum ada, sehingga kita perlu membuatnya. Nilai kolom ini kita ambil dari banyaknya nim dengan status lulus 4. Tidak Lulus. Kolom ini juga belum ada, sehingga kita perlu membuatnya. Nilai kolom ini kita ambil dari banyaknya nim dengan status tidak lulus Dari hasil analisa diatas, susunan kolom pada klausa SELECT adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7.
SELECT nama_jurusan AS "Nama Jurusan" , nim AS "Jumlah Mahasiswa" , IF(status="lulus", nim, NULL) AS "Lulus" , IF(status="tidak lulus", nim, NULL) AS "Tidak Lulus" FROM mhs_jurusan LEFT JOIN mhs USING(kd_jurusan) LEFT JOIN mhs_status USING(nim)
Hasil: +--------------+------------------+-------+-------------+ | Nama Jurusan | Jumlah Mahasiswa | Lulus | Tidak Lulus | +--------------+------------------+-------+-------------+
Killer Trik Query MySQL
149
| AKUNTANSI | 001 | 001 | NULL | | AKUNTANSI | 002 | NULL | 002 | | MANAJEMEN | 003 | NULL | 003 | | MANAJEMEN | 004 | 004 | NULL | | MANAJEMEN | 005 | NULL | 005 | | AKUNTANSI | 006 | 006 | NULL | | AKUNTANSI | 007 | 007 | NULL | | TEKNIK | NULL | NULL | NULL | +--------------+------------------+-------+-------------+
Selanjutnya kita hitung banyaknya nim dengan menggunakan fungsi COUNT. Jangan lupa ya, karena kita akan menghitung berdasarkan nama jurusan, maka kita tambahkan klausa GROUP BY jurusan. Querynya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8.
SELECT nama_jurusan AS "Nama Jurusan" , COUNT(nim) AS "Jumlah Mahasiswa" , COUNT(IF(status="lulus", nim, NULL)) AS "Lulus" , COUNT(IF(status="tidak lulus", nim, NULL)) AS "Tidak Lulus" FROM mhs_jurusan LEFT JOIN mhs USING(kd_jurusan) LEFT JOIN mhs_status USING(nim) GROUP BY kd_jurusan
Hasil: +--------------+------------------+-------+-------------+ | Nama Jurusan | Jumlah Mahasiswa | Lulus | Tidak Lulus | +--------------+------------------+-------+-------------+ | MANAJEMEN | 3 | 1 | 2 | | AKUNTANSI | 4 | 3 | 1 | | TEKNIK | 0 | 0 | 0 | +--------------+------------------+-------+-------------+
Pada contoh diatas, kolom alias mengandung spasi, sehingga kita harus menggunakan tanda kutip. Pada operator pembanding, dalam contoh diatas adalah =, maka data yang dibandingkan secara default bersifat case insensitive jadi lulus dan LULUS sama saja. Hal ini tergantung
150
BAB 8 Mempertajam Agregasi dan JOIN
dari collation tabel atau kolom (agar pembahasan tidak terlalu melebar, kita tidak membahas collation dibuku ini)
Pengayaan…. Selanjutnya, untuk lebih mengasah kemampuan Anda, silakan kembangkan tabel diatas dengan menambahkan kolom persentase sebagai berikut: +--------------+------------------+-------+---------+-------------+---------------+ | Nama Jurusan | Jumlah Mahasiswa | Lulus | % Lulus | Tidak Lulus | % Tidak Lulus | +--------------+------------------+-------+---------+-------------+---------------+ | MANAJEMEN | 3 | 1 | 33.33% | 2 | 66.67% | | AKUNTANSI | 4 | 3 | 75.00% | 1 | 25.00% | | TEKNIK | 0 | 0 | NULL | 0 | NULL | +--------------+------------------+-------+---------+-------------+---------------+
Ayo dicoba… Sudah bisa.. ? Ok,
mari
kita
bahas.
Nilai
persentase
diperoleh
dengan
membandingkan antara jumlah mahasiswa yang lulus / tidak lulus dengan jumlah keseluruhan mahasiswa, sehingga kita gunakan operator aritmetika biasa. Mari kita ubah query kita menjadi: 1. SELECT nama_jurusan AS "Nama Jurusan" 2. , COUNT(nim) AS "Jumlah Mahasiswa" 3. , COUNT(IF(status="lulus", nim, NULL)) AS "Lulus" 4. , CONCAT ( ROUND( COUNT(IF(status="lulus", nim, NULL)) / COUNT(nim) * 100, 2), '%') AS "% Lulus" 5. , COUNT(IF(status="tidak lulus", nim, NULL)) AS "Tidak Lulus" 6. , CONCAT ( ROUND( COUNT(IF(status="tidak lulus", nim, NULL)) / COUNT(nim) * 100, 2), '%') AS "% Tidak Lulus" 7. FROM mhs_jurusan 8. LEFT JOIN mhs USING(kd_jurusan) 9. LEFT JOIN mhs_status USING(nim) 10. GROUP BY kd_jurusan
Pada query diatas, terdapat tambahan query pada baris 4 dan 6. Pada query tersebut kita menggunakan fungsi ROUND untuk Killer Trik Query MySQL
151
membulatkan desimal persen hingga 2 angka di belakang koma, selain itu kita gunakan fungsi CONCAT untuk menggabungkan persentase dengan tanda persen ( % ) Ingat kembali: gunakan CONCAT untuk menggabungkan nilai kolom maupun string dan ROUND untuk membulatkan nilai desimal.
8.5. Studi Kasus #5 Pada kasus kali ini kita akan membuat resume jumlah guru yang mengikuti pelatihan per kabupaten. Adapun tabel yang tersedia dan hubungan antar tabel adalah sebagai berikut:
152
BAB 8 Mempertajam Agregasi dan JOIN
Gambar 8.1 Hubungan Antar Tabel
Sedangkan tabel output yang akan ditampilkan adalah sebagai berikut:
Killer Trik Query MySQL
153
+----------------+----+-----+-----+-------+ | Nama Kabupaten | SD | SMP | SMA | TOTAL | +----------------+----+-----+-----+-------+ | Kabupaten 1 | 1 | 2 | 0 | 3 | | Kabupaten 2 | 0 | 1 | 2 | 3 | | Kabupaten 3 | 1 | 1 | 0 | 2 | | Kabupaten 4 | 0 | 0 | 0 | 0 | +----------------+----+-----+-----+-------+
Silakan Anda coba ya.. sudah banyak contoh seperti ini pada latihan sebelumnya… practice make perfect…. Benar? Sudah bisa? Oke mari kita bahas….
Pertama… Seperti biasa, pertama kita analisa tabel yang diperlukan dengan melihat output tabel. Pada output tabel, kita akan menghitung data guru yang ada pada tabel sekolah_guru dengan mengelompokkannya per kabupaten (tabel sekolah_kab), berdasarkan hubungan gambar diatas, maka mau tidak mau kita harus melibatkan keempat tabel, karena agar tabel sekolah_kab dapat terhubung dengan sekolah_guru, kita harus melibatkan tabel sekolah dan sekolah_kec Sekarang kita gabungkan semua tabel menggunakan join dan kita tes hasil penggabungan tersebut dengan klausa SELECT. Agar hasil uji coba tidak terlalu banyak kolomnya, kali ini kita tidak menggunakan asterik *, melainkan kita pilih kolom yang akan digunakan saja yaitu kolom nama_guru, jenjang, pelatihan, dan nama kabupaten. Jalankan query berikut:
154
BAB 8 Mempertajam Agregasi dan JOIN
1. 2. 3. 4. 5.
SELECT nama_guru, jenjang, pelatihan, nama_kab FROM sekolah_guru LEFT JOIN sekolah USING(kd_sekolah) LEFT JOIN sekolah_kec USING(kd_kec) LEFT JOIN sekolah_kab USING(kd_kab)
Hasil yang kita peroleh: +-----------+---------+-----------+-------------+ | nama_guru | jenjang | pelatihan | nama_kab | +-----------+---------+-----------+-------------+ | Budi | SD | | Kabupaten 1 | | Jeni | SMP | IKUT | Kabupaten 1 | | Hari | SD | | Kabupaten 1 | | Indri | SMP | IKUT | Kabupaten 1 | | Erdi | SD | IKUT | Kabupaten 1 | | Ferry | SMA | IKUT | Kabupaten 2 | | Gari | SMA | IKUT | Kabupaten 2 | | Denny | SMP | IKUT | Kabupaten 2 | | Aldi | SD | IKUT | Kabupaten 3 | | Cyndi | SMP | IKUT | Kabupaten 3 | +-----------+---------+-----------+-------------+
Sudah benar? Wait… coba perhatikan kolom nama_kab, ternyata ada yang kurang, tahu kurangnya dimana? Yup, Kabupaten 4 tidak ada. Kenapa begitu? Seperti sebelum sebelumnya, karena kita kita akan menampilkan semua data nama kabupaten beserta jumlah guru yang mengikuti pelatihan, maka tabel yang paling kiri adalah tabel sekolah_kab. Jangan terkecoh dengan urutan tabel pada gambar atau urutan tabel berdasarkan abjad, karena meskipun tampak rapi, belum tentu hasilnya benar. Mari kita ubah querynya:
Killer Trik Query MySQL
155
1. 2. 3. 4. 5.
SELECT nama_kab, nama_guru, jenjang, pelatihan FROM sekolah_kab LEFT JOIN sekolah_kec USING(kd_kab) LEFT JOIN sekolah USING(kd_kec) LEFT JOIN sekolah_guru USING(kd_sekolah)
Hasil: +-------------+-----------+---------+-----------+ | nama_kab | nama_guru | jenjang | pelatihan | +-------------+-----------+---------+-----------+ | Kabupaten 3 | Aldi | SD | IKUT | | Kabupaten 1 | Budi | SD | | | Kabupaten 3 | Cyndi | SMP | IKUT | | Kabupaten 2 | Denny | SMP | IKUT | | Kabupaten 1 | Erdi | SD | IKUT | | Kabupaten 2 | Ferry | SMA | IKUT | | Kabupaten 2 | Gari | SMA | IKUT | | Kabupaten 1 | Hari | SD | | | Kabupaten 1 | Indri | SMP | IKUT | | Kabupaten 1 | Jeni | SMP | IKUT | | Kabupaten 4 | NULL | NULL | NULL | +-------------+-----------+---------+-----------+
Apakah hasilnya sudah benar? Yes, sudah! Selanjutnya kita melangkah ke bagian kedua
Kedua… Kedua mari kita susun kolom pada klausa SELECT. Bagian Kolom Nama kabupaten sudah tersedia, kita tinggal menuliskannya pada klausa SELECT, untuk kolom SD, SMP, SMA, dan TOTAL, kita perlu membuatnya. Jalankan query berikut: 1. SELECT nama_kab AS "Nama Kabupaten" 2. , IF(jenjang="SD" AND pelatihan = "IKUT", 1, NULL) AS SD , IF(jenjang="SMP" AND pelatihan = "IKUT", 1, NULL) AS SMP 3. , IF(jenjang="SMA" AND pelatihan = "IKUT", 1, NULL) AS SMA , IF(pelatihan = "IKUT",1,NULL) AS TOTAL 4. FROM sekolah_kab LEFT JOIN sekolah_kec USING(kd_kab)
156
BAB 8 Mempertajam Agregasi dan JOIN
5. LEFT JOIN sekolah USING(kd_kec) 6. LEFT JOIN sekolah_guru USING(kd_sekolah)
Hasil: +----------------+------+------+------+-------+ | Nama Kabupaten | SD | SMP | SMA | TOTAL | +----------------+------+------+------+-------+ | Kabupaten 3 | 1 | NULL | NULL | 1 | | Kabupaten 1 | NULL | NULL | NULL | NULL | | Kabupaten 3 | NULL | 1 | NULL | 1 | | Kabupaten 2 | NULL | 1 | NULL | 1 | | Kabupaten 1 | 1 | NULL | NULL | 1 | | Kabupaten 2 | NULL | NULL | 1 | 1 | | Kabupaten 2 | NULL | NULL | 1 | 1 | | Kabupaten 1 | NULL | NULL | NULL | NULL | | Kabupaten 1 | NULL | 1 | NULL | 1 | | Kabupaten 1 | NULL | 1 | NULL | 1 | | Kabupaten 4 | NULL | NULL | NULL | NULL | +----------------+------+------+------+-------+
Selanjutnya kita hitung jumlah baris untuk kolom SD, SMP, dan SMA menggunakan fungsi COUNT. Jangan lupa karena kita akan mengelompokkan data berdasarkan nama kabupaten, maka kita tambahkan klausa GROUP BY kd_kab Query jadinya adalah sebagai berikut: 1. SELECT nama_kab AS "Nama Kabupaten" 2. , COUNT(IF(jenjang="SD" AND pelatihan = "IKUT", 1, NULL)) AS SD 3. , COUNT(IF(jenjang="SMP" AND pelatihan = "IKUT", 1, NULL)) AS SMP 4. , COUNT(IF(jenjang="SMA" AND pelatihan = "IKUT", 1, NULL)) AS SMA 5. , COUNT(IF(pelatihan = "IKUT",1,NULL)) AS TOTAL 6. FROM sekolah_kab 7. LEFT JOIN sekolah_kec USING(kd_kab) 8. LEFT JOIN sekolah USING(kd_kec) 9. LEFT JOIN sekolah_guru USING(kd_sekolah) 10. GROUP BY kd_kab
Hasil: Killer Trik Query MySQL
157
+----------------+----+-----+-----+-------+ | Nama Kabupaten | SD | SMP | SMA | TOTAL | +----------------+----+-----+-----+-------+ | Kabupaten 1 | 1 | 2 | 0 | 3 | | Kabupaten 2 | 0 | 1 | 2 | 3 | | Kabupaten 3 | 1 | 1 | 0 | 2 | | Kabupaten 4 | 0 | 0 | 0 | 0 | +----------------+----+-----+-----+-------+
Sudah benar? Yup! Sudah benar….
Cara lain… Query adalah seni sehingga banyak jalan untuk memecahkan kasus, demikian juga dengan kasus diatas, kasus diatas juga dapat di selesaikan menggunakan query berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
SELECT nama_kab AS "Nama Kabupaten" , COUNT(IF(jenjang="SD", 1, NULL)) AS SD , COUNT(IF(jenjang="SMP", 1, NULL)) AS SMP , COUNT(IF(jenjang="SMA", 1, NULL)) AS SMA , COUNT(pelatihan) AS TOTAL FROM sekolah_kab LEFT JOIN sekolah_kec USING(kd_kab) LEFT JOIN sekolah USING(kd_kec) LEFT JOIN sekolah_guru USING(kd_sekolah) WHERE pelatihan = "IKUT" OR nama_guru IS NULL GROUP BY kd_kab
Karena pada fungsi IF terdapat kondisi yang sama, yaitu pelatihan = "IKUT" maka kondisi ini dapat kita tempatkan pada klusa WERE
158
BAB 8 Mempertajam Agregasi dan JOIN
BAB 9 Menguasai SUBQUERY Subquery artinya query didalam query atau klausa SELECT didalam statemen SELECT Subquery ini merupakan fitur yang sangat powerfull untuk memecahkan berbagai permasalahan query, untuk itu Anda perlu memahami konsep ini dengan baik, dan ingat: practice.. practice.. practice…
9.1. Subquery Sebagai Kolom Model pertama dari subquery adalah subquery sebagai kolom. Dinamakan demikian karena subquery ini letaknya pada klausa SELECT. Ingat pembahasan kita pada awal bab SELECT, bahwa semua yang ada pada klausa select akan ditampilkan sebagai kolom.
9.1.1 Studi Kasus #1 Misal kita memiliki tabel anggota_dewan dengan data sebagai berikut: +------------+--------------+-------------+-----------+ | id_anggota | nama_anggota | nama_partai | periode | +------------+--------------+-------------+-----------+ | 1 | Alfa | Rakyat | 2010-2015 | | 2 | Beta | Rakyat | 2015-2020 | | 3 | Charlie | Rakyat | 2010-2015 | | 4 | Denny | PRDD | 2015-2020 | | 5 | Erry | PRDD | 2010-2015 | | 6 | Ferry | PRDD | 2015-2020 | +------------+--------------+-------------+-----------+
Selanjutnya
kita
akan
menampilkan
data
jumlah
anggota
berdasarkan nama partai dan periode sebagai berikut:
Killer Trik Query MySQL
159
+-------------+--------+--------+-----------+ | nama_partai | jumlah | persen | periode | +-------------+--------+--------+-----------+ | Rakyat | 2 | 66.67 | 2010-2015 | | PRDD | 1 | 33.33 | 2010-2015 | | Rakyat | 1 | 33.33 | 2015-2020 | | PRDD | 2 | 66.67 | 2015-2020 | +-------------+--------+--------+-----------+
Bagaimana query yang kita jalankan? Pertama kita buat tanpa persentase, query yang kita jalankan: 1. 2. 3. 4. 5. 6.
SELECT nama_partai, COUNT(nama_anggota) AS jumlah_anggota, periode FROM anggota_dewan GROUP BY nama_partai, periode ORDER BY periode
Hasil: +-------------+----------------+-----------+ | nama_partai | jumlah_anggota | periode | +-------------+----------------+-----------+ | Rakyat | 3 | 2010-2015 | | PRDD | 1 | 2010-2015 | | Rakyat | 1 | 2015-2020 | | PRDD | 2 | 2015-2020 | +-------------+----------------+-----------+
Selanjutnya, persentase kita hitung dengan membagi jumlah anggota dengan seluruh jumlah anggota pada periode yang sama, nah untuk mendapatkan jumlah seluruh anggota per periode, kita harus menggunakan data periode pada tabel utama, query yang kita jalankan: 1. 2. 3. 4. 5. 6.
160
SELECT nama_partai, COUNT(nama_partai) AS jumlah, ROUND( COUNT(nama_partai) / (SELECT COUNT(*) FROM anggota_dewan WHERE periode = t1.periode) * 100, 2
9.1. Subquery Sebagai Kolom
7. ) AS persen, 8. periode 9. FROM anggota_dewan t1 10. GROUP BY nama_partai, periode 11. ORDER BY periode
Hasil: +-------------+--------+--------+-----------+ | nama_partai | jumlah | persen | periode | +-------------+--------+--------+-----------+ | Rakyat | 3 | 75.00 | 2010-2015 | | PRDD | 1 | 25.00 | 2010-2015 | | Rakyat | 1 | 33.33 | 2015-2020 | | PRDD | 2 | 66.67 | 2015-2020 | +-------------+--------+--------+-----------+
Pada query diatas, klausa SELECT akan dieksekusi pada setiap baris tabel anggota_dewan sehingga klausa WHERE periode = t1.periode pada subquery akan berubah ubah sesuai dengan baris yang ada, misal pada baris pertama, klausa where menjadi WHERE periode = 2010-2015, sedangkan pada baris ke 3 WHERE periode = 2015-2020
Perhatikan ilustrasi berikut:
Gambar 9.1 Ilustrasi Perubahan Klausa WHERE Pada Correlatd Subquery
Subquery ini sering digunakan ketika membuat persentase dimana penyebutnya merupakan total dari suatu data.
Killer Trik Query MySQL
161
Correlated Subquery Model subquery ditas berbentuk Correlated subquery (subquery berkorelasi) artinya subquery yang bergantung pada nilai kolom yang ada pada query utama. Bagaimana mengidentifikasi masalah sehingga kita menggunakan correlated subquery? Correlated Subquery terjadi jika subquery yang kita buat mengandung klausa WHERE, dan kondisi pada klausa WHERE ini melibatkan kolom pada query utama. Bagaimana kita tahu kondisi pada klausa WHERE ini melibatkan query utama? Untuk menemukannya, mau tidak mau kita harus bisa mengidentifikasi permasalahan yang ingin kita pecahkan.
9.2 Subquery Sebagai Data Subquery sebagai data artinya bahwa subquery diletakkan pada klausa FROM, sehingga menghasilkan tabel, nah tabel ini yang dimaksud dengan data. Untuk memahami model subquery ini, mari kita belajar dari contoh kasus.
9.2.1. Studi Kasus #2 Pada studi kasus ini, kita ambil contoh yang paling mudah. Misal kita memiliki tabel barang, barang_masuk, dan barang_keluar sebagai berikut:
162
9.1. Subquery Sebagai Kolom
barang +-----------+-------------+------+-------+ | kd_barang | nama_barang | stok | harga | +-----------+-------------+------+-------+ | 1 | Mouse | 14 | 76000 | | 2 | Flashdisk | 15 | 55000 | | 3 | Mousepad | 17 | 35000 | | 4 | Keyboard | 12 | 80000 | | 5 | Kabel VGA | 7 | 45000 | +-----------+-------------+------+-------+ barang_masuk +------------+-----------+-------------+-----------+ | tgl_masuk | kd_barang | kd_supplier | jml_masuk | +------------+-----------+-------------+-----------+ | 2017-05-02 | 1 | 1 | 14 | | 2017-05-03 | 1 | 2 | 5 | | 2017-05-04 | 2 | 2 | 13 | | 2017-05-04 | 3 | 1 | 4 | | 2017-05-05 | 4 | 3 | 10 | | 2017-04-26 | 1 | 1 | 7 | +------------+-----------+-------------+-----------+ barang_keluar +------------+-----------+------------+ | tgl_keluar | kd_barang | jml_keluar | +------------+-----------+------------+ | 2017-05-11 | 1 | 13 | | 2017-05-12 | 1 | 4 | | 2017-05-13 | 2 | 5 | | 2017-05-14 | 3 | 6 | | 2017-05-15 | 4 | 7 | | 2017-04-27 | 1 | 5 | +------------+-----------+------------+
Selanjutnya kita ingin menampilkan data kd_barang, nama_barang, dan jumlah barang masuk dan keluar untuk bulan Mei 2017 dengan output sebagai berikut: +-----------+-------------+-----------+------------+ | kd_barang | nama_barang | jml_masuk | jml_keluar | +-----------+-------------+-----------+------------+ | 1 | Mouse | 19 | 17 | | 2 | Flashdisk | 13 | 5 | | 3 | Mousepad | 4 | 6 | | 4 | Keyboard | 10 | 7 | | 5 | Kabel VGA | NULL | NULL | +-----------+-------------+-----------+------------+
Killer Trik Query MySQL
163
Bagaimana querynya? Untuk menyusun querynya, kita gunakan prinsip yang telah kita pelajari yaitu:
Pertama… Kita identifikasi data yang ingin ditampilkan, yaitu kd_barang, nama_barang, dan jml_masuk, dan jml_keluar. Ketiganya ada di tabel barang, barang_masuk, dan barang_keluar, untuk itu kita perlu menggabungkan ketiga tabel. Bagaimana bentuk penggabungannya? Kali ini kita gabungkan ketiga tabel tersebut menggunakan LEFT JOIN dan kita letakkan tabel barang di paling kiri. Mari kita tes bentuk tabel hasil penggabungan dengan menggunakan klausa SELECT *: 1. 2. 3. 4.
SELECT * FROM barang LEFT JOIN barang_masuk USING(kd_barang) LEFT JOIN barang_keluar USING(kd_barang)
Hasil yang kita peroleh: +-----------+-------------+------+-------+------------+-------------+-----------+ | kd_barang | nama_barang | stok | harga | tgl_masuk | kd_supplier | jml_masuk | +-----------+-------------+------+-------+------------+-------------+-----------+ | 1 | Mouse | 14 | 76000 | 2017-05-02 | 1 | 14 | | 1 | Mouse | 14 | 76000 | 2017-05-03 | 2 | 5 | | 1 | Mouse | 14 | 76000 | 2017-04-26 | 1 | 7 | | 1 | Mouse | 14 | 76000 | 2017-05-02 | 1 | 14 | | 1 | Mouse | 14 | 76000 | 2017-05-03 | 2 | 5 | | 1 | Mouse | 14 | 76000 | 2017-04-26 | 1 | 7 | | 2 | Flashdisk | 15 | 55000 | 2017-05-04 | 2 | 13 | | 3 | Mousepad | 17 | 35000 | 2017-05-04 | 1 | 4 | | 4 | Keyboard | 12 | 80000 | 2017-05-05 | 3 | 10 | | 1 | Mouse | 14 | 76000 | 2017-05-02 | 1 | 14 | | 1 | Mouse | 14 | 76000 | 2017-05-03 | 2 | 5 | | 1 | Mouse | 14 | 76000 | 2017-04-26 | 1 | 7 | | 5 | Kabel VGA | 7 | 45000 | NULL | NULL | NULL | +-----------+-------------+------+-------+------------+-------------+-----------+ Lanjutan... +------------+------------+
164
9.1. Subquery Sebagai Kolom
| tgl_keluar | jml_keluar | +------------+------------+ | 2017-05-11 | 13 | | 2017-05-11 | 13 | | 2017-05-11 | 13 | | 2017-05-12 | 4 | | 2017-05-12 | 4 | | 2017-05-12 | 4 | | 2017-05-13 | 5 | | 2017-05-14 | 6 | | 2017-05-15 | 7 | | 2017-04-27 | 5 | | 2017-04-27 | 5 | | 2017-04-27 | 5 | | NULL | NULL | +------------+------------+
Ternyata tabel yang dihasilkan terdiri dari banyak sekali row, hal ini seperti yang telah kita bahas pada bagian JOIN, bahwa ketika kita menggabungkan tabel, maka semua kombinasi dari data yang berhubungan akan ditampilkan semua.
Kedua… Setelah memiliki gambaran tabel hasil penggabungan, maka selanjutnya kita susun kolom pada klausa SELECT, selain itu kita tambahkan klausa WHERE untuk memfilter data sehingga hanya diambil data bulan Mei 2017. Oiya jangan lupa menambahkan klausa GROUP BY karena kita akan menjumlahkan data per barang. Bentuk query jadinya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
SELECT kd_barang , nama_barang , SUM(jml_masuk) AS jml_masuk , SUM(jml_keluar) AS jml_keluar FROM barang LEFT JOIN barang_masuk USING (kd_barang) LEFT JOIN barang_keluar USING (kd_barang) WHERE (YEAR(tgl_masuk) = 2017 AND YEAR(tgl_masuk) = 2017 AND MONTH(tgl_masuk) = 5 AND MONTH(tgl_masuk) = 5) OR tgl_masuk IS NULL OR tgl_keluar IS NULL GROUP BY kd_barang
Killer Trik Query MySQL
165
Hasil yang kita peroleh: +-----------+-------------+-----------+------------+ | kd_barang | nama_barang | jml_masuk | jml_keluar | +-----------+-------------+-----------+------------+ | 1 | Mouse | 57 | 44 | | 2 | Flashdisk | 13 | 5 | | 3 | Mousepad | 4 | 6 | | 4 | Keyboard | 10 | 7 | | 5 | Kabel VGA | NULL | NULL | +-----------+-------------+-----------+------------+
Ternyata hasil yang kita peroleh berbeda. Kenapa seperti itu? Coba perhatikan tabel hasil penggabungan, data baik pada kolom jml_masuk maupun kolom jml_keluar, datanya dobel sehingga ketika dijumlahkan menggunakan SUM, jumlahnya menjadi lebih besar. Bagaimana mengatasinya? Nah, inilah saatnya kita menggunakan subquery… Tapi… bagaimana mendesain subquerynya? Untuk menjawab pertanyaan tersebut, selalu gunakan prinsip hubungan one to one pada JOIN, dimana setiap data pada tabel hanya berhubungan dengan satu data pada tabel lain, pada kasus diatas, buat setiap tabel hanya memiliki data kd_barang yang unik, tidak dobel Perhatikan gambar dibawah ini:
Gambar 9.2. Ilustrasi Hubungan One to One Pada Tabel barang, barang_masuk, dan barang_keluar
166
9.1. Subquery Sebagai Kolom
Bagaimana bisa mendapatkan ide bentuk tabel barang_masuk dan barang_keluar seperti diatas? bentuk diatas didapatkan dari hasil identifikasi dari permasalahan yang ada, yang pada kebanyakan kasus sering melibatkan fungsi agregasi (SUM atau COUNT). TIPS: Subquery sering digunakan ketika hubungan antara tabel yang digabungkan berbentuk many to many, yang artinya data pada tabel sebelah kiri berhubungan lebih dari satu data pada tabel disebelah kanan dan sebaliknya, yang pada contoh diatas tabel barang_masuk dan barang_keluar Selanjutnya kita kembali lagi ke tahapan awal
Pertama… Pertama kita susun ulang hubungan antar tabel, kita terjemahkan gambar diatas menjadi bentuk query, hasilnya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
SELECT * FROM barang LEFT JOIN ( SELECT kd_barang, SUM(jml_masuk) AS jml_masuk FROM barang_masuk WHERE MONTH(tgl_masuk) = 5 AND YEAR(tgl_masuk) = 2017 GROUP BY kd_barang ) AS barang_masuk USING(kd_barang) LEFT JOIN ( SELECT kd_barang, SUM(jml_keluar) AS jml_keluar FROM barang_keluar WHERE MONTH(tgl_keluar) = 5 AND YEAR(tgl_keluar) = 2017 GROUP BY kd_barang ) AS brg_keluar USING(kd_barang)
Perhatikan bahwa pada query diatas, kita menggunakan dua subquery yaitu pada baris 4 – 7 yang menghasilkan tabel barang_masuk dan 11 s.d 14 untuk barang_keluar. Pada masing
Killer Trik Query MySQL
167
masing subquery kita gunakan klausa WHERE untuk mengambil data bulan Mei 2017 Perhatikan ilustrasi berikut:
Gambar 9.3. Ilustrasi Eksekusi Query Pada Subquery
Hasil yang kita peroleh adalah: +-----------+-------------+------+-------+-----------+------------+ | kd_barang | nama_barang | stok | harga | jml_masuk | jml_keluar | +-----------+-------------+------+-------+-----------+------------+ | 1 | Mouse | 14 | 76000 | 19 | 17 | | 2 | Flashdisk | 15 | 55000 | 13 | 5 | | 3 | Mousepad | 17 | 35000 | 4 | 6 | | 4 | Keyboard | 12 | 80000 | 10 | 7 | | 5 | Kabel VGA | 7 | 45000 | NULL | NULL | +-----------+-------------+------+-------+-----------+------------+
Hasil diatas sudah mendekati apa yang kita harapkan, tinggal kita pilih kolom yang ingin ditampilkan melalui klausa SELECT
168
9.1. Subquery Sebagai Kolom
Kedua…. Seperti tradisi kita, setelah melalui tahap pertama, kita lanjut tahap kedua yaitu menentukan kolom pada klausa SELECT. Karena data pada tabel hasil penggabungan sudah sesuai dengan yang kita harapkan, maka kita tingggal memilih kolom yang ingin kita tampilkan, tanpa perlu menggunakan fungsi agregasi. Query lengkapnya adalalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
SELECT kd_barang, nama_barang, jml_masuk, jml_keluar FROM barang LEFT JOIN ( SELECT kd_barang, SUM(jml_masuk) AS jml_masuk FROM barang_masuk WHERE MONTH(tgl_masuk) = 5 AND YEAR(tgl_masuk) = 2017 GROUP BY kd_barang ) AS brg_masuk USING(kd_barang) LEFT JOIN ( SELECT kd_barang, SUM(jml_keluar) AS jml_keluar FROM barang_keluar WHERE MONTH(tgl_keluar) = 5 AND YEAR(tgl_keluar) = 2017 GROUP BY kd_barang ) AS brg_keluar USING(kd_barang)
Jika query diatas dieksekusi, hasil yang kita peroleh adalah: +-----------+-------------+-----------+------------+ | kd_barang | nama_barang | jml_masuk | jml_keluar | +-----------+-------------+-----------+------------+ | 1 | Mouse | 19 | 17 | | 2 | Flashdisk | 13 | 5 | | 3 | Mousepad | 4 | 6 | | 4 | Keyboard | 10 | 7 | | 5 | Kabel VGA | NULL | NULL | +-----------+-------------+-----------+------------+
Pada contoh diatas, terdapat klausa AS brg_masuk dan AS brg_keluar. Klausa ini digunakan untuk memberi nama alias
Killer Trik Query MySQL
169
untuk tabel hasil subquery dan wajib ada. Anda bebas memberi nama alias sesuai keinginan. Mungkin anda bertanya tanya, dimana letak tabel hasil subquery ini? Tabel hasil subquery disimpan di dalam memory (RAM) dan akan dihapus setelah eksekusi query selesai, oleh karena itu, tabel ini dinamakan temporary table (tabel sementara). Karena disimpan pada memory maka semakin besar tabel hasil subquery ini, maka semakin besar space memory yang digunakan.
170
9.1. Subquery Sebagai Kolom
BAB 10 Menguasai UNION Seperti yang telah kita bahas pada BAB Menguasai JOIN, pada MySQL kita dapat menggabungkan dua atau lebih tabel secara vertikal maupun horizontal Penggabungan secara horizontal telah kita bahas pada BAB Menguasai JOIN, nah pada BAB ini kita akan membahas bentuk yang kedua, yaitu penggabungan vertikal, atas dan bawah. Ilustrasi penggabungan secara vertikal tampak seperti gambar dibawah:
Gambar 10.1 Ilustrasi UNION
Killer Trik Query MySQL
171
10.1. Memahami UNION dan UNION ALL MySQL menyediakan dua buah operator untuk melakukan penggabungan tabel secara vertikal yaitu UNION dan UNION ALL, keduanya merupakan operator penting dalam poses pengolahan data, untuk itu kita perlu memahaminya dengan baik Format penulisan kedua operator tersebut adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
SELECT kolom1, kolom2, kolom3 FROM tabel1 WHERE kondisi GROUP BY kolom UNION atau UNION ALL SELECT kolom1, kolom2, kolom3 FROM tabel2 WHERE kondisi GROUP BY kolom [statemen SELECT yang lain…] ORDER BY nama_kolom
Penting diperhatikan bahwa ketika menggunakan operator ini, statemen SELECT hanya bisa sampai pada klausa GROUP BY sedangkan operator setelah GROUP BY, yaitu ORDER BY, HAVING, dan LIMIT hanya bisa digunakan di paling bawah sehingga akan
berpengaruh pada tabel hasil penggabungan, bukan pada masingmasing statemen SELECT. Kenapa sampai GROUP BY? Karena statemen SELECT minimal membutuhkan klausa GROUP BY khususnya jika statemen SELECT tersebut mengandung fungsi agregasi, lihat kembali pembahasan mengenai Kunci Memahami Statemen SELECT
172
BAB 10 Menguasai UNION
Penggunaan UNION dan UNION ALL harus memenuhi kriteria yaitu jumlah kolom pada masing masing klausa SELECT yang akan digabungkan harus sama persis. Selanjutnya, nama kolom tabel hasil penggabungan akan mengikuti nama kolom pada klausa SELECT yang pertama
Perbedaan UNION dan UNION ALL UNION dan UNION ALL hanya memiliki satu perbedaan mendasar yaitu
pada baris hasil penggabungan. UNION akan menggabungkan baris yang memiliki data yang sama di
semua kolomnya, sedangkan UNION ALL akan menggabungkan baris apa adanya tanpa menggabungkan baris yang memiliki data sama. Untuk lebih memahami perbedaan UNION dan UNION ALL, misal kita memiliki tabel kredit_agen dan kredit_survey, dan kredit_closing sebagai berikut: kredit_agen +----+--------------+------------+---------+ | id | nama_pegawai | tanggal | nasabah | +----+--------------+------------+---------+ | 1 | Alfa | 2017-06-10 | Toni | | 2 | Beta | 2017-06-11 | Sapta | | 3 | Charlie | 2017-06-12 | Umar | | 4 | Beta | 2017-06-13 | Versa | +----+--------------+------------+---------+ kredit_survey +----+--------------+------------+---------+ | id | nama_pegawai | tanggal | nasabah | +----+--------------+------------+---------+ | 1 | Beta | 2017-06-17 | Toni | | 2 | Charlie | 2017-06-18 | Sapta | | 3 | Ekky | 2017-06-19 | Umar | | 4 | Charlie | 2017-06-20 | Versa | +----+--------------+------------+---------+ kredit_closing +----+--------------+------------+---------+ | id | nama_pegawai | tanggal | nasabah |
Killer Trik Query MySQL
173
+----+--------------+------------+---------+ | 1 | Charlie | 2017-06-17 | Toni | | 2 | Ferry | 2017-06-18 | Sapta | | 3 | Alfa | 2017-06-19 | Umar | | 4 | Gery | 2017-06-20 | Versa | +----+--------------+------------+---------+
Selanjutnya kita akan menampilkan semua data pegawai yang pernah melakukan kegiatan baik calling, survey, maupun closing, untuk keperluan tersebut, kita gunakan klausa UNION, jalankan query berikut: 1. 2. 3. 4. 5. 6. 7. 8.
SELECT nama_pegawai FROM kredit_agen UNION SELECT nama_pegawai FROM kredit_survey UNION SELECT nama_pegawai FROM kredit_closing
Hasil yang kita peroleh: +--------------+ | nama_pegawai | +--------------+ | Alfa | | Beta | | Charlie | | Ekky | | Ferry | | Gery | +--------------+
Dari hasil diatas terlihat tidak ada nama yang muncul lebih dari sekali, jika kita gunakan UNION ALL, maka
nama Charlie akan
muncul empat kali Selanjutnya kita gabungkan semua data pada ketiga tabel dan kita beri tanda, mana yang calling, survey, dan closing, jalankan query berikut:
174
BAB 10 Menguasai UNION
1. 2. 3. 4. 5. 6. 7. 8.
SELECT nama_pegawai, "calling" AS jenis FROM kredit_calling UNION ALL SELECT nama_pegawai, "survey" AS jenis FROM kredit_survey UNION ALL SELECT nama_pegawai, "closing" AS jenis FROM kredit_closing
Hasil: +--------------+---------+ | nama_pegawai | jenis | +--------------+---------+ | Alfa | calling | | Beta | calling | | Charlie | calling | | Beta | calling | | Alfa | calling | | Beta | survey | | Charlie | survey | | Ekky | survey | | Charlie | survey | | Charlie | closing | | Ferry | closing | | Alfa | closing | | Ferry | closing | +--------------+---------+
Pada contoh diatas, semua data ditampilkan, jika kita tidak menggunakan ALL, maka pegawai dengan nama Alfa dan dengan Jenis calling akan digabung.
Latihan… Sebagai latihan, coba Anda tampilkan data pegawai beserta jumlah calling, survey, dan closing nya, tabel output yang kita inginkan seperti berikut ini: +--------------+---------+--------+---------+ | nama_pegawai | calling | survey | closing | +--------------+---------+--------+---------+ | Alfa | 2 | 0 | 1 |
Killer Trik Query MySQL
175
| Beta | 2 | 1 | 0 | | Charlie | 1 | 2 | 1 | | Ekky | 0 | 1 | 0 | | Ferry | 0 | 0 | 2 | +--------------+---------+--------+---------+
Silakan dicoba…., gunakan prinsip prinsip yang telah kita pelajari salah satunya slogan "menghitung? ya gunakan COUNT", masih ingat kan prinsip tersebut?, selain itu gunakan juga prinsip alur eksekusi query… Nah silakan di coba lagi…. Sudah bisa…? Baiklah, mari kita cocokkan jawabannya….
Pertama… Kita identifikasi bentuk tabel hasil klausa FROM, namun sebelumnya, karena melibatkan lebih dari satu tabel, kita analisa terlebih dahulu tabel outputnya. Dari hasil analisa tabel output, kita membutuhkan data ketiga tabel, sehingga kita perlu menggabungkan ketiga tabel tersebut menjadi satu. Bagaimana bentuk penggabungan nya? Menggunakan JOIN atau UNION? Sebelum menjawab pertanyaan tersebut, mari kita breakdown tabel outputnya. Seperti pada pembahasan sebelumnya tentang COUNT, mari kita tarik mundur bentuk tabel output, tabel yang kita peroleh adalah sebagai berikut: +--------------+---------+--------+---------+ | nama_pegawai | calling | survey | closing | +--------------+---------+--------+---------+
176
BAB 10 Menguasai UNION
| Alfa | 1 | NULL | NULL | | Beta | 1 | NULL | NULL | | Charlie | 1 | NULL | NULL | | Beta | 1 | NULL | NULL | | Alfa | 1 | NULL | NULL | | Beta | NULL | 1 | NULL | | Charlie | NULL | 1 | NULL | | Ekky | NULL | 1 | NULL | | Charlie | NULL | 1 | NULL | | Charlie | NULL | NULL | 1 | | Ferry | NULL | NULL | 1 | | Alfa | NULL | NULL | 1 | | Ferry | NULL | NULL | 1 | +--------------+---------+--------+---------+
Sebagai penjelasan, perhatikan ilustrasi berikut:
Gambar 10.2 Ilustrasi Brekdown Tabel Output
Killer Trik Query MySQL
177
Seperti yang telah kita bahas sebelum sebelumnya, selalu buat tabel antara seperti gambar diatas jika bentuk tabel output berupa penghitungan (COUNT) atau penjumlahan (SUM) dan kolom pada tabel output tidak ada pada tabel hasil klausa FROM Kembali ke bentuk tabel hasil klausa FROM, untuk mendapatkan bentuk tabel seperti diatas, maka kita gabungkan ketiga tabel menggunakan UNION. Mari kita tes hasil penggabungan nya menggunakan statemen SELECT: 1. 2. 3. 4. 5. 6. 7. 8.
SELECT * FROM kredit_calling UNION ALL SELECT * FROM kredit_survey UNION ALL SELECT * FROM kredit_closing
Hasil: +----+--------------+------------+---------+ | id | nama_pegawai | tanggal | nasabah | +----+--------------+------------+---------+ | 1 | Alfa | 2017-06-10 | Toni | | 2 | Beta | 2017-06-11 | Sapta | | 3 | Charlie | 2017-06-12 | Umar | | 4 | Beta | 2017-06-13 | Versa | | 5 | Alfa | 2017-06-14 | Zeti | | 1 | Beta | 2017-06-17 | Toni | | 2 | Charlie | 2017-06-18 | Sapta | | 3 | Ekky | 2017-06-19 | Umar | | 4 | Charlie | 2017-06-20 | Versa | | 1 | Charlie | 2017-06-17 | Toni | | 2 | Ferry | 2017-06-18 | Sapta | | 3 | Alfa | 2017-06-19 | Umar | | 4 | Ferry | 2017-06-20 | Versa | +----+--------------+------------+---------+
178
BAB 10 Menguasai UNION
Tabel tersebut sudah mirip dengan tabel antara seperti pada gambar, tinggal kita tambahkan kolom calling, survey, dan closing menggunakan fungsi IF Tapi tunggu… Bisakah kita membuatnya? Ternyata tidak bisa.. kenapa? Karena tidak ada data unik yang dapat membedakan data ketiga tabel, coba gunakan seperti ini IF(nama_pegawai="...", 1, NULL) AS calling Bisakah Anda mengisikan titik titk nya? atau mengganti ekspresi pertama dengan ekspresi lain sehingga menghasilkan data akurat? Ternyata tidak bisa, untuk itu, kita perlu membuat data baru sehingga dapat dibedakan mana tabel calling, tabel survey, dan tabel closing, salah satunya adalah sebagai berikut: +--------------+---------+ | nama_pegawai | jenis | +--------------+---------+ | Alfa | calling | | Beta | calling | | Charlie | calling | | Beta | calling | | Alfa | calling | | Beta | survey | | Charlie | survey | | Ekky | survey | | Charlie | survey | | Charlie | closing | | Ferry | closing | | Alfa | closing | | Ferry | closing | +--------------+---------+
Kolom jenis pada tabel diatas digunakan untuk pembeda tabel, Anda bebas menggantinya dengan bentuk lain. Query untuk membuat tabel diatas sudah dibahas diatas, yaitu:
Killer Trik Query MySQL
179
1. 2. 3. 4. 5. 6. 7. 8.
SELECT nama_pegawai, "calling" AS jenis FROM kredit_calling UNION ALL SELECT nama_pegawai, "survey" AS jenis FROM kredit_survey UNION ALL SELECT nama_pegawai, "closing" AS jenis FROM kredit_closing
Dengan query diatas, bentuk tabel hasil klausa FROM akan seperti berikut ini: +--------------+---------+ | nama_pegawai | jenis | +--------------+---------+ | Alfa | calling | | Beta | calling | | Charlie | calling | | Beta | calling | | Alfa | calling | | Beta | survey | | Charlie | survey | | Ekky | survey | | Charlie | survey | | Charlie | closing | | Ferry | closing | | Alfa | closing | | Ferry | closing | +--------------+---------+
Kedua… Seperti biasa, setelah mendapatkan gambaran tabel hasil klausa FROM, selanjutnya kita susun kolom pada klausa SELECT Karena kita perlu kolom baru, yaitu kolom calling, survey, dan closing, maka kita perlu mendefinisikan kolom tersebut pada klausa SELECT dengan query sebagai berikut: 1. SELECT nama_pegawai 2. , IF(jenis="calling", 1, NULL) AS calling 3. , IF(jenis="survey", 1, NULL) AS survey
180
BAB 10 Menguasai UNION
4. , IF(jenis="closing", 1, NULL) AS closing 5. FROM 6. ( 7. SELECT nama_pegawai, "calling" AS jenis 8. FROM kredit_calling 9. UNION ALL 10. SELECT nama_pegawai, "survey" AS jenis 11. FROM kredit_survey 12. UNION ALL 13. SELECT nama_pegawai, "closing" AS jenis 14. FROM kredit_closing 15. ) AS pegawai
Hasil: +--------------+---------+--------+---------+ | nama_pegawai | calling | survey | closing | +--------------+---------+--------+---------+ | Alfa | 1 | NULL | NULL | | Beta | 1 | NULL | NULL | | Charlie | 1 | NULL | NULL | | Beta | 1 | NULL | NULL | | Alfa | 1 | NULL | NULL | | Beta | NULL | 1 | NULL | | Charlie | NULL | 1 | NULL | | Ekky | NULL | 1 | NULL | | Charlie | NULL | 1 | NULL | | Charlie | NULL | NULL | 1 | | Ferry | NULL | NULL | 1 | | Alfa | NULL | NULL | 1 | | Ferry | NULL | NULL | 1 | +--------------+---------+--------+---------+
Killer Trik Query MySQL
181
Untuk penjelasannya, perhatikan gambar berikut:
Gambar 10.3 Ilustrasi Penggunaan Fungsi IF Pada Klausa SELECT
Bingung? Ingat kembali penjelasan tentang fungsi IF dan prinsip eksekusi klausa SELECT, yaitu bahwa setiap baris pada tabel hasil klausa FROM akan dieksekusi sesuai ekspresi pada klausa SELECT. Nah dari sini sudah kebayang kan? Bagaimana query jadinya? Yup, querynnya adalah sebagai berikut: 1. SELECT nama_pegawai 2. , COUNT(IF(jenis="calling", 1, NULL)) AS calling 3. , COUNT(IF(jenis="survey", 1, NULL)) AS survey 4. , COUNT(IF(jenis="closing", 1, NULL)) AS closing 5. FROM 6. ( 7. SELECT nama_pegawai, "calling" AS jenis 8. FROM kredit_calling 9. UNION ALL 10. SELECT nama_pegawai, "survey" AS jenis 11. FROM kredit_survey
182
BAB 10 Menguasai UNION
12. UNION ALL 13. SELECT nama_pegawai, "closing" AS jenis 14. FROM kredit_closing 15. ) AS pegawai 16. GROUP BY nama_pegawai
Hasil: +--------------+---------+--------+---------+ | nama_pegawai | calling | survey | closing | +--------------+---------+--------+---------+ | Alfa | 2 | 0 | 1 | | Beta | 2 | 1 | 0 | | Charlie | 1 | 2 | 1 | | Ekky | 0 | 1 | 0 | | Ferry | 0 | 0 | 2 | +--------------+---------+--------+---------+
Pada query diatas, kita tambahkan fungsi COUNT untuk menghitung jumlah baris pada kolom calling, survey, dan closing, selain itu kita tambahkan klausa GROUP BY nama_pegawai karena kita akan mengelompokkan data berdasarkan kolom nama pegawai. Selanjutnya coba Anda buat baris total untuk baris dan kolom sebagai berikut: +--------------+---------+--------+---------+-------+ | nama_pegawai | calling | survey | closing | total | +--------------+---------+--------+---------+-------+ | Alfa | 2 | 0 | 1 | 3 | | Beta | 2 | 1 | 0 | 3 | | Charlie | 1 | 2 | 1 | 4 | | Ekky | 0 | 1 | 0 | 1 | | Ferry | 0 | 0 | 2 | 2 | | TOTAL | 5 | 4 | 4 | 13 | +--------------+---------+--------+---------+-------+
Killer Trik Query MySQL
183
10.2. Studi Kasus #1 Dalam praktik, union sering digunakan untuk membuat baris total dan subtotal, pada bagian ini kita akan membahas beberapa kasus terkait dua hal tersebut
Total Contoh pertama kita akan membuat baris total yang ada di bagian paling bawah, misal kita memiliki tabel penjualan sebagai berikut: +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 0 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Selanjutnya, untuk menambahkan baris total, kita gunakan UNION ALL, query pertama digunakan untuk mengambil data tabel dan query kedua untuk membuat baris total. Agar menghasilkan baris total, gunakan fungsi agregasi pada query kedua, query jadinya adalah sebagai berikut: 1. 2. 3. 4. 5.
SELECT id_trx, tgl_trx, total_trx FROM penjualan UNION ALL SELECT 'TOTAL', "", SUM(total_trx) FROM penjualan
Hasil: +--------+------------+-----------+ | id_trx | tgl_trx | total_trx | +--------+------------+-----------+ | 1 | 2017-03-02 | 192000 |
184
BAB 10 Menguasai UNION
| 2 | 2017-03-10 | 186000 | | 3 | 2017-04-10 | 259000 | | 4 | 2017-04-05 | 110000 | | 5 | 2016-11-10 | 256000 | | TOTAL | | 1003000 | +--------+------------+-----------+
Pada contoh diatas terlihat sebuah baris baru yang berisi total nilai kolom total_trx Cara ini jauh lebih mudah dibanding menggunakan klausa WITH ROLLUP (dibahas pada bab selanjutnya) Selanjutnya, sebagai latihan coba Anda ubah query diatas sehingga menampilkan kolom id_pelanggan dan nama_pelanggan sebagai berikut: +--------+--------------+------+------------+-----------+ | id_trx | id_pelanggan | nama | tgl_trx | total_trx | +--------+--------------+------+------------+-----------+ | 1 | 1 | Alfa | 2017-03-02 | 192000 | | 2 | 1 | Alfa | 2017-03-10 | 186000 | | 4 | 2 | Beta | 2017-04-05 | 110000 | | 5 | 2 | Beta | 2016-11-10 | 256000 | | 3 | 0 | NULL | 2017-04-10 | 259000 | | TOTAL | | | | 1003000 | +--------+--------------+------+------------+-----------+
Bagaimana? Sudah bisa kan…? Jangan lupa, selalu gunakan prinsip urutan eksekusi query… Baiklah, jika sudah mari kita cocokkan jawabannya Berikut ini query versi saya: 1. 2. 3. 4. 5. 6.
SELECT id_trx, id_pelanggan, nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan) UNION ALL SELECT 'TOTAL', "", "", "", SUM(total_trx) FROM penjualan
Killer Trik Query MySQL
185
Tips membuat total dengan subquery merupakan cara yang powerful dapat menghasilkan hasil yang konsisten sehingga cara ini layak untuk digunakan dalam praktek nyata
Sub Total Selain baris total, dalam praktik kita juga sering membuat baris sub total, pada subtotal, data dikelompokkan berdasarkan kriteria tertentu. Misal melanjutkan contoh sebelumnya, kita buat baris SUB TOTAL yang berisi total penjualan per pelanggan, hasil yang kita inginkan adalah sebagai berikut: +-----------+--------------+-----------+------------+-----------+ | id_trx | id_pelanggan | nama | tgl_trx | total_trx | +-----------+--------------+-----------+------------+-----------+ | 3 | 0 | NULL | 2017-04-10 | 259000 | | SUB TOTAL | 0 | SUB TOTAL | | 259000 | | 1 | 1 | Alfa | 2017-03-02 | 192000 | | 2 | 1 | Alfa | 2017-03-10 | 186000 | | SUB TOTAL | 1 | SUB TOTAL | | 378000 | | 4 | 2 | Beta | 2017-04-05 | 110000 | | 5 | 2 | Beta | 2016-11-10 | 256000 | | SUB TOTAL | 2 | SUB TOTAL | | 366000 | +-----------+--------------+-----------+------------+-----------+
Bagaimana querynya? Sebagai clue (petunjuk), kali ini kita akan menggunakan dua buah tabel yaitu tabel penjualan dan pelanggan sebagai berikut: penjualan +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 0 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+ pelanggan
186
BAB 10 Menguasai UNION
+--------------+---------+-----------+---------+ | id_pelanggan | nama | alamat | id_staf | +--------------+---------+-----------+---------+ | 1 | Alfa | Jakarta | 1 | | 2 | Beta | Semarang | 1 | | 3 | Charlie | Surabaya | 2 | | 4 | Delta | Surakarta | 3 | +--------------+---------+-----------+---------+
Silakan dicoba…. Sudah bisa? Baiklah mari kita bahas…
Pertama… Seperti biasa kita susun klausa FROM, karena melibatkan dua tabel, kita harus menggunakan JOIN, pilihannya JOIN atau LEFT JOIN. Karena kita akan menampilkan semua data penjualan, maka kita gunakan LEFT JOIN dan kita letakkan tabel penjualan di sebelah kiri. Mari kita tes dengan menjalankan query berikut: 1. SELECT id_trx, id_pelanggan, nama, tgl_trx, total_trx 2. FROM penjualan 3. LEFT JOIN pelanggan USING(id_pelanggan)
Hasil yang kita peroleh: +--------+--------------+------+------------+-----------+ | id_trx | id_pelanggan | nama | tgl_trx | total_trx | +--------+--------------+------+------------+-----------+ | 1 | 1 | Alfa | 2017-03-02 | 192000 | | 2 | 1 | Alfa | 2017-03-10 | 186000 | | 4 | 2 | Beta | 2017-04-05 | 110000 | | 5 | 2 | Beta | 2016-11-10 | 256000 | | 3 | 0 | NULL | 2017-04-10 | 259000 | +--------+--------------+------+------------+-----------+
Apakah sudah mirip dengan hasil?
Killer Trik Query MySQL
187
Ya, sudah, tinggal kita tambahkan baris SUB TOTAL. Tapi… bagaimana menambahkannya? Pasti Anda akan menjawab: karena topik pembahasannya adalah UNION pasti caranya menggunakan UNION Yup betul sekali Terus bagaimana caranya? Caranya, kita gunakan UNION dan ORDER BY. Pertama kita buat total per id_pelanggan. Kenapa per id_pelanggan? Ya karena kita akan membuat sub total per pelanggan. Jalankan query berikut: 1. 2. 3. 4. 5. 6. 7.
SELECT id_trx, id_pelanggan, nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan) UNION ALL SELECT 'SUB TOTAL', id_pelanggan, "SUB TOTAL", "", SUM(total_trx) FROM penjualan GROUP BY id_pelanggan
Hasilnya adalah: +-----------+--------------+-----------+------------+-----------+ | id_trx | id_pelanggan | nama | tgl_trx | total_trx | +-----------+--------------+-----------+------------+-----------+ | 1 | 1 | Alfa | 2017-03-02 | 192000 | | 2 | 1 | Alfa | 2017-03-10 | 186000 | | 4 | 2 | Beta | 2017-04-05 | 110000 | | 5 | 2 | Beta | 2016-11-10 | 256000 | | 3 | 0 | NULL | 2017-04-10 | 259000 | | SUB TOTAL | 0 | SUB TOTAL | | 259000 | | SUB TOTAL | 1 | SUB TOTAL | | 378000 | | SUB TOTAL | 2 | SUB TOTAL | | 366000 | +-----------+--------------+-----------+------------+-----------+
188
BAB 10 Menguasai UNION
Selanjutnya dengan ORDER BY kita ubah urutan sehingga baris SUB TOTAL berada di bawah tiap tiap nama pelanggan, kita tambahkan klausa ORDER BY di baris terakhir sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9.
SELECT id_trx, id_pelanggan, nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan) UNION ALL SELECT 'SUB TOTAL' , id_pelanggan, "SUB TOTAL", "", SUM(total_trx) FROM penjualan GROUP BY id_pelanggan ORDER BY id_pelanggan, id_trx
Hasil yang kita peroleh: +-----------+--------------+-----------+------------+-----------+ | id_trx | id_pelanggan | nama | tgl_trx | total_trx | +-----------+--------------+-----------+------------+-----------+ | 3 | 0 | NULL | 2017-04-10 | 259000 | | SUB TOTAL | 0 | SUB TOTAL | | 259000 | | 1 | 1 | Alfa | 2017-03-02 | 192000 | | 2 | 1 | Alfa | 2017-03-10 | 186000 | | SUB TOTAL | 1 | SUB TOTAL | | 378000 | | 4 | 2 | Beta | 2017-04-05 | 110000 | | 5 | 2 | Beta | 2016-11-10 | 256000 | | SUB TOTAL | 2 | SUB TOTAL | | 366000 | +-----------+--------------+-----------+------------+-----------+
Pada
contoh
diatas,
pertama
data
diurutkan
berdasarkan
id_pelanggan sehingga baris sub total akan berada pada tiap-tiap pelanggan sesuai dengan id_pelanggannya Selanjutnya data diurutkan berdasarkan kolom id_trx, sehingga baris SUB TOTAL berada di bawah, kenapa dibawah? karena berupa abjad, bukan numeric sebagaimana id_trx yang lain Anda bisa membuat baris subtotal tersebut berada di atas dengan menambahkan DESC pada id_trx menjadi ORDER BY id_pelanggan, id_trx DESC
Killer Trik Query MySQL
189
NOTE: untuk lebih memudahkan mengurutkan baris total dan sub total, kita bisa memberi nilai kolom dengan NULL bukan TOTAL atau SUB TOTAL. Hal ini lebih memudahkan karena nilai NULL akan selalu di paling atas jika diurutkan secara ascending dan di paling bawah jika diurutkan secara descending. Penerapan teknik ini dapat diikuti pada BAB 11 menguasai WITH ROLLUP.
10.3. Studi Kasus #2 Studi kasus ini merupakan kelanjutan dari contoh Sub Total yang telah kita bahas pada Studi Kasus #1 Pada tabel output contoh kasus sebelumnya, terdapat kolom yang tidak diperlukan, seperti kolom id_trx dan id_pelanggan, untuk itu kita akan menghilangkannya. Selain itu kita juga akan menambahkan baris total pada bagian akhir tabel, sehingga hasil akhir tabel adalah sebagai berikut: +----------------+-------------------+-----------------+ | Nama Pelanggan | Tanggal Transaksi | Nilai Transaksi | +----------------+-------------------+-----------------+ | | 2017-04-10 | 259000 | | SUB TOTAL | | 259000 | | Alfa | 2017-03-02 | 192000 | | Alfa | 2017-03-10 | 186000 | | SUB TOTAL | | 378000 | | Beta | 2017-04-05 | 110000 | | Beta | 2016-11-10 | 256000 | | SUB TOTAL | | 366000 | | TOTAL | | 1003000 | +----------------+-------------------+-----------------+
Jauh lebih rapi dan informatif kan? Oke, tapi bagaimana caranya?
190
BAB 10 Menguasai UNION
Baiklah, karena teknik ini belum pernah kita bahas, mari kita bahas step-step nya. Masih ingat pembahasan subquery? Nah, karena kita akan menghilangkan beberapa kolom pada tabel hasil query sub total maka kita tempatkan query sub total menjadi subquery Remainding: Ingat kembali jika kita ingin mengolah lagi tabel hasil dari suatu query, maka gunakan subquery. Jalankan query berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
SELECT IFNULL(nama, "-") AS "Nama Pelanggan" , tgl_trx AS "Tanggal Transaksi" , total_trx AS "Nilai Transaksi" FROM ( SELECT id_trx, id_pelanggan, nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan) UNION ALL SELECT "SUB TOTAL", id_pelanggan , "SUB TOTAL", "", SUM(total_trx) FROM penjualan GROUP BY id_pelanggan ORDER BY id_pelanggan, id_trx ) AS penjualan
Hasilnya adalah: +----------------+-------------------+-----------------+ | Nama Pelanggan | Tanggal Transaksi | Nilai Transaksi | +----------------+-------------------+-----------------+ | | 2017-04-10 | 259000 | | SUB TOTAL | | 259000 | | Alfa | 2017-03-02 | 192000 | | Alfa | 2017-03-10 | 186000 | | SUB TOTAL | | 378000 | | Beta | 2017-04-05 | 110000 | | Beta | 2016-11-10 | 256000 | | SUB TOTAL | | 366000 | +----------------+-------------------+-----------------+
Killer Trik Query MySQL
191
Mungkin Anda akan bertanya tanya kenapa kita tidak langsung menghilangkan id_trx, id_pelanggan pada query utama, sehingga tidak perlu subquery? Hal ini tidak kita lakukan karena kedua kolom itu kita gunakan untuk mengurutkan data, lihat klausa ORDER BY Kembali ke tabel hasil query… hasilnya sudah mirip dengan yang kita inginkan bukan? Nah selanjutnya tinggal kita tambahkan baris total. Silakan Anda coba ya? Bisa? Baiklah, mari kita cocokkan. Query merurut versi saya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.
SELECT IFNULL(nama, "-") AS "Nama Pelanggan" , tgl_trx AS "Tanggal Transaksi" , total_trx AS "Nilai Transaksi" FROM ( SELECT id_trx, id_pelanggan, nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan) UNION ALL SELECT "SUB TOTAL", id_pelanggan , "SUB TOTAL", "", SUM(total_trx) FROM penjualan GROUP BY id_pelanggan ORDER BY id_pelanggan, id_trx ) AS penjualan UNION ALL SELECT "TOTAL", "", SUM(total_trx) FROM penjualan
Hasil: +----------------+-------------------+-----------------+ | Nama Pelanggan | Tanggal Transaksi | Nilai Transaksi | +----------------+-------------------+-----------------+ | | 2017-04-10 | 259000 |
192
BAB 10 Menguasai UNION
| SUB TOTAL | | 259000 | | Alfa | 2017-03-02 | 192000 | | Alfa | 2017-03-10 | 186000 | | SUB TOTAL | | 378000 | | Beta | 2017-04-05 | 110000 | | Beta | 2016-11-10 | 256000 | | SUB TOTAL | | 366000 | | TOTAL | | 1003000 | +----------------+-------------------+-----------------+
Yes, sudah berhasil. Sudah sama dengan yang Anda buat?
Equivalen / Alternatif Seribu jalan menuju Roma, banyak cara memecahkan masalah. Demikian juga dengan query total dan sub total diatas, kita dapat menggunakan cara lain untuk menghasilkan tabel yang sama persis. Pertanyaannya bagaimana caranya? Caranya.. menggunakan WITH ROLLUP, yang akan dibahas pada BAB selanjutnya Namun jika Anda penasaran ingin segera tahu querynya seperti apa, berikut ini query jadinya: 1. SELECT CASE 2. WHEN id_trx IS NULL AND id_pelanggan IS NOT NULL 3. THEN "SUB TOTAL" 4. WHEN id_trx IS NULL AND id_pelanggan IS NULL 5. THEN "TOTAL" 6. WHEN nama IS NULL 7. THEN "-" 8. ELSE nama 9. END AS Nama 10. , IF(ISNULL(id_trx), "", tgl_trx) AS "Tanggal Transaksi" 11. , total_trx AS "Total Transaksi" 12. FROM 13. ( 14. SELECT id_trx, id_pelanggan
Killer Trik Query MySQL
193
15. 16. 17. 18. 19. 20. ) AS
, nama, tgl_trx, SUM(total_trx) AS total_trx FROM penjualan LEFT JOIN pelanggan USING(id_pelanggan) GROUP BY id_pelanggan, id_trx WITH ROLLUP penjualan
Hasilnya: +----------------+-------------------+-----------------+ | Nama Pelanggan | Tanggal Transaksi | Nilai Transaksi | +----------------+-------------------+-----------------+ | | 2017-04-10 | 259000 | | SUB TOTAL | | 259000 | | Alfa | 2017-03-02 | 192000 | | Alfa | 2017-03-10 | 186000 | | SUB TOTAL | | 378000 | | Beta | 2017-04-05 | 110000 | | Beta | 2016-11-10 | 256000 | | SUB TOTAL | | 366000 | | TOTAL | | 1003000 | +----------------+-------------------+-----------------+
Menurut Anda, mana yang lebih mudah dan efisien? Saya pribadi tidak begitu confident menggunakan WITH ROLLUP, karena kita tidak memiliki kontrol penuh terhadap hasil query, kenapa? karena pada WITH ROLLUP kita sangat bergantung pada klausa GROUP BY
10.4. Studi Kasus #3 Pada pembahasan sebelumnya tentang SUB TOTAL, kita telah belajar bagaimana menggunakan klausa ORDER BY pada UNION, pada contoh tersebut, ORDER BY di tulis pada bagian paling bawah sehingga berpengaruh pada tabel hasil penggabungan.
194
BAB 10 Menguasai UNION
Nah, pada kondisi tertentu, kita ingin menggunakan klausa ORDER BY ini pada masing-masing query SELECT, tidak pada keseluruhan hasil query. Nah, studi kasus kali ini kita akan membahas bagaimana cara melakukan itu, kita gunakan contoh pada pembahasan pembuatan baris total, mari kita lihat kembali querynya: 1. 2. 3. 4. 5.
SELECT id_trx, tgl_trx, total_trx FROM penjualan UNION ALL SELECT 'TOTAL', "", SUM(total_trx) FROM penjualan
Hasil: +--------+------------+-----------+ | id_trx | tgl_trx | total_trx | +--------+------------+-----------+ | 1 | 2017-03-02 | 192000 | | 2 | 2017-03-10 | 186000 | | 3 | 2017-04-10 | 259000 | | 4 | 2017-04-05 | 110000 | | 5 | 2016-11-10 | 256000 | | TOTAL | | 1003000 | +--------+------------+-----------+
Selanjutnya kita akan urutkan data transaksi mulai dari yang terbesar, sehingga dapat mudah diketahui pelanggan mana yang memberikan kontribusi terbesar. Hasil yang diinginkan tampak seperti tabel berikut: +-------+------------+-----------+ | nama | tgl_trx | total_trx | +-------+------------+-----------+ | | 2017-04-10 | 259000 | | Beta | 2016-11-10 | 256000 | | Alfa | 2017-03-02 | 192000 | | Alfa | 2017-03-10 | 186000 | | Beta | 2017-04-05 | 110000 | | TOTAL | | 1003000 | +-------+------------+-----------+
Killer Trik Query MySQL
195
Bagaimana Query nya? Jika Anda paham pembahasan pada teknik pembuatan SUB TOTAL maka akan mudah menyelesaikan case ini. Query lengkapnya adalah sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
SELECT * FROM ( SELECT IFNULL(nama, "-") AS nama, tgl_trx, total_trx FROM penjualan LEFT JOIN pelanggan USING (id_pelanggan) ORDER BY total_trx DESC ) AS penjualan_rinci UNION ALL SELECT 'TOTAL',"", SUM(total_trx) FROM penjualan
Mungkin Anda bertanya tanya kenapa tidak langsung menggunakan ORDER BY di query utama kemudian diberi tanda kurung seperti ini? 1. ( SELECT IFNULL(nama, "-") AS nama, tgl_trx, total_trx 2. FROM penjualan 3. LEFT JOIN pelanggan USING (id_pelanggan) 4. ORDER BY total_trx DESC 5. ) 6. UNION ALL 7. ( SELECT 'TOTAL',"", SUM(total_trx) 8. FROM penjualan)
Query tersebut dapat berjalan dengan baik, namun data pada query pertama tidak berubah, kenapa? Karena seperti pada konsep UNION, MySQL tidak memproses klausa setelah GROUP BY yang ada pada pada masing masing klausa SELECT yang dilakukan secara langsung (tanpa subquery). Dengan demikian kita harus menggunakan subquery.
196
BAB 10 Menguasai UNION
BAB 11 Menguasai WITH ROLLUP Pada bab UNION kita telah belajar bagaimana membuat baris total dan subtotal, pada MySQL terdapat fitur khusus untuk menangani hal tersebut yaitu dengan klausa WITH ROLLUP, apakah klausa ini lebih powerful dibanding UNION? Kita bahas tuntas di bab ini.
11.1. Memahami Cara Kerja WITH ROLLUP WITH ROLLUP bekerja dengan menjumlahkan nilai kolom yang terdapat fungsi agregasinya,
sehingga jika ingin kolom terebut
muncul baris totalnya, maka kolom tersebut harus diberi fungsi agregasi Selanjutnya, selain fungsi agregasi, untuk dapat menggunakan klausa WITH ROLLUP, kita harus menggunakan klausa GROUP BY, klausa ini harus diletakkan tepat sebelum klausa WITH ROLLUP. Selain itu, ketika menggunakan klausa ini, kita tidak dapat menggunakan klausa ORDER BY, namun dapat mneggunakan klausa lain seperti HAVING dan LIMIT Untuk lebih jelasnya, misal kita miliki tabel penjualan dengan data sebagai berikut: +--------+--------------+------------+-----------+ | id_trx | id_pelanggan | tgl_trx | total_trx | +--------+--------------+------------+-----------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 0 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | +--------+--------------+------------+-----------+
Killer Trik Query MySQL
197
Selanjutnya kita tampilkan data penjualan tersebut dengan tambahan baris total di bagian akir baris, jalankan query berikut: 1. 2. 3. 4.
SELECT id_trx, id_pelanggan, tgl_trx, SUM(total_trx) FROM penjualan GROUP BY id_trx WITH ROLLUP
Hasil: +--------+--------------+------------+----------------+ | id_trx | id_pelanggan | tgl_trx | SUM(total_trx) | +--------+--------------+------------+----------------+ | 1 | 1 | 2017-03-02 | 192000 | | 2 | 1 | 2017-03-10 | 186000 | | 3 | 0 | 2017-04-10 | 259000 | | 4 | 2 | 2017-04-05 | 110000 | | 5 | 2 | 2016-11-10 | 256000 | | NULL | 2 | 2016-11-10 | 1003000 | +--------+--------------+------------+----------------+
Perhatikan bahwa pada query diatas, dengan GROUP BY kita kelompokkan data berdasar kolom id_trx meskipun riilnya tidak ada data yang dikelompokkan, karena tidak ada id_trx yang sama, hal ini hanya untuk "mengakali" saja agar kita dapat menggunakan WITH ROLLUP Agak maksa? Ya memang harus seperti itu….. Selain itu perhatikan bahwa kita juga menggunakan fungsi agregasi SUM pada kolom total_trx, sehingga pada baris total kita dapatkan jumlah total semua transaksi. Ciri khas baris hasil WITH ROLLUP ini adalah adanya nilai NULL pada kolom yang ada pada klausa GROUP BY (pada contoh diatas kolom id_trx pada baris total), sedangkan nilai pada kolom lain (yang tidak ada fungsi agregasinya) biasanya sama dengan nilai pada baris sebelumnya
198
BAB 11 Menguasai WITH ROLLUP
Selanjutnya mari kita lanjutkan pembahasan dengan membuat baris subtotal, misal kita kelompokkan data berdasarkan bulan transaksi dan id_pelanggan serta kita tampilkan data nama pelanggan. Adapun data tabel pelanggan adalah sebagai berikut: +--------------+---------+-----------+---------+ | id_pelanggan | nama | alamat | id_staf | +--------------+---------+-----------+---------+ | 1 | Alfa | Jakarta | 1 | | 2 | Beta | Semarang | 1 | | 3 | Charlie | Surabaya | 2 | | 4 | Delta | Surakarta | 3 | +--------------+---------+-----------+---------+
Selanjutnya jalankan query berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9.
SELECT MONTH(tgl_trx) AS bln_trx , id_pelanggan , nama , COUNT(id_trx) AS jml_trx , SUM(total_trx) AS nilai_trx FROM penjualan LEFT JOIN pelanggan USING (id_pelanggan) GROUP BY bln_trx, id_pelanggan WITH ROLLUP
Hasil yang kita peroleh adalah: +---------+--------------+------+---------+-----------+ | bln_trx | id_pelanggan | nama | jml_trx | nilai_trx | +---------+--------------+------+---------+-----------+ | 3 | 1 | Alfa | 2 | 378000 | | 3 | NULL | Alfa | 2 | 378000 | | 4 | 0 | NULL | 1 | 259000 | | 4 | 2 | Beta | 1 | 110000 | | 4 | NULL | Beta | 2 | 369000 | | 11 | 2 | Beta | 1 | 256000 | | 11 | NULL | Beta | 1 | 256000 | | NULL | NULL | Beta | 5 | 1003000 | +---------+--------------+------+---------+-----------+
Killer Trik Query MySQL
199
Perhatikan bahwa pada contoh diatas, terdapat beberapa baris tambahan yang dihasilkan dari klausa WITH ROLLUP (baris yang mengandung nilai NULL), yaitu pada kolom bln_trx dan id_pelanggan. Perhatikan juga bahwa WITH ROLLUP mengkalkulasi nilai pada kolom yang terdapat fungsi COUNT() dan SUM() yaitu kolom jml_trx dan nilai_trx Karena pada klausa GROUP BY kita mengelompokkan data urut berdasarkan bulan, baru kemudian id_pelanggan, maka ketika MySQL membuat total untuk kelompok tersebut, MySQL akan memberikan nilai NULL pada kolom id_pelanggan (untuk subtotal setiap bulan) dan id_pelanggan + bulan untuk total semua data, perhatikan gambar berikut:
Gambar 11.1 Ilustrsi WITH ROLLUP Pada Beberapa Kolom
Sampai disini sudah paham kan? Bagaimana klausa WITH ROLLUP bekerja? Jika sudah, mari kita lanjutkan…
200
BAB 11 Menguasai WITH ROLLUP
11.2. Mengganti Nilai NULL Pada bagian sebelumnya terlihat bahwa dengan WITH ROLLUP nilai kolom yang ada pada klausa GROUP BY akan bernilai NULL. Agar lebih memiliki arti, kita perlu menggantinya dengan kata lain, seperti TOTAL atau SUB TOTAL Melanjutkan contoh sebelumnya, kita kelompokkan data penjualan berdasarkan bulan dan id_pelanggan dan kita ganti nilai NULL dengan string TOTAL dan SUB TOTAL Jalankan query berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9.
SELECT MONTH(tgl_trx) AS bln_trx , id_pelanggan , nama , COUNT(id_trx) AS jml_trx , SUM(total_trx) AS nilai_trx FROM penjualan LEFT JOIN pelanggan USING (id_pelanggan) GROUP BY bln_trx, id_pelanggan WITH ROLLUP
Hasilnya adalah: +---------+-----------+------+---------+-----------+ | bln_trx | id | nama | jml_trx | nilai_trx | +---------+-----------+------+---------+-----------+ | 11 | 2 | Beta | 1 | 256000 | | 11 | SUB TOTAL | Beta | 1 | 256000 | | 3 | 1 | Alfa | 2 | 378000 | | 3 | SUB TOTAL | Alfa | 2 | 378000 | | 4 | 0 | NULL | 1 | 259000 | | 4 | 2 | Beta | 1 | 110000 | | 4 | SUB TOTAL | Beta | 2 | 369000 | | NULL | SUB TOTAL | Beta | 5 | 1003000 | +---------+-----------+------+---------+-----------+
Pada contoh diatas, kita berhasil mengubah nilai NULL pada kolom id menjadi SUB TOTAL, namun kenapa nilai pada bln_trx tetap NULL?
Killer Trik Query MySQL
201
Jawabnya, memang untuk fungsi tertentu, khususnya fungsi terkait date time, kita tidak dapat mengubah NULL secara langsung seketika saat fungsi dijalankan, sebagai solusinya, kita dapat menggunakan subquery sebagai berikut: 1. SELECT IFNULL(bln_trx, "TOTAL") AS bulan 2. , id 3. , nama 4. , jml_trx 5. , nilai_trx 6. FROM 7. (SELECT MONTH(tgl_trx) AS bln_trx 8. , IFNULL(id_pelanggan, "TOTAL") AS id, nama 9. , COUNT(id_trx) AS jml_trx 10. , SUM(total_trx) AS nilai_trx 11. FROM penjualan 12. LEFT JOIN pelanggan USING (id_pelanggan) 13. GROUP BY bln_trx, id_pelanggan 14. WITH ROLLUP 15. ) AS penjualan
Hasil: +-------+-------+------+---------+-----------+ | bulan | id | nama | jml_trx | nilai_trx | +-------+-------+------+---------+-----------+ | 3 | 1 | Alfa | 2 | 378000 | | 3 | TOTAL | Alfa | 2 | 378000 | | 4 | 0 | NULL | 1 | 259000 | | 4 | 2 | Beta | 1 | 110000 | | 4 | TOTAL | Beta | 2 | 369000 | | 11 | 2 | Beta | 1 | 256000 | | 11 | TOTAL | Beta | 1 | 256000 | | TOTAL | TOTAL | Beta | 5 | 1003000 | +-------+-------+------+---------+-----------+
Kenapa
subquery? Ingat kembali prinsip bahwa jika kita ingin
mengolah tabel hasil query, maka kita harus menggunakan subquery, kenapa? karena pada subquery, temporary tabel sudah terbentuk sehingga tabel tersebut dapat diolah layaknya tabel riil.
202
BAB 11 Menguasai WITH ROLLUP
11.3. ORDER BY Pada WITH ROLLUP Seperti disampaikan diawal, bahwa ketika menggunakan WITH ROLLUP kita mutlak tidak dapat menggunakan klausa ORDER BY, sehingga jika kita ingin mengurutkan data (termasuk baris baru hasil WITH ROLLUP) kita harus menggunakan subquery. Misal
melanjutkan
contoh
sebelumnya,
kita
urutkan
data
berdasarkan bulan secara desending, hasil yang kita harapkan adalah sebagai berikut: +---------+-----------+------+---------+-----------+ | bln_trx | id | nama | jml_trx | nilai_trx | +---------+-----------+------+---------+-----------+ | 4 | 0 | NULL | 1 | 259000 | | 4 | 2 | Beta | 1 | 110000 | | 4 | SUB TOTAL | Beta | 2 | 369000 | | 3 | 1 | Alfa | 2 | 378000 | | 3 | SUB TOTAL | Alfa | 2 | 378000 | | NULL | SUB TOTAL | Alfa | 4 | 747000 | +---------+-----------+------+---------+-----------+
Bagaimana querynya? Untuk memperoleh hasil seperti tabel diatas, kita tinggal tambahkan DESC pada ORDER BY sebagai berikut: 1. 2. 3. 4. 5. 6. 7. 8. 9.
SELECT MONTH(tgl_trx) AS bln_trx , IFNULL(id_pelanggan, "SUB TOTAL") AS id, nama , COUNT(id_trx) AS jml_trx , SUM(total_trx) AS nilai_trx FROM penjualan LEFT JOIN pelanggan USING (id_pelanggan) WHERE YEAR(tgl_trx) = 2017 GROUP BY bln_trx DESC, id_pelanggan WITH ROLLUP
Perhatikan bahwa pada baris nomor 8 kita tambahkan DESC setelah kolom bln_trx, simpel kan?
Killer Trik Query MySQL
203
Kenapa bisa seperti itu? Seperti yang telah kita bahas pada BAB I, ketika MySQL menjalankan GROUP BY, maka otomatis MySQL akan mengurutkan data berdasarkan data kolom yang ada pada klausa GROUP BY, hal ini disebut implisit order. Secara default, implisit order berbentuk ascending (dari kecil ke besar), namun demikian kita dapat mengubahnya menjadi descending, yaitu dengan menambahkan DESC pada kolom yang ingin kita ubah urutannya.
Tetapi… Tetapi…. sejak MySQL 5.7 cara ini sudah deprecated yang artinya akan dihilangkan pada versi berikutnya (entah kapan), sehingga jika kita menggunakan teknik ini, maka kode yang kita buat tidak “aman” yang artinya tidak akan berjalan pada MySQL versi terbaru. Saya pribadi tidak nyaman jika menggunakan fitur yang deprecated, karena kedepannya mau tidak mau kita harus beralih ke versi terbaru, entah karena hardware yang sudah tidak kompatibel, kebijakan management yang berubah, dll sehingga sebisa mungkin hindari fitur deprecated. Ketika menjalankan klausa GROUP BY, dibelakang layar MySQL akan mengurutkan data kolom yang ada pada klausa group by secara ascending (implisit order), kita dapat mengubah pola pengurutan ini dengan menambahkan DESC pada kolom yang ada pada klausa GROUP BY Sejak MySQL versi 5.7, fitur ini deprecated, yang artinya akan dihilangkan
204
pada
MySQL
versi
BAB 11 Menguasai WITH ROLLUP
berikutnya,
sehingga
disarankan untuk menggunakan eksplisit order dengan menuliskan klausa ORDER BY Nah, melanjutkan case sebelumnya, bagaimana query yang kita gunakan jika menggunakan eksplisit order? Silakan dicoba, sebagai clue, kita akan gunakan subquery Sudah bisa? Baiklah, mari kita cocokkan. Query versi saya adalah sebagai berikut: 1. SELECT * 2. FROM 3. (SELECT MONTH(tgl_trx) AS bln_trx 4. , id_pelanggan AS id, nama 5. , COUNT(id_trx) AS jml_trx 6. , SUM(total_trx) AS nilai_trx 7. FROM penjualan 8. LEFT JOIN pelanggan USING (id_pelanggan) 9. WHERE YEAR(tgl_trx) = 2017 10. GROUP BY bln_trx, id_pelanggan 11. WITH ROLLUP 12. ) AS penjualan 13. ORDER BY bln_trx DESC, id DESC
Hasil yang kita peroleh adalah: +---------+------+------+---------+-----------+ | bln_trx | id | nama | jml_trx | nilai_trx | +---------+------+------+---------+-----------+ | 4 | 2 | Beta | 1 | 110000 | | 4 | 0 | NULL | 1 | 259000 | | 4 | NULL | Beta | 2 | 369000 | | 3 | 1 | Alfa | 2 | 378000 | | 3 | NULL | Alfa | 2 | 378000 | | NULL | NULL | Beta | 4 | 747000 | +---------+------+------+---------+-----------+
Penjelasan:
Killer Trik Query MySQL
205
Karena terdapat WITH ROLLUP maka untuk menggunakan ORDER BY, mau tidak mau kita harus menggunakan subquery
Kenapa kembali ke nilai NULL? bukan kata kata TOTAL atau SUB TOTAL? kita menggunakan nilai NULL agar mudah mengatur posisi baris null, karena jika kita urutkan secara ascending, nilai NULL akan selalu diatas sedangkan untuk descending akan selalu dibawah Untuk
memudahkan
penempatan
baris
TOTAL
dan
SUBTOTAL, selalu perhatikan nilai NULL karena nilai tersebut akan selalu berada dibawah ketika diurutkan secara descending Selanjutnya sebagai latihan, ubah nilai NULL tersebut dengan nilai string TOTAL dan SUB TOTAL Silakan dicoba? Apakah queryny sama seperti saya ini? 1. SELECT IFNULL(bln_trx, "TOTAL") AS bulan 2. , IFNULL(id, "SUB TOTAL") AS id 3. , nama 4. , jml_trx 5. , nilai_trx 6. FROM 7. ( 8. SELECT * 9. FROM 10. (SELECT MONTH(tgl_trx) AS bln_trx 11. , id_pelanggan AS id 12. , IFNULL(nama, "-") AS nama 13. , COUNT(id_trx) AS jml_trx 14. , SUM(total_trx) AS nilai_trx 15. FROM penjualan 16. LEFT JOIN pelanggan USING (id_pelanggan) 17. WHERE YEAR(tgl_trx) = 2017 18. GROUP BY bln_trx, id_pelanggan
206
BAB 11 Menguasai WITH ROLLUP
19. WITH ROLLUP 20. ) AS penjualan 21. ORDER BY bln_trx DESC, id DESC 22. ) AS penjualan
Hasil: +-------+-----------+------+---------+-----------+ | bulan | id | nama | jml_trx | nilai_trx | +-------+-----------+------+---------+-----------+ | 4 | 2 | Beta | 1 | 110000 | | 4 | 0 | | 1 | 259000 | | 4 | SUB TOTAL | Beta | 2 | 369000 | | 3 | 1 | Alfa | 2 | 378000 | | 3 | SUB TOTAL | Alfa | 2 | 378000 | | TOTAL | SUB TOTAL | Beta | 4 | 747000 | +-------+-----------+------+---------+-----------+
Penjelasan: kita akan mengolah lagi tabel hasil query, sehingga mau tidak mau kita harus menggunakan subquery
Real World…. Dalam dunia nyata, pengurutan data bisa sangat kompleks, misal pada tabel hasil query diatas, selain terdapat baris total dan subtotal, diata diurutkan lagi berdasarkan nilai_trx tertinggi Bisakah dilakukan dengan SQL? Sayang sekali tidak bisa… Kenapa? Karena SQL hanya bahasa deklarasi (seperti HTML dan CSS) yang hanya ditujukan untuk pengambilan data untuk dapat diolah lebih lanjut oleh aplikasi lain bukan bahasa procedural seperti PHP dan ASP yang memang ditujukan untuk menyelesaikan suatu masalah. So… jika memang tidak bisa dikerjakan di SQL, tidak perlu dipaksakan….
Killer Trik Query MySQL
207
11.4. Studi kasus Sebagai contoh kasus, kita lanjutkan contoh pada bagian sebelumnya sehingga tabel yang dihasikan lebih informatif dan siap untuk disajikan, tabel hasil query sebelumnya adalah seperti ini: +-------+-----------+------+---------+-----------+ | bulan | id | nama | jml_trx | nilai_trx | +-------+-----------+------+---------+-----------+ | 4 | 2 | Beta | 1 | 110000 | | 4 | 0 | | 1 | 259000 | | 4 | SUB TOTAL | Beta | 2 | 369000 | | 3 | 1 | Alfa | 2 | 378000 | | 3 | SUB TOTAL | Alfa | 2 | 378000 | | TOTAL | SUB TOTAL | Beta | 4 | 747000 | +-------+-----------+------+---------+-----------+
Nah, kita akan mengubahnya menjadi seperti ini: +-----------+-------+---------+-----------+ | nama | bulan | jml_trx | nilai_trx | +-----------+-------+---------+-----------+ | Beta | 4 | 1 | 110000 | | | 4 | 1 | 259000 | | SUB TOTAL | 4 | 2 | 369000 | | Alfa | 3 | 2 | 378000 | | SUB TOTAL | 3 | 2 | 378000 | | TOTAL | | 4 | 747000 | +-----------+-------+---------+-----------+
Bagaimana querynya? Silakan dicoba, cara yang digunakan sudah dibahas pada bab bab sebelumnya…. Sebagai clue, kita gunakan ekspresi logika CASE Sudah? Baik, mari kita cocokkan. Query versi saya adalah sebagai berikut: 1. SELECT CASE WHEN bln_trx IS NULL THEN "TOTAL" 2. WHEN id IS NULL THEN "SUB TOTAL"
208
BAB 11 Menguasai WITH ROLLUP
3. ELSE nama 4. END AS nama 5. , IFNULL(bln_trx, "") AS bulan 6. , jml_trx 7. , nilai_trx 8. FROM 9. ( 10. SELECT * 11. FROM 12. ( SELECT MONTH(tgl_trx) AS bln_trx 13. , id_pelanggan AS id 14. , IFNULL(nama, "-") AS nama 15. , COUNT(id_trx) AS jml_trx 16. , SUM(total_trx) AS nilai_trx 17. FROM penjualan 18. LEFT JOIN pelanggan USING (id_pelanggan) 19. WHERE YEAR(tgl_trx) = 2017 20. GROUP BY bln_trx, id_pelanggan 21. WITH ROLLUP 22. ) AS penjualan 23. ORDER BY bln_trx DESC, id DESC, nilai_trx DESC 24. ) AS penjualan
Ingat kembali pinsip eksekusi sql, bahwa pada statemen SELECT, setiap baris pada tabel hasil klausa FROM akan di evaluasi sesuai dengan yang ada pada klausa select, pehatikan ilustrasi berikut:
Gambar 11.2 Ilustrasi Eksekusi Klausa SELECT
Killer Trik Query MySQL
209
Pada contoh diatas, karena baris ketiga kolom id bernilai NULL, maka memenuhi kondisi kedua pada ekspresi CASE, sehingga kolom nama bernilai SUB TOTAL, demikian juga dengan kolom lain.
210
BAB 11 Menguasai WITH ROLLUP
BAB 12. Menguasai Variable Pada kasus tertentu, kita perlu menggunakan hasil eksekusi suatu query untuk kemudian digunakan pada query lain, untuk keperluan tersebut,
kita
perlu menyimpan
data hasil query.
MySQL
memfasilitasi ini dengan menyediakan fitur bernama variabel. Materi tentang variabel merupakan materi lanjutan, di buku ini kita tidak membahasnya secara mendalam, initinya Anda paham variabel dan dapat menggunakannya pada kasus kasus umum.
12.1. Memahami Variable Variabel digunakan untuk menyimpan data, baik data scalar, ekspresi, maupun data hasil query. Sesuai artinya yaitu berubah, maka nilai variabel ini dapat berubah ubah. Pada MySQL, variable dapat di deklarasikan menggunakan dua cara yaitu menggunakan statement SET dan SELECT diikuti nama variable. Contoh pendeklarasian variable: 1. 2. 3. 4.
SET @nomor := SET @total := SELECT @nomor SELECT @total
1; (SELECT SUM(total_trx) FROM penjualan); := 1; := (SELECT SUM(total_trx) FROM penjualan);
Note: jika nilai variable berupa query, maka query tersebut harus diletakkan di dalam tanda kurung (baris 2 dan 4) Dalam satu statement, kita dapat mendeklarasikan beberapa variable sekaligus, caranya, pisahkan variable tersebut dengan tanda koma, misal:
Killer Trik Query MySQL
211
1. SET @nomor := 1, @total := (SELECT SUM(total_trx) FROM penjualan); 2. SELECT @nomor := 1, SELECT @total := (SELECT SUM(total_trx) FROM penjualan);
Ketentuan:
Untuk meng assign nilai pada variabel, kita dapat menggunakan tanda := atau =
Tanda := dapat digunaan pada SET dan SELECT sedangkan = hanya dapat digunakan pada SET. Untuk memudahkan, gunakan selalu :=
Nama variabel harus diawali dengan @ dan diikuti dengan: huruf, angka, titik (.), underscore (_), atau dollar ($)
Nama variabel bersifat case insensitive, tidak membedakan huruf kapital, @Nomor akan sama dengan @nomor
Variable hanya dapat diisi satu nilai. Jika nilai tersebut berupa hasil query, maka data hasil query tersebut harus terdiri dari satu nilai (satu kolom dan satu baris)
Sebagai contoh, misal kita urutkan data tabel penjualan berdasarkan tanggal transaksi dan kita beri kolom nomor urut. Query yang kita jalankan: 1. 2. 3. 4. 5. 6. 7. 8.
212
SET @no := 0; SELECT @no := @no + 1 AS no_urut , nama , tgl_trx FROM penjualan LEFT JOIN pelanggan USING (id_pelanggan) WHERE YEAR(tgl_trx) = 2017 ORDER BY tgl_trx
BAB 12. Menguasai Variable
Hasil: +---------+------+------------+ | no_urut | nama | tgl_trx | +---------+------+------------+ | 1 | Alfa | 2017-03-02 | | 2 | Alfa | 2017-03-10 | | 3 | Beta | 2017-04-05 | | 4 | NULL | 2017-04-10 | +---------+------+------------+
Pada query diatas, pertama tama kita deklarasikan variabel @no dengan nilai 0, selanjutnya, pada statement SELECT, variabel tersebut akan dieksekusi sebanyak jumlah baris yang ada pada tabel hasil klausa FROM, ingat kembali konsep urutan eksekusi query, bahwa ketik menjalankan klausa select, setiap baris tabel hasil klausa from akan dieksekusi. Setiap kali variabel tersebut dieksekusi, nilainya akan selalu bertambah satu @no := @no + 1, sehingga akan membentuk data nomor urut.
12.2. Single Statement Pada
contoh
sebelumnya,
kita
mendefinisikan
variabel
menggunakan statemen SET di luar statemen SELECT, sehingga terdapat dua statemen yaitu SET dan SELECT. Ketika praktik lapangan dimana query SQL kita terapkan pada aplikasi,
maka
pendefinisian
dua
statement
ini
terkadang
merepotkan, untuk itu kita perlu mendefinisikan nya dengan satu statemen (single statemen). Dengan single statemen, maka query diatas akan berbentuk: 1. SELECT @no := @no + 1 AS no_urut
Killer Trik Query MySQL
213
2. , nama 3. , tgl_trx 4. FROM (SELECT @no := 0) AS nomor 5. , penjualan 6. LEFT JOIN pelanggan USING (id_pelanggan) 7. WHERE YEAR(tgl_trx) = 2017 8. ORDER BY tgl_trx
Hasil yang kita peroleh sama persis yaitu: +---------+------+------------+ | no_urut | nama | tgl_trx | +---------+------+------------+ | 1 | Alfa | 2017-03-02 | | 2 | Alfa | 2017-03-10 | | 3 | Beta | 2017-04-05 | | 4 | NULL | 2017-04-10 | +---------+------+------------+
Bagaimana proses pendefinisian variabel diatas? Ingat kembali konsep urutan eksekusi query Sudah paham kan? Yup, seperti yang telah kita pelajari, bahwa pertama kali yang dieksekusi adalah klausa FROM 1. FROM (SELECT @no := 0) AS nomor 2. , penjualan 3. LEFT JOIN pelanggan USING (id_pelanggan)
Nah, pada klausa tersebut, selain menggabungkan tabel penjualan dan pelanggan, MySQL juga mendefinisikan variabel @no, nah disitulah awal mula variabel @no didefinisikan dengan nilai 0 Dari sini sudah paham kan kelanjutannya bagaimana variabel @no bisa berbentuk no urut?
214
BAB 12. Menguasai Variable
Yup, ketika mendefinisikan variabel pada statemen SELECT, yaitu pada contoh diatas @no := @no + 1 maka selain menyimpan nilai variabel, nilai variabel tersebut akan dicetak. Sudah tahu kan bagaimana proses penyimpanan nilai variabel? Prosesnya sama seperti bahasa pemrograman umum… Ketika menyimpan variabel, ekspresi sebelah kanan tanda := akan di eksekusi terlebih dahulu, hasilnya disimpan ke variabel di sebelah kiri, sehingga pada eksekusi pertama nilai sebelah kanan adalah satu (0 + 1) hasilnya disimpan pada variabel @no dan dicetak sehingga muncul angka 1, demikian seterusnya hingga sampai eksekusi baris terakhir. Penggunaan single statemen akan lebih memudahkan dalam pengembangan aplikasi karena lebih mudah mengeksekusi satu statemen dari pada dua statemen, terlebih jika aplikasi kita menggunakan framework (seperti codeigniter pada PHP). Contoh penerapan single statement pada aplikasi berbasis PHP 1.
