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). In all cases, a scalar subquery must be enclosed in its own parentheses, even if its syntactic location already positions it within parentheses (for example, when the scalar subquery is used as the argument to a built-in function).

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 GROUP BY clauses

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

关于scalar subquery expressions的ocp考题：

14、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

20、Which two are true about scalar subquery expressions?

A.You cannot correlate them with a table in the parent statement

B.You can use them as a default value for a column.

C..You must enclose them in parentheses.

D.They can return at most one row.

E.They can return two columns.

Answer: C D