Subquery

Resume6

STIKOM SURABAYA

Menggunakan Subquery untuk Memecahkan QueryQuery

• Menggunakan suatu Subquery untuk Memecahkan suatu Persoalan

Misalkan ingin menulis suatu query untuk mencari tahu penghasilan siapa yang lebih besar daripada penghasilan Abel. Untuk memecahkan masalah ini, memerlukan dua query: satu query untuk mencari berapa banyak penghasilan Abel, dan query kedua untuk mencari penghasilan siapa yang lebih besar dari jumlah itu. Inner query (atau subquery) mengembalikan suatu nilai yang digunakan outer query (atau query utama). Penggunaan suatu subquery sama dengan penggunaan dua query berturu-tturut dan menggunakan hasil dari query pertama sebagai nilai pencari dalam query yang kedua.

Sintak Subquery

Suatu subquery adalah suatu pernyataan SELECT yang dilekatkan didalam suatu klausa pada pernyataan SELECT lain.. Subquery-subquery bisa sangat bermanfaat ketika Anda memerlukan untuk memilih baris-baris dari suatu table dengan suatu kondisi yang tergantung pada data didalam tabel itu sendiri.

Dapat menempatkan subquery didalam sejumlah klausa-klausa SQL, termasuk berikut :

 Klausa WHERE

 Klausa HAVING

 Klausa FROM

Didalam Syntax : Operator termasuk suatu kondisi pembanding seperti >, =, atau IN

Catatan : Kondisi-kondisi pembanding dibagi dalam dua kelas : singlerow operator (>,=,>=,<,<>,<=) dan multiplerow operator (IN, ANY, ALL). Subquery lebih dikenal sebagai suatu SELECT bersarang (nested), subSELECT, atau pernyataan inner SELECT. Secara umum subquery dieksekusi pertama kali, dan hasilnya digunakan untuk melengkapi kondisi query pada query utama (atau outer).

• Menggunakan suatu Subquery

Inner query menentukan penghasilan dari pegawai bernama Abel. Outer query mengambil hasil dari inner query dan menggunakan hasil ini untuk menampilkan semua pegawaiyang berpenghasilan lebih dari jumlah tersebut.

• Pedoman-Pedoman untuk Menggunakan Subquery

 Suatu subquery harus diapit tanda kurung.

 Tempatkan subquery disisi kanan dari kondisi pembanding agar mudah dibaca.

 Pada Oracle8i dan keluarankeluaran berikutnya,

suatu klausa ORDER BY bisa digunakan dan diperlukan dalam subquery untuk melakukan pemeringkatan (TopNanalysis).

 Dua kelas dari kondis-ikondisi pembanding digunakan dalam subquerysubquery: singlerow operator dan multiplerow operator.

 Singlerow subquery: Query yang mengembalikan hanya satu baris dari pernyataan inner SELECT (SELECT terdalam).

 Multiplerow subquery: Query yang mengembalikan lebih dari satu baris dari pernyataan inner SELECT.

Catatan : Ada juga multiplecolumn subquery, dimana queryqueryitu mengembalikan lebih darisatu kolom dari pernyataan inner SELECT. Hal tersebut dicakup dalam Oracle Database 10g: SQL Fundamentals II Course.

Suatu singlerow subquery adalah mengembalikan satu baris dari pernyataan inner SELECT. Tipe

dari subquery ini menggunakan suatu singlerow operator. Pada slide diberikan suatu daftar darioperator-operator singlerow.Contoh Tampilkan pegawai-pegawaiyang job IDnyasama dengan pegawai 141:

SELECT last_name, job_id

FROM employees

WHERE job_id =

(SELECT job_id

FROM employees

WHERE employee_id = 141 );

• Mengeksekusi SingleRow Subqueries

Pada contoh terdapat tiga blok query: outer query dan dua inner query. Blok inner query dieksekusi pertama kali, secara berturut-turut menghasilkan hasil query ST_CLERK dan 2600. Blok outer query kemudian memproses dan menggunakan nilai-nilai yang dikembalikan oleh inner queries untuk melengkapi kondisikondisi

pencariannya. Dua inner query mengembalikan nilai-nilai tunggal ( secara berturutturut ST_CLERK dan 2600), jadi pernyataan SQL ini disebut suatu singlerow subquery.

Catatan : Outer query dan inner query bisa mengambil data dari table-tabel yang berbeda.

• Menggunakan Grup Function dalam suatu Subquery

Menampilkan data dari suatu query utama dengan menggunakan grup function didalam subquery untuk mengembalikan suatu baris tunggal. Subquery berada dalam tanda kurung dan diletakkan setelah kondisi pembanding. Contoh pada slide menampilkan nama belakang pegawai, job ID, dan penghasilan seluruh pegawai

yang penghasilannya sama dengan penghasilan minimum. Group function MIN mengembalikan suatu

nilai tunggal (2500) untuk outer query.

• Klausa HAVING dengan Subquery

Menggunakan subquery-subquery tidak hanya pada klausa WHERE tapi bisa juga padaklausa HAVING. Server Oracle mengeksekusi subquery, dan hasilnya dikembalikan ke dalam klausa HAVING pada query utama.Pernyataan SQL dalam slide menampilkan semua departemen yang mempunyai penghasilanminimum lebih besar dari departemen 50.Contoh :Cari jabatan dengan penghasilan rata-rata minimum.

SELECT job_id, AVG(salary)

FROM employees

GROUP BY job_id

HAVING AVG(salary) = (SELECT MIN(AVG(salary))

FROM employees

GROUP BY job_id);

• Kesalahan-Kesalahan pada SubquerySubquery

Pada pernyataan SQL dalam slide, subquery berisi suatu klausa GROUP BY, yang berakibat

subquery itu akan mengembalikan banyak baris, satu dari setiap kelompok yang ditemukannya.

Dalam kasus ini, hasil dari subquery adalah 4400, 6000, 2500, 4200, 7000, 17000, dan 8300.

Outer query mengambil hasil-hasil itu dan menggunakannya pada klausa WHERE. Klausa WHERE

berisi suatu operator samadengan ( = ), suatu operator pembanding singlerow yang dikira hanya

satu nilai. Operator = tidak menerima lebih dari satu nilai dari subquery dan oleh karena itu

menghasilkan kesalahan. Untuk memperbaiki kesalahan ini, rubah operator = menjadi IN.

• Persoalan-Persoalan pada SubquerySubquery

Suatu persoalan umum pada subquery-subquery muncul saat tidak ada baris-baris Yang dikembalikan oleh inner query. Pada pernyataan SQL dalam slide, subquery berisi suatu klausa WHERE. Rupanya, tujuannya adalah untuk mencari pegawai yang bernama Haas. Pernyataannya benar tetapi tidak ada baris yang terpilih ketika dieksekusi. Tidak ada pegawai bernama Haas. Sehingga subquery tidak mengembalikan baris-baris. Outer query

mengambil hasil-hasil dari subquery (null) dan menggunakan hasilhasil tersebut pada klausa WHERE outer query. Outer query tidak mencari pegawai dengan suatu job ID sama dengan null, jadi

tidak mengembalikan baris-baris.

Jika suatu job berada pada suatu nilai null, baris tidak dikembalikan karena perbandingan dua nilai null menghasilkan suatu null. Oleh karena, kondisi WHERE tidak benar.

• MultipleRow Subqueries

Subquery-subquery yang mengembalikan lebih dari satu baris disebut multiplerow subqueries.

Menggunakan suatu multiplerow operator, disamping suatu singlerow operator, pada suatu

Miltiplerow subquery. Multiplerow operator memperkirakan satu atau lebih nilai-nilai:

Contoh

Cari pegawai-pegawai yang mendapat penghasilan yang sama dengan penghasilan minimum untuk

setiap departemen. Inner query dieksekusi pertama kali, menghasilkan suatu hasil query. Blok query utama kemudian memproses dan menggunakan nilainilai yang dikembalikan oleh inner query untuk melengkapi

kondisi pencariannya. Sesungguhnya, query utama tampak pada server Oracle sebagai berikut:

SELECT last_name, salary, department_id

FROM employess

WHERE salary IN (2500, 4200, 6000, 7000, 800, 8600, 17000);

Operator ANY ( dan sinonimnya, operator SOME) membandingkan suatu nilai pada setiap nilai yang

dikembalikan oleh suatu subquery. Contoh pada slide menampilkan para pegawai yang bukan IT

programmers dan penghasilan siapa yang kurang dari beberapa IT programmer. Penghasilan

maksimum yang didapat seorang programmer adalah $ 9,000. <ANY maksudnya kurang dari maksimum. >ANY maksudnya lebih dari minimum. =ANY adalah sama dengan IN.

Operator ALL membandingkan suatu nilai untuk setiap nilai yang dikembalikan oleh suatu subquery. Operator NOT dapat digunakan dengan operatoroperator IN, ANY, dan ALL.

• Mengembalikan Null-Null dalam Menghasilkan Kumpulan dari suatu Subquery

Pernyataan SQL dalam slide mencoba untuk menampilkan semua pegawai yang tidak memiliki

beberapa subordinate. Secara logika, pernyataan SQL ini akan dikembalikan 12 baris. Bagaimanapun, pernyataan SQL tidak mengembalikan beberapa baris pun. Satu dari nilainilai dikembalikan oleh inner- query adalah nilai null, dan dan karenanya keseluruhan query tidak mengembalikan baris-baris.

Jadi kapanpun nilai-nilai null secara kemungkinan menjadi bagian dari sekelompok hasil-hasil

dari suatu subquery, jangan gunakan operator NOT IN. Operator NOT IN sama dengan operatorm <>ALL.

Sebagai catatan bahwa nilai null sebagai bagian dari sekelompok hasil-hasil dari suatu subquery adalah tidak menjadi masalah jika. Menggunakan operator IN. Operator IN adalah sama dengan operator =ANY. Sebagai contoh, untuk menampilkan para pegawai yang memiliki subordinate, gunakan pernyataan SQL berikut :

SELECT emp.last_name

FROM employees emp

WHERE emp.employees_id IN

(SELECT mgr.manager_id

FROM employees mgr);

Sebagai alternatif, suatu klausa WHERE dapat disertakan pada subquery untuk menampilkan seluruh pegawai yang tidak memiliki beberapa subordinate :

SELECT last_name FROM employees

WHERE employee_id NOT IN

(SELECT manager_id

FROM employees

WHERE manager_id IS NOT NULL)

Sumber :copyright@2004, oracle.