子查询的知识点

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm#sthref2680

 

Scalar Subquery Expressions

A scalar subquery expression is a subquery that returns exactlyone column value from one row. The value of the scalar subqueryexpression is the value of the select list item of thesubquery.

 

If the subquery returns 0 rows, then the value of thescalar subquery expression is NULL.--如果子查询返回0行，那么标量子查询的表达式的值为空。

 

补充：如果子查询返回0行，那么这个子查询的表达式的值为空。

如果在子查询中不是单列的（即标量的子查询）的子查询中，子查询返回0行，那么会在多列子查询中返回的是多列空值。如：

Select * From
test te Where (b,a)
=(select b,a from test where a='dd')

和下列等同

Select * From
test te Where (b,a)=((Null,Null))

 

If the subquery returns more than one row, then Oracle returnsan error.

You can use a scalar subquery expression in most syntax thatcalls for an expression_r(expr). However, scalarsubqueries are not valid expressions in the following places:

• As default values for columns

• As hash expressions for clusters

• In the RETURNING clause of DML statements

• As the basis of a function-based index

• In CHECK constraints

• In WHEN conditions of CASEexpressions

• In GROUP BY and HAVINGclauses

• In START WITH and CONNECTBY clauses

• In statements that are unrelated to queries, such asCREATE PROFILE

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries007.htm#sthref3193

 

Using Subqueries

A subquery answers multiple-part questions. Forexample, to determine who works in Taylor's department, you canfirst use a subquery to determine the department in which Taylorworks. You can then answer the original question with the parentSELECT statement. A subquery in the FROMclause of a SELECT statement is also called aninline view. A subquery in the WHEREclause of a SELECT statement is also called anested subquery.

 

A subquery can contain another subquery. Oracle Databaseimposes no limit on the number of subquery levels in theFROM clause of the top-level query.

--Oracle在FROM 字句的顶层查询中没有施加子查询层数的数量的限制。

 

 You can nest up to 255 levels ofsubqueries in the WHERE clause.

--你能够在WHERE字句中嵌套255层子查询

 

 

If columns in a subquery have the same name as columns in thecontaining statement, then you must prefix any reference to thecolumn of the table from the containing statement with the tablename or alias. To make your statements easier to read, alwaysqualify the columns in a subquery with the name or alias of thetable, view, or materialized view.

Oracle performs a correlated subquery when a nestedsubquery references a column from a table referred to a parentstatement any number of levels above the subquery. The parentstatement can be a SELECT, UPDATE, orDELETE statement in which the subquery is nested. Acorrelated subquery is evaluated once for each row processed by theparent statement. Oracle resolves unqualified columns in thesubquery by looking in the tables named in the subquery and then inthe tables named in the parent statement.

A correlated subquery answers a multiple-part question whoseanswer depends on the value in each row processed by the parentstatement. For example, you can use a correlated subquery todetermine which employees earn more than the average salaries fortheir departments. In this case, the correlated subqueryspecifically computes the average salary for each department.

See Also:

"UsingCorrelated Subqueries: Examples"

Use subqueries for the following purposes:

• To define the set of rows to be inserted into the target tableof an INSERT or CREATE TABLEstatement

• To define the set of rows to be included in a view ormaterialized view in a CREATE VIEW orCREATE MATERIALIZED VIEWstatement

• To define one or more values to be assigned to existing rows inan UPDATE statement

• To provide values for conditions in a WHERE clause,HAVING clause, or START WITHclause of SELECT, UPDATE, andDELETE statements

• To define a table to be operated on by a containing query

  You do this by placing the subquery in the FROMclause of the containing query as you would a table name. You mayuse subqueries in place of tables in this way as well inINSERT, UPDATE, and DELETEstatements.

  Subqueries so used can employ correlation variables, but onlythose defined within the subquery itself, not outer references.Please refer to table_collection_expression for moreinformation.

  Scalar subqueries, which return a single column value from asingle row, are a valid form of expression. You can use scalarsubquery expressions in most of the places where expr is called for insyntax. Please refer to "Scalar Subquery Expressions"for more information.