Subkueri untuk Memecahkan Kueri

Subkueri paling baik didefinisikan sebagai kueri dalam kueri. Subkueri memungkinkan Anda untuk menulis kueri yang memilih baris data untuk kriteria yang sebenarnya dikembangkan saat kueri dijalankan pada waktu proses. Secara lebih formal, ini adalah penggunaan pernyataan SELECT di dalam salah satu klausa pernyataan SELECT lainnya. Faktanya, sebuah subquery bisa ditampung di dalam subquery lain, yang berada di dalam subquery lain, dan seterusnya. Sebuah subkueri juga bisa bertumpuk di dalam pernyataan INSERT, UPDATE, dan DELETE. Subkueri harus diapit dalam tanda kurung.

Subkueri dapat digunakan di mana saja di mana ekspresi diperbolehkan asalkan mengembalikan satu nilai. Ini berarti bahwa subkueri yang mengembalikan nilai tunggal juga dapat dicantumkan sebagai objek dalam daftar klausa FROM. Ini disebut tampilan sebaris karena ketika subkueri digunakan sebagai bagian dari klausa FROM, itu diperlakukan seperti tabel atau tampilan virtual. Subquery dapat ditempatkan baik dalam klausa FROM, klausa WHERE, atau klausa HAVING dari kueri utama.

Oracle memungkinkan maksimum 255 level subquery dalam klausa WHERE. Tidak ada batasan untuk subkueri bertingkat yang diekspresikan dalam klausa FROM. Dalam praktiknya, batas 255 level sebenarnya bukan batasan sama sekali karena jarang menemukan subkueri yang bertumpuk di atas tiga atau empat level.

Pernyataan SELECT subkueri sangat mirip dengan pernyataan SELECT yang digunakan untuk memulai kueri reguler atau kueri luar. Sintaks lengkap dari subkueri adalah:

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

Jenis Subkueri

Single Row Sub Query: Sub query yang mengembalikan keluaran baris tunggal. Mereka menandai penggunaan operator perbandingan baris tunggal, bila digunakan dalam kondisi WHERE.

Multiple row sub query: Sub query menghasilkan keluaran beberapa baris. Mereka menggunakan operator perbandingan beberapa baris seperti IN, ANY, ALL. Mungkin ada sub-kueri yang mengembalikan beberapa kolom juga.

Correlated Sub Query: Subkueri terkait bergantung pada data yang disediakan oleh kueri luar. Tipe subkueri ini juga menyertakan subkueri yang menggunakan operator EXISTS untuk menguji keberadaan baris data yang memenuhi kriteria yang ditentukan.

Sub Query Baris Tunggal

Subkueri baris tunggal digunakan saat hasil kueri luar didasarkan pada satu nilai yang tidak diketahui. Meskipun jenis kueri ini secara formal disebut "baris tunggal", namanya menyiratkan bahwa kueri mengembalikan beberapa kolom - tetapi hanya satu baris hasil. Namun, subkueri baris tunggal hanya bisa mengembalikan satu baris hasil yang terdiri dari hanya satu kolom ke kueri luar.

Dalam query SELECT di bawah ini, inner SQL hanya mengembalikan satu baris yaitu gaji minimum untuk perusahaan. Ini pada gilirannya menggunakan nilai ini untuk membandingkan gaji semua karyawan dan hanya menampilkan mereka, yang gajinya sama dengan gaji minimum.

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary) 
		FROM employees);

Klausa HAVING digunakan ketika hasil grup dari kueri perlu dibatasi berdasarkan beberapa kondisi. Jika hasil subkueri harus dibandingkan dengan fungsi grup, Anda harus menumpuk kueri dalam di klausa HAVING kueri luar.

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary) FROM employees)

Beberapa Baris Sub Query

Subkueri beberapa baris adalah kueri bertingkat yang bisa mengembalikan lebih dari satu baris hasil ke kueri induk. Subkueri beberapa baris paling sering digunakan dalam klausa WHERE dan HAVING. Karena mengembalikan beberapa baris, itu harus ditangani oleh operator perbandingan set (IN, ALL, ANY). Sementara operator IN memiliki arti yang sama seperti yang dibahas di bab sebelumnya, operator ANY membandingkan nilai yang ditentukan untuk setiap nilai yang dikembalikan oleh sub query sementara SEMUA membandingkan nilai untuk setiap nilai yang dikembalikan oleh sub kueri.

Kueri di bawah ini menunjukkan kesalahan ketika satu baris sub kueri mengembalikan beberapa baris.

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Penggunaan operator Beberapa Baris

[> SEMUA] Lebih dari nilai tertinggi yang dikembalikan oleh subkueri
[<ALL] Kurang dari nilai terendah yang dikembalikan oleh subkueri
[<ANY] Kurang dari nilai tertinggi yang dikembalikan oleh subkueri
[> ANY] Lebih dari nilai terendah yang dikembalikan oleh subkueri
[= ANY] Sama dengan nilai apa pun yang dikembalikan oleh subkueri (sama seperti IN)

SQL di atas dapat ditulis ulang menggunakan operator IN seperti di bawah ini.

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

Catatan dalam kueri di atas, IN mencocokkan id departemen yang dikembalikan dari sub kueri, membandingkannya dengan yang ada di kueri utama dan mengembalikan nama karyawan yang memenuhi kondisi tersebut.

Gabungan akan menjadi solusi yang lebih baik untuk kueri di atas, tetapi untuk tujuan ilustrasi, sub kueri telah digunakan di dalamnya.

Sub Query Terkait

Berbeda dengan subkueri biasa, di mana kueri luar bergantung pada nilai yang disediakan oleh kueri dalam, subkueri yang berkorelasi adalah kueri dalam yang bergantung pada nilai yang disediakan oleh kueri luar. Ini berarti bahwa dalam subkueri yang berkorelasi, kueri dalam dijalankan berulang kali, sekali untuk setiap baris yang mungkin dipilih oleh kueri luar.

Subkueri terkait dapat menghasilkan tabel hasil yang menjawab pertanyaan manajemen yang kompleks.

Pertimbangkan kueri SELECT di bawah ini. Tidak seperti subkueri yang sebelumnya dianggap, subkueri dalam pernyataan SELECT ini tidak dapat diselesaikan secara independen dari kueri utama. Perhatikan bahwa kueri luar menentukan bahwa baris dipilih dari tabel karyawan dengan nama alias e1. Kueri dalam membandingkan kolom nomor departemen karyawan (DepartmentNumber) dari tabel karyawan dengan alias e2 ke kolom yang sama untuk nama tabel alias e1.

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

Beberapa Kolom Sub Query

Subkueri multi-kolom mengembalikan lebih dari satu kolom ke kueri luar dan bisa dicantumkan di klausa FROM, WHERE, atau HAVING kueri luar. Misalnya, kueri di bawah ini menunjukkan rincian riwayat karyawan untuk karyawan yang gajinya saat ini berkisar antara 1000 dan 2000 dan bekerja di departemen 10 atau 20.

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000 
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

Saat subkueri multi-kolom digunakan di klausa FROM kueri luar, itu membuat tabel sementara yang bisa direferensikan oleh klausa lain dari kueri luar. Tabel sementara ini secara lebih formal disebut tampilan sebaris. Hasil subkueri diperlakukan seperti tabel lain di klausa FROM. Jika tabel sementara berisi data yang dikelompokkan, subset yang dikelompokkan akan diperlakukan sebagai baris data terpisah dalam tabel. Pertimbangkan klausa FROM dalam kueri di bawah ini. Tampilan sebaris yang dibentuk oleh subkueri adalah sumber data untuk kueri utama.

SELECT * 
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);