scalar subquery expressions 标量子查询

Scalar Subquery Expressions

A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL. If the subquery returns more than one row, then Oracle returns an error.

You can use a scalar subquery expression in most syntax that calls for an expression (expr). However, scalar subqueries 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 CASE expressions

• In GROUP BY and HAVING clauses

• In START WITH and CONNECT BY clauses

• In statements that are unrelated to queries, such as CREATE PROFILE

• 翻译

• 标量子查询表达式是一个子查询，它从一行中仅返回一列值。标量子查询表达式的值是子查询的选择列表项的值。如果子查询返回0行，则标量子查询表达式的值为NULL。如果子查询返回多行，则Oracle返回错误。

  您可以在大多数需要表达式（expr）的语法中使用标量子查询表达式。但是，标量子查询在以下位置不是有效的表达式：

  • 作为列的默认值

  • 作为集群的哈希表达式

  • 在RETURNINGDML语句的子句中

  • 作为基于功能的索引的基础

  • 在CHECK约束中

  • 在表达WHEN条件下CASE

  • 在GROUP BY和HAVING子句中

  • 在START WITH和CONNECT BY子句中

  • 在与查询无关的语句中，例如 CREATE PROFILE

  • ocp考题

  • Which three are true about scalar subquery expressions?

    A.A scalar subquery expression that returns zero rows evaluates to zoro

    B.They cannot be used in the values clause of an insert statement*

    C.They can be nested.

    D.A scalar subquery expression that returns zero rows evaluates to null.

    E.They cannot be used in group by clauses.

    F.They can be used as default values for columns in a create table statement.

    Answer:B D E