Rosihan Ari's Blog

Penggunaan Subquery dalam Query SQL (Bag. 1)

July 14th, 2008 | by rosihanari |  Cetak Artikel

Anda barangkali hanya sering menggunakan statement SQL SELECT secara tunggal, maksudnya adalah statement SQL SELECT yang Anda gunakan hanyalah berbentuk SELECT … FROM … [WHERE ...].

Perlu Anda tahu bahwa, di dalam statement SQL bisa terdapat subquery lagi atau dengan kata lain ‘query dalam query’ atau disebut juga ‘nested query’. Hal ini tidak hanya terjadi pada statement SELECT, namun dapat pula terjadi pada statement UPDATE dan DELETE.

Seperti apa bentuknya serta contohnya? Perhatikan contoh-contoh kasus berikut ini.

Untuk studi kasus, lagi-lagi kita ambil sampel yang terkait dengan nilai matakuliah mahasiswa, karena merupakan sampel yang paling mudah dalam pemahamannya. Berikut ini adalah struktur tabel yang digunakan beserta recordnya.

CREATE TABLE mhs (
nim     varchar(5),
namaMhs varchar(30),
PRIMARY KEY(nim)
);

INSERT INTO mhs VALUES
('001', 'Joko'),
('002', 'Amir'),
('003', 'Budi');

CREATE TABLE mk (
kodeMK    varchar(5),
namaMK    varchar(20),
sks       int(11),
PRIMARY KEY(kodeMK)
);

INSERT INTO mk VALUES
('A01', 'Kalkulus', 3),
('A02', 'Geometri', 2),
('A03', 'Aljabar', 3);

CREATE TABLE ambilmk (
nim    varchar(5),
kodeMK varchar(5),
nilai  int(11),
PRIMARY KEY(nim, kodeMK)
);

INSERT INTO ambilmk VALUES
('001', 'A01', 3),
('001', 'A02', 4),
('001', 'A03', 2),
('002', 'A02', 3),
('002', 'A03', 2),
('003', 'A01', 4),
('003', 'A03', 3);

Nah selanjutnya misalkan ada pertanyaan-pertanyaan sebagai berikut ini:

1. Tampilkan nama mahasiswa dan nilai matakuliah yang memiliki nilai tertinggi dalam matakuliah ‘A02′.
2. Dalam perkuliahan dengan kode ‘A03′, siapakah mahasiswa (nim dan nama) yang memiliki nilai di atas rata-rata nilai dari semua mahasiswa yang mengambil matakuliah tersebut?
3. Dari data mahasiswa yang terdaftar, siapa sajakah (nama) mahasiswa yang tidak mengambil matakuliah ‘A01′?
4. Hapuslah data mahasiswa (dari tabel mhs) yang memiliki IPK terendah (kasus di DO).
5. Tampilkan mahasiswa yang memiliki IPK di bawah 3.5.
6. Untuk semua mahasiswa yang mengambil matakuliah ‘A03′, nilai matakuliah tersebut dinaikkan 1, karena ada kesalahan perhitungan nilai oleh dosennya

Wah cukup lumayan banyak juga ya pertanyaan-pertanyaan yang saya buat. OK untuk efisiensi, jawaban 6 pertanyaan di atas dibagi dalam 2 artikel. Untuk artikel pertama ini, hanya akan dibahas untuk pertanyaan 1 s/d 3 dulu.

OK akan kita jawab dulu pertanyaan No. 1.

Untuk pertanyaan ini, mungkin Anda mengira query atau statement SQL nya berbentuk seperti ini:

SELECT mhs.namaMhs, ambilmk.nilai
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A02' AND
      ambilmk.nilai = MAX(ambilmk.nilai);

Apabila query di atas dijalankan, maka akan terjadi error. Padahal secara logika sudah benar kan? Ternyata salahnya karena penggunaan aggregate function (dalam hal ini MAX) tidak boleh diletakkan dalam WHERE. Lantas di mana donk? Aggregate function hanya boleh diletakkan di bagian SELECT dan HAVING. Sehingga query yang benar adalah sebagai berikut:

SELECT mhs.namaMhs, ambilmk.nilai
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A02' AND
      ambilmk.nilai = (SELECT MAX(nilai)
                       FROM ambilmk
                       WHERE kodeMK = 'A02');

Perhatikan perintah di atas, terutama pada bagian SELECT MAX(nilai) FROM ambilmk WHERE kodeMK = ‘A02′. Bagian ini disebut dengan subquery. Perintah tersebut digunakan untuk mencari nilai tertinggi untuk matakuliah ‘A02′. Hasil dari subquery ini nantinya digunakan sebagai syarat untuk query yang berada di level atasnya (parent query).

Pertanyaan saya, haruskah ada perintah ambilmk.kodeMK = ‘A02′ dalam WHERE pada query di atas, toh kan sudah ada perintah kodeMK = ‘A02′ pada subquery? Bagaimana jika dihapus? Apa yang akan terjadi? Silakan Anda renungkan sendiri ya…

Selanjutnya kita bahas pertanyaan No. 2

Nah untuk pertanyaan ini, Anda mungkin juga mengira querynya berbentuk seperti ini

SELECT mhs.nim, mhs.namaMhs
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A03' AND
      ambilmk.nilai > AVG(ambilmk.nilai);

Apabila Anda jalankan, maka akan terjadi error. Penyebabnya sama dengan error yang terjadi pada soal No. 1, yaitu adanya aggregate function (dalam hal ini AVG()) dalam WHERE.

Statement SQL yang benar adalah:

SELECT mhs.nim, mhs.namaMhs
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A03' AND
      ambilmk.nilai > (SELECT AVG(nilai)
                       FROM ambilmk
                       WHERE kodeMK = 'A03');

Sudah mulai pahamkah Anda tentang subquery? Saya harapkan Anda sudah mulai paham, meskipun sedikit demi sedikit

OK… kita lanjutkan ke pertanyaan No. 3.

Secara logika, untuk menjawab pertanyaan ini adalah kita buat 2 buah query, yaitu query pertama untuk menampilkan semua mahasiswa yang terdaftar di database dan query kedua untuk menampilkan mahasiswa yang mengambil matakuliah ‘A01′. Selanjutnya hasil query pertama dibandingkan dengan hasil query kedua. Dan sebagai outputnya atau yang ditampilkan adalah mahasiswa yang ada di hasil query pertama namun tidak ada di hasil query kedua. Nah… bagaimana untuk mengimplementasikan logika ini di SQL? ini dia perintahnya

SELECT nim, namaMhs
FROM mhs
WHERE nim NOT IN
      (SELECT nim FROM ambilmk WHERE kodeMK = 'A01');

Maksud dari klausa WHERE nim NOT IN (SELECT nim FROM ambilmk WHERE kodeMK = ‘A01′) adalah bahwa syarat yang ditampilkan adalah nim yang ada di tabel mhs namun tidak terdapat (NOT IN) di hasil subquery SELECT nim FROM ambilmk WHERE kodeMK = ‘A01′ (nim yang mengambil ‘A01′). Sederhana bukan?

Baca Juga Artikel Terkait

Perintah SQL (SELECT) untuk Multi Tabel (Bag. 2)

Perintah SQL (SELECT) untuk Multi Tabel (Bag. 1)

Menyisipkan Elemen ke dalam Array di PHP

Mencari Jumlah Hari dalam Bulan Tertentu

Kata kunci: