Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
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. you can nest any number of subqueries in an inline view. A subquery in the WHERE
clause of a SELECT
statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the a nested subquery.
A subquery can contain another subquery. Oracle Database 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 WHERE
clause.
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 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 a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. The parent statement can be a SELECT
, UPDATE
, or DELETE
statement in which the subquery is nested. A correlated subquery is evaluated once for each row processed by the parent statement. Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.
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.
See Also:
"Using Correlated Subqueries: Examples"Use subqueries for the following purposes:
To define the set of rows to be inserted into the target table of an INSERT
or CREATE
TABLE
statement
To define the set of rows to be included in a view or materialized view in a CREATE
VIEW
or CREATE
MATERIALIZED
VIEW
statement
To define one or more values to be assigned to existing rows in an UPDATE
statement
To provide values for conditions in a WHERE
clause, HAVING
clause, or START
WITH
clause of SELECT
, UPDATE
, and DELETE
statements
To define a table to be operated on by a containing query
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 INSERT
, UPDATE
, and DELETE
statements.
Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Refer to table_collection_expression for more information.
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. Refer to "Scalar Subquery Expressions" for more information.