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.
2. 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) = ?;
On the face of it, using a scalar subquery here looks like a circuitous way of doing a simple thing. Actually, it offers a performance advantage – Oracle can cache the results of a scalar subquery and reuse the value. More on scalar subquery caching here: Efficient function calls from SQL.
Reference: AskTom
