Single-Row Subqueries

The main query expects the sub query to return only one value.
Check out the following example, which should look familiar:
SQL>  select ename, deptno, sal
  2   from emp
  3   where deptno =
  4    ( select deptno
  5      from dept
  6      where loc = ‘NEW YORK’ );
ENAME         DEPTNO       SAL
———-         ———         ———
CLARK             10            2450
KING               10            5000
MILLER            10            1300
Though the above query results have 3 rows it is a single-row subquery Because, the subquery on the DEPT table to derive the output from EMP returns only one row of data.