The expansion of subquery specifies a set of values to be used in the evaluation of a predicate or the SET clause of an UPDATE statement.
Expansion of subquery
►►─── query-specification ────────────────────────────────────────────────────►◄
Specifies the query specification that comprises the subquery. For expanded query-specification syntax, see Expansion of Query-specification.
Restriction on DISTINCT
You can specify DISTINCT only once in a subquery (not counting occurrences in nested subqueries). For example, if the value expression that identifies the result column includes an aggregate function with the keyword DISTINCT, you cannot specify DISTINCT either before the value expression or with any other aggregate function.
Column References in the WHERE parameter
Each column that the query specification of a subquery references must identify a column of a table, view, procedure or table procedure named in the FROM clause of the query specification or be an outer reference.
A Subquery Without Correlation in an IN Predicate
The following SELECT statement returns the name and department identifier of each employee who has more than 80 hours of outstanding vacation time. The set of values returned by the subquery consists of the identifiers of all employees with more than 80 hours of outstanding vacation time.
select emp_fname, emp_lname, dept_id
from employee
where emp_id in
(select emp_id
from benefits
group by emp_id
having sum(vac_accrued) - sum(vac_taken) > 80);
A Correlated Subquery in a Comparison Predicate
The following SELECT statement identifies employees who earn more than their managers. The subquery is evaluated once for each value in the EMP_ID column of the EMPLOYEE table named in the outer SELECT statement.
select e1.emp_id
from employee e1, position p1
where e1.emp_id = p1.emp_id
and p1.salary_amount >
(select p2.salary_amount
from employee e2, position p2
where e1.manager_id = e2.emp_id
and e2.emp_id = p2.emp_id);
|
Copyright © 2014 CA.
All rights reserved.
|
|