Pemanfaatan Subquery Untuk Menyelesaikan Query

Subquery merupakan “query didalam query” atau perintah select yang berada didalam perintah select lainnya. Subquery terdiri dari dua perintah select. Perintah select pertama disebut Outer Query atau Query induk sedangkan query yang berada pada klausa where disebut inner query. Inner query akan dikerjakan terlebih dahulu dan hasilnya akan digunakan sebagai pembnding pada pencarian data di klausa where outer query. Subquery dapat digunakan untuk menghasilkan nilai-nilai bagi outer query pada saat kriteria pencarian yang dibutuhkan tidak diketahui.


Bentuk umum subquery:

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table) ;

Jenis Subquery

• Single-row subquery
Merupakan subquery yang hanya menghasilkan satu baris dan satu kolom data atau data tanggal. Pada subquery juga bisa menggunakan group function seperti AVG, MAX dan MIN.

SELECT last_name, job_id, salary ST_CLERK
FROM employees
WHERE job_id = (SELECT job_id FROM employees
WHERE employee_id = 141)
AND salary < 2600 (SELECT salary FROM employees WHERE employee_id = 143) ; SELECT last_name, salary, deparment_id FROM employees WHERE deparment_id < (Select department_id FROM employees WHERE last_name = ‘Landry’) OR salary > (SELECT salary
FROM employees
WHERE last_name = ‘De Haan’);
ORDER by salary;


Penggunaan fungsi group dalam subquery
Fungsi group seperti AVG, MAX, MIN

SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN (salary)
FROM employees);


SELECT last_name, job_id, salary
FROM employees
WHERE salary < (SELECT AVG (salary) FROM employees); Pernyataan having dengan subquery SELECT department_id, MIN (salary) FROM employees GROUP BY department_id HAVING MIN(salary) >
(SELECT MIN (salary)
FROM employees
WHERE department_id =50);


SELECT department_id, AVG (salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) < 2500 (SELECT AVG (salary) FROM employees WHERE last_name = ‘Abel’); • Multy-row subquery Menghasilkan lebih dari satu row. Tanda pembanding yang digunakan dalam multi row subquery adalah tanda pembanding jamak yaitu IN, ANY dan ALL. SELECT employee_id, last_name FROM employees WHERE salary IN (SELECT MIN (salary) FROM employees GROUP BY department_id); SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY 9000,6000,4800,4200 (SELECT salary FROM employees WHERE job_id = ‘IT_PROG’) AND job_id <> ‘IT_PROG’;



SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL 9000,6000,4800,4200 (SELECT salary FROM employees WHERE job_id = ‘IT_PROG’) AND job_id <> ‘IT_PROG’;


Nilai NULL dalam subquery

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);



SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);


Dengan menggunakan klausa WHERE


SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr
WHERE manager_id IS NOT NULL);



Download Here