Search notes:

Oracle: Scalar Subquery

A scalar subquery is a subquery that returns zero or one row with exactly one column.
With this property, scalar subqueries can be used in SQL statements where an expression is expected.
If the subquery would return more than one row, Oracle throws the error message ORA-01427: single-row subquery returns more than one row. 
select
   object_name,
   created,
  (select count(*) from user_objects obj_inner where trunc(obj_inner.created) = trunc(obj_outer.created)) cnt_created_same_day
from
   user_objects obj_outer;

See also

The no_unnest hint.
SQL plan for scalar subqueries (also for the CREATE TABLE STATEMENT operator).

Index