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.
The expansion of subquery specifies a set of values to be used in the evaluation of a predicate or an
Expansion of subquery
- query-specificationSpecifies the query specification that comprises the subquery. For expandedquery-specificationsyntax, 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);