MySQL: Perintah Query untuk Mencari Record yang Tidak Ada di Tabel Lain

Sebagai programmer, saat membangun suatu aplikasi seringkali kita menemukan permasalahan atau kondisi yang mengharuskan kita melakukan perbandingan record pada tabel basis data. Sebagai contoh sederhana adalah pada kasus sistem perpustakaan. Jika data transaksi peminjaman dan pengembalian terpisah dalam tabel berbeda, maka akan timbul permasalahan saat kita ingin menampilkan buku atau transaksi yang belum dikembalikan. Dengan kata lain, kita harus dapat melakukan perbandingan diantara tabel peminjaman dan pengembalian. Untuk menampilkan data transaksi yang belum dikembalikan, tentu kita harus mencari record yang ada di tabel peminjaman namun tidak ada di tabel pengembalian.

Pada tutorial ini akan dibahas mengenai perintah query untuk mencari atau menampilkan record yang ada di suatu tabel namun tidak ada di tabel yang lainnya, seperti pada kasus peminjaman dan pengembalian buku perpustakaan di atas. Seluruh perintah query dalam tutorial ini diujicobakan dalam database MySQL. Dan sebelum melanjutkan tutorial ini, alangkah baiknya jika Anda mengenal terlebih dahulu perintah-perintah dasar MySQL dan bagaimana perintah penggabungan (join) beberapa tabel di MySQL. Semoga tutorial ini bermanfaat untuk kita semua.

Contoh Tabel dan Record: Sistem Informasi Perpustakaan

Sebagai contoh dalam tutorial ini, dibuat struktur basis data untuk Sistem Informasi Perpustakaan sederhana. Lihat Gambar 1 . Data buku tersimpan di tabel buku, dan untuk setiap copy buku disimpan di tabel copy_buku. Untuk meminjam buku di perpustakaan, peminjam harus menjadi anggota terlebih dahulu. Tidak ada batasan jumlah buku untuk setiap transaksi peminjaman, namun lama peminjaman normal dibatasi hanya 1 minggu kalender (tidak memperhitungkan hari libur). Untuk seluruh buku yang dipinjam dalam satu transaksi peminjaman harus dikembalikan bersamaan, dan pengembalian setelah batas waktu pinjam akan dikenakan denda.

Contoh Struktur Basis Data Sistem Perpustakaan

Gambar 1: Contoh Struktur Basis Data Sistem Perpustakaan

Perintah SQL untuk membuat dan mengisi data pada struktur basis data di atas dapat diunduh di http://achmatim.net/_downloads/dbperpustakaan.sql.

Penggabungan (JOIN) Beberapa Tabel

Dalam pembuatan sistem informasi, seringkali kita membutuhkan penggabungan beberapa tabel sekaligus. Untuk melakukan penggabungan tabel, hal utama yang harus diperhatikan adalah setiap tabel yang akan digabungkan harus saling terhubung atau ber-relasi. Sebagai contoh pada struktur basis data Gambar 1 di atas, tabel anggota dan tabel peminjaman dapat digabungkan (di-JOIN) karena terdapat field yang menjadi primary key di tabel anggota dan foreign key di tabel peminjaman, yaitu kd_anggota. Field tersebut-lah yang menjadi penghubung kedua tabel. Sementara itu, antara tabel anggota dan pengembalian tidak dapat digabung secara langsung karena tidak memiliki field penghubung. Keduanya dapat digabungkan dengan menyertakan tabel peminjaman dalam proses penggabungan. Lebih jelas mengenai perintah penggabungan beberapa tabel di MySQL dapat mempelajari tutorial saya sebelumnya berjudul Perintah MySQL untuk Menampilkan Data Dari Beberapa Tabel.

Menampilkan Transaksi Peminjaman yang Belum Kembali

Kembali ke permasalahan utama tutorial ini, yaitu menampilkan data di suatu tabel yang tidak ada di tabel yang lainnya. Dalam contoh kasus Sistem Informasi Perpustakaan di atas, kita akan menampilkan transaksi peminjaman yang belum dikembalikan. Data yang akan ditampilkan adalah ID Peminjaman, tanggal peminjaman, nama anggota peminjam dan batas akhir pengembalian. Untuk melakukan hal tersebut, kita perlu menggabungkan 3 (tiga) buah tabel sekaligus yaitu tabel (1) anggota, (2) peminjaman dan (3) pengembalian. Kita akan menampilkan data yang ada di tabel peminjaman namun belum ada di tabel pengembalian.

Perhatikan perintah query sebagai berikut. Antara tabel anggota dan peminjaman dilakukan INNER JOIN sedangkan antara peminjaman dan pengembalian dilakukan LEFT JOIN karena terdapat kemungkinan data di peminjaman tidak ada di tabel pengembalian. Jika ingin menampilkan data peminjaman yang belum kembali cukup menambahkan kondisi dimana ID pinjam di tabel pengembalian masih kosong (NULL).

SELECT b.id_pinjam, b.tgl_pinjam, a.nama, b.tgl_hrskembali 
FROM anggota a INNER JOIN peminjaman b ON a.kd_anggota=b.kd_anggota
LEFT JOIN pengembalian c ON b.id_pinjam=c.id_pinjam
WHERE c.id_pinjam IS NULL

Cara lain untuk menyelesaikan permasalahan yang sama adalah dengan menggunakan sub-query atau sub-select.  Cara kedua ini tidak memerlukan join dengan tabel pengembalian.

SELECT b.id_pinjam, b.tgl_pinjam, a.nama, b.tgl_hrskembali 
FROM anggota a INNER JOIN peminjaman b ON a.kd_anggota=b.kd_anggota
WHERE b.id_pinjam NOT IN (SELECT id_pinjam FROM pengembalian)

Kedua query di atas akan menampilkan hasil yang sama, namun demikian query 1 yang menggunakan JOIN lebih disarankan karena memiliki performa / kecepatan yang lebih baik. Beberapa sumber menyatakan bahwa query JOIN lebih baik dari SUBQUERY dari sisi performa, diantaranya salah satu diskusi di situs StackOverflow.

Berikut ini hasil dari perintah query diatas.

hasil-1

 

Menampilkan Buku yang Masih Dipinjam (Belum Dikembalikan)

Pada dasarnya permasalahan ini sama seperti sebelumnya, hanya saja data yang ingin ditampilkan adalah data buku, bukan data transaksi peminjaman. Kolom yang akan ditampilkan meliputi kode buku, judul buku, nama peminjam, tanggal peminjaman dan tanggal harus kembali. Untuk menyelesaikannya, kita perlu melibatkan tabel detil_pinjam serta tabel terkait buku dan copy buku. Dengan kata lain, kita perlu menggabungkan semua tabel pada Gambar 1.

Perhatikan dan pahami query 3 berikut ini.

SELECT e.kd_buku, e.judul, a.nama, b.tgl_pinjam, b.tgl_hrskembali
FROM anggota a INNER JOIN peminjaman b ON a.kd_anggota=b.kd_anggota
INNER JOIN detil_pinjam c ON b.id_pinjam=c.id_pinjam
INNER JOIN copy_buku d ON c.kd_copybuku=d.kd_copybuku
INNER JOIN buku e ON d.kd_buku=e.kd_buku
LEFT JOIN pengembalian f ON b.id_pinjam=f.id_pinjam
WHERE f.id_pinjam IS NULL
ORDER BY e.judul

Hasil dari perintah query 3 di atas sebagai berikut.

hasil-2

Kesimpulan

Tutorial ini menjelaskan secara singkat mengenai bagaimana perintah query untuk mencari record atau data yang ada di suatu tabel namun tidak ada di tabel yang lainnya. Dalam tutorial ini dicontohkan kasus sistem informasi perpustakaan. Solusi permasalahan dapat menggunakan query dengan penggabungan LEFT JOIN dan query dengan SUB-SELECT atau SUBQUERY. Keduanya dapat digunakan namun lebih disarankan menggunakan LEFT JOIN karena lebih baik secara performa.

Download Tutorial

Referensi

Semoga tutorial ini bermanfaat untuk kita semua dan berbagilah hal baik ke sebanyak-banyaknya orang maka kebahagiaan akan menghampiri Anda. Satu Karya untuk Indonesia!

Comments
  1. 1 year ago
  2. 1 year ago
  3. 1 year ago
  4. 1 year ago
  5. 12 months ago
  6. 11 months ago
  7. 11 months ago
  8. 11 months ago
  9. 8 months ago
  10. 5 months ago
  11. 5 months ago
  12. 5 months ago
  13. 4 months ago

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>