Subqueries are a form of a SELECT statement that is nested inside another SQL statement. The rows returned by the subquery are used by the main, or the parent, statement. Oracle has no limitations to the level of subqueries you perform.
select a.institution, a.facility_id, a.effdt_fac
from ps_dwsr_facility a
where a.institution = 'UMNDL'
and a.effdt_fac = (select max (b.effdt_fac)
from ps_dwsr_facility b
where a.institution = b.institution)
INSTI FACILITY_I EFFDT_FAC
----- ---------- ---------
UMNDL DECC000001 07-SEP-99
The above is an example of a correlated subquery. A correlated subquery is where the subquery is processed for each row in the parent statement. In this statement, it is processing a correlated subquery because you are referencing that the institution must equal the institution in both the parent and the subquery. Oracle does a full table scan by reading a row in the parent, then the subquery, and so on.
Example :from DWSYSADM.ps_dwhr_j_emp_job
select emplid, name, annl_benef_base_rt
where jobcode ='9401'and deptid ='627A' and status_flg = 'C' and
annl_benef_base_rt > (select avg(annl_benef_base_rt)
where jobcode = '9401' and
deptid = '627A' and
status_flg = 'C')
© 2018 by IMS and the Regents of the University of Minnesota