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 builtin 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 functionbased index

In
CHECK
constraints 
In
GROUP
BY
clauses 
In statements that are unrelated to queries, such as
CREATE
PROFILE