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:

1 2 3 4 5 6

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).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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:

1 2 3

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

use scalar subqueries:

1 2 3

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

Reference: AskTom on Scalar Subqueries