|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-02
A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent
SELECT statement. A subquery in the
FROM clause of a
SELECT statement is also called an inline view. A subquery in the
WHERE clause of a
SELECT statement is also called a nested subquery.
A subquery can contain another subquery. Oracle imposes no limit on the number of subquery levels in the
FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the
If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.
Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a
A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.
Use subqueries for the following purposes:
You do this by placing the subquery in the
FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in
Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Outer references ("left-correlated subqueries") are allowed only in the
FROM clause of a
Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where
expr is called for in syntax.