Penggabungan Untuk Mengisi Kekosongan - MySQL Dasar #78

Lihat daftar isi : Menggunakan Multi Tabel - MySQL Dasar #72 

Sehingga untuk menyiasati masalah ini bisa dengan cara menciptakan sebuah tabel refrensi yang mencantumkan tiap kategori dan menghasilkan kesimpulan berdasarkan operasi LEFT JOIN antara tabel refrensi tersebut dan tabel yang memuat data. 

Jadi, tiap kategori pada tabel refrensi akan ditampilkan pada kolom keluaran. Seperti yang kita ketahui bersama bahwa ketika query kesimpulan dijalankan, maka dia hanya akan menghasilkan masukan – masukan untuk nilai – nilai yang ada pada data. Dimisalkan disini kita akan mencoba menghasilkan sebuah query kesimpulan berdasarkan waktu (t) pada tabel mail berikut

MariaDB [root93]> SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+--------+
| t                   | srcuser | srchost | dstuser | dsthost | ukuran |
+---------------------+---------+---------+---------+---------+--------+
| 2021-10-13 12:32:41 | barb    | saturn  | tricia  | mars    |  58274 |
| 2021-10-13 10:36:44 | tricia  | mars    | gene    | venus   |  19263 |
| 2021-10-13 10:37:20 | phil    | mars    | phil    | saturn  |   1048 |
| 2021-10-13 10:38:06 | barb    | saturn  | tricia  | venus   |    271 |
| 2021-10-13 10:38:54 | phil    | mars    | tricia  | saturn  |   5781 |
| 2021-10-13 10:39:37 | gene    | venus   | barb    | mars    |   2291 |
| 2021-10-13 10:40:07 | barb    | venus   | barb    | venus   |  98161 |
| 2021-10-13 10:40:45 | tricia  | saturn  | phil    | venus   | 239238 |
| 2021-10-13 10:41:27 | gene    | mars    | gene    | saturn  |   3623 |
| 2021-10-13 10:41:27 | gene    | saturn  | gene    | mars    |  32682 |
| 2021-10-13 10:42:32 | gene    | venus   | barb    | mars    |   3872 |
| 2021-10-13 10:42:32 | phil    | venus   | barb    | venus   |   7823 |
| 2021-10-13 10:43:39 | gene    | saturn  | gene    | venus   |  22332 |
+---------------------+---------+---------+---------+---------+--------+
13 rows in set (0.025 sec)

Untuk menentukan berapa banyak pesan yang dikirim tiap jam, kita bisa menggunakan query kesimpulan seperti berikut

MariaDB [root93]> SELECT HOUR(t) AS jam, COUNT(HOUR(t)) AS jumlah
    -> FROM mail
    -> GROUP by jam;
+------+--------+
| jam  | jumlah |
+------+--------+
|   10 |     12 |
|   12 |      1 |
+------+--------+
2 rows in set (0.009 sec)

Pada hasil keluaran diatas, kita bisa melihat bahwa hasil query hanya menampilkan jam – jam yang memang direpresentasikan pada tabel mail yaitu jam 10 dan 12. Sehingga jika kita misalnya ingin menghasilkan kesimpulan yang mencantumkan semua jam, meskipun misalnya jam(waktu) tersebut tidak terdapat pada tabel mail maka kita bisa menggunakan/menciptakan tabel refrensi untuk mengisi kekosongan tersebut.

MariaDB [root93]> INSERT INTO ref(h)
    -> VALUES (0), (1), (2), (3), (4),(5),(6),(7),(8),(9),(10),(11),
    -> (12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);
Query OK, 24 rows affected (0.043 sec)
Records: 24  Duplicates: 0  Warnings: 0

Selanjutnya, kita gunakan operasi penggabungan LEFT JOIN antara tabel refrensi dengan tabel mail

MariaDB [root93]> SELECT ref.h AS jam, COUNT(HOUR(mail.t)) AS jumlah
    -> FROM ref LEFT JOIN mail
    -> ON ref.h=HOUR(mail.t)
    -> GROUP by jam;
+------+--------+
| jam  | jumlah |
+------+--------+
|    0 |      0 |
|    1 |      0 |
|    2 |      0 |
|    3 |      0 |
|    4 |      0 |
|    5 |      0 |
|    6 |      0 |
|    7 |      0 |
|    8 |      0 |
|    9 |      0 |
|   10 |     12 |
|   11 |      0 |
|   12 |      1 |
|   13 |      0 |
|   14 |      0 |
|   15 |      0 |
|   16 |      0 |
|   17 |      0 |
|   18 |      0 |
|   19 |      0 |
|   20 |      0 |
|   21 |      0 |
|   22 |      0 |
|   23 |      0 |
+------+--------+
24 rows in set (0.001 sec)

Sekarang terlihat dari query keluaran diatas bahwa operasi LEFT JOIN akan memaksa setiap jam untuk mencantumkan baris untuk setiap rekaman pada tabel refrensi tanpa memandang isi dari tabel mail. Kemudian jika misalnya kita menampilkan kategori jam yang tidak ada pada yang akan disimpulkan. Query berikut ini akan menunjukan kategori jam-jam dimana tidak ada pesan yang terkirim dengan memanfaatkan klausa HAVING

MariaDB [root93]> SELECT ref.h AS jam, COUNT(HOUR(mail.t)) AS jumlah
    -> FROM ref LEFT JOIN mail ON ref.h=HOUR(mail.t)
    -> GROUP by jam
    -> HAVING jumlah=0;
+------+--------+
| jam  | jumlah |
+------+--------+
|    0 |      0 |
|    1 |      0 |
|    2 |      0 |
|    3 |      0 |
|    4 |      0 |
|    5 |      0 |
|    6 |      0 |
|    7 |      0 |
|    8 |      0 |
|    9 |      0 |
|   11 |      0 |
|   13 |      0 |
|   14 |      0 |
|   15 |      0 |
|   16 |      0 |
|   17 |      0 |
|   18 |      0 |
|   19 |      0 |
|   20 |      0 |
|   21 |      0 |
|   22 |      0 |
|   23 |      0 |
+------+--------+
22 rows in set (0.001 sec)

Sebenarnya bisa saja query kesimpulan GROUP BY tidak perlu digunakan seperti diatas ketika misalnya kita ingin hanya menampilkan daftar jam – jam yang tidak direfrensikan pada tabel mail. Contohnya :

MariaDB [root93]> SELECT ref.h AS jam
    -> FROM ref LEFT JOIN mail ON ref.h=HOUR(mail.t)
    -> WHERE mail.t IS NULL;
+------+
| jam  |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   11 |
|   13 |
|   14 |
|   15 |
|   16 |
|   17 |
|   18 |
|   19 |
|   20 |
|   21 |
|   22 |
|   23 |
+------+
22 rows in set (0.001 sec)