Scalar Sub Query

 Scalar Subquery

A subquery in the SELECT clause of the main query is called a scalar subquery.
This is a single row, single column query, which looks just like a column or function in the SELECT clause. The structure is:

select col1
     , ...
     , (scalar subquery 1) vcol1
     , (scalar subquery 2) vcol2
     , ...
from table; 

 Scalar Subquery Characteristics

•  A scalar subquery returns exactly one row as output.
• If the scalar subquery finds no match, it returns NULL.
• If the scalar subquery finds more than one match, it returns an error.

 Scalar Subquery Application: Replacing an OUTER JOIN

A query that lists employee names and department names, using the outer join (used so that employee name is included even if the department is unspecified).

SQL> select e.ename
  2       , d.dname
  3  from emp e
  4     , dept d
  5  where e.deptno = d.deptno (+);
 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
 
14 rows selected.

The same query re-written using the scalar subquery:

SQL> select e.ename
  2      , (select d.dname
  3         from   dept d
  4         where  d.deptno = e.deptno) dname
  5  from  emp e;
 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
 
14 rows selected.

Be cautious before you rewrite your outer joins as scalar subqueries though – they may not be more efficient.
The next section highlights cases where a scalar subquery should be your choice of construct.

Where to use scalar subqueries

 It is recommended to use scalar subqueries in the following scenarios:

1. When your data set is small

A query that gets data from lookup tables, which are typically small, is a good fit for scalar subqueries.

1. When you want fast initial response time

When queries are paginated, you get the data in small chunks even if the query returns a huge volume of data in total. (e.g. 1-25 of 2017 rows the first time, 26-50 the next, and so on.)
In the above scenario, a scalar subquery is a good idea.

3. When you call PL/SQL from SQL

Instead of:

select f(x)
from t
where g(y) = ?;

use scalar subqueries:

select (select f(x) from dual)
from t
where (select g(y) from dual) = ?;