子查询

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, both defined within the subquery itself and those defined in query blocks containing the subquery. 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.