Expansion of Subquery

The expansion of subquery specifies a set of values to be used in the evaluation of a predicate or an XML-value-expression.
idmscu
The expansion of subquery specifies a set of values to be used in the evaluation of a predicate or an
XML-value-expression
.
Syntax
Expansion of subquery
►►─── 
query-specification
 ────────────────────────────────────────────────────►◄
Parameters
  • query-specification
    Specifies the query specification that comprises the subquery. For expanded
    query-specification
    syntax, see Expansion of Query-specification.
Usage
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.
Examples
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);
More Information