Scalar Subqueries
Overview
Introduced in Oracle9, scalar subqueries allow you to treat the output of a subquery as a column or even an expression within a SELECT statement. It is a query that only selects one column or expression and returns just one row. If the scalar subquery fails to return select any rows, Oracle will use a NULL value for the output of the scalar subquery.
The following is an example of using the scalar subquery to determine how many rows in the DEPT table contain an employee corresponding to each row in the EMP table.
SELECT d.deptno, d.dname, (SELECT count(*) FROM emp e
WHERE e.deptno = d.deptno) AS "Num Dept"
FROM dept d;DEPTNO DNAME Num Dept
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
Where to use Scalar Subqueries
Scalar Subqueries are mainly used for the following tasks:
When you remove an outer join, not only is the resulting query usually easier to read, but many times, the performance can be improved as well.
Consider this Outer Join Query:
select a.username, count(*) Cnt, avg(b.object_id) Avg
from all_users a, all_objects b
where a.username = b.owner(+)
group by a.username
order by a.username;USERNAME CNT AVG ------------------------------ ---------- ---------- ANONYMOUS 1 CTXSYS 338 42790.6657 DBSNMP 46 9673.65217 DIP 1 DMSYS 189 42439.6878 EXFSYS 279 42935.1792 F1 1 G1 1 MDDATA 1 MDSYS 863 49667.7068 MGMT_VIEW 1 NAVSEARCH 36 51610.0278 OLAPSYS 718 49881.61 ORDPLUGINS 10 44487.7 ORDSYS 1720 45540.4012 OUTLN 7 1179.57143 SCOTT 43 53183.1163 SI_INFORMTN_SCHEMA 8 44475.5 SYS 22957 17383.9356 SYSMAN 1291 52369.1534 SYSTEM 426 6553.87324 TSMSYS 2 8687.5 WMSYS 232 10102.1422 XDB 346 43841.8006Replaced by the following Query using two Scalar Subqueries.
select a.username UserName, (select count(*) from all_objects b where b.owner = a.username) Cnt, (select avg(object_id ) from all_objects b where b.owner = a.username) Avg from all_users a order by a.username;USERNAME CNT AVG
------------------------------ ---------- ----------
ANONYMOUS 0
CTXSYS 338 42790.6657
DBSNMP 46 9673.65217
DIP 0
DMSYS 189 42439.6878
EXFSYS 279 42935.1792
F1 0
G1 0
MDDATA 0
MDSYS 863 49667.7068
MGMT_VIEW 0
NAVSEARCH 36 51610.0278
OLAPSYS 718 49881.61
ORDPLUGINS 10 44487.7
ORDSYS 1720 45540.4012
OUTLN 7 1179.57143
SCOTT 43 53183.1163
SI_INFORMTN_SCHEMA 8 44475.5
SYS 22957 17383.9356
SYSMAN 1291 52369.1534
SYSTEM 426 6553.87324
TSMSYS 2 8687.5
WMSYS 232 10102.1422
XDB 346 43841.8006The two Scalar Subqueries can be even replaced with an Object Type
create or replace type ConvertType as object
(
cnt number,
average number
)
/That maps to the two numbers we would like to return: the count and the average. Now, we can get the result using this query:
select username UserName, a.data.cnt Cnt, a.data.average Avg from (select username, (select ConvertType(count(*),avg(object_id)) from all_objects b where b.owner = a.username) data from all_users a) A order by username;USERNAME CNT AVG
------------------------------ ---------- ----------
ANONYMOUS 0
CTXSYS 338 42790.6657
DBSNMP 46 9673.65217
DIP 0
DMSYS 189 42439.6878
EXFSYS 279 42935.1792
F1 0
G1 0
MDDATA 0
MDSYS 863 49667.7068
MGMT_VIEW 0
NAVSEARCH 36 51610.0278
OLAPSYS 718 49881.61
ORDPLUGINS 10 44487.7
ORDSYS 1720 45540.4012
OUTLN 7 1179.57143
SCOTT 43 53183.1163
SI_INFORMTN_SCHEMA 8 44475.5
SYS 22957 17383.9356
SYSMAN 1291 52369.1534
SYSTEM 426 6553.87324
TSMSYS 2 8687.5
WMSYS 232 10102.1422
XDB 346 43841.8006
Aggregate from Multiple Tables
If you want to calculate Aggregates from different Tables, then Scalar Subqueries can be used.
Suppose you are trying to generate a report that shows by username, the username, created date, number of tables they own, and the number of constraints they own for all users.
Solution with Outer Join and Inline View:
select a.username,
a.created,
nvl(b.cons_cnt,0) cons,
nvl(c.tables_cnt,0) tables
from all_users a,
(select all_constraints.owner, count(*) cons_cnt
from all_constraints, all_users
where all_users.username = all_constraints.owner
group by owner) b,
(select all_tables.owner, count(*) tables_cnt
from all_tables, all_users
where all_users.username = all_tables.owner
group by owner) c
where a.username = b.owner(+)
and a.username = c.owner(+)
order by a.username
/
USERNAME CREATED CONS TABLES
------------------------------ --------- ---------- ----------
ANONYMOUS 23-AUG-06 0 0
CTXSYS 23-AUG-06 92 37
DBSNMP 23-AUG-06 88 21
DIP 23-AUG-06 0 0
DMSYS 23-AUG-06 7 2
EXFSYS 23-AUG-06 89 44
F1 23-OCT-06 0 0
G1 23-OCT-06 0 0
MDDATA 23-AUG-06 0 0
MDSYS 23-AUG-06 170 49
MGMT_VIEW 24-OCT-06 0 0
NAVSEARCH 23-OCT-06 12 7
OLAPSYS 23-AUG-06 568 126
ORDPLUGINS 23-AUG-06 0 0
ORDSYS 23-AUG-06 13 4
OUTLN 23-AUG-06 0 3
SCOTT 23-AUG-06 57 7
SI_INFORMTN_SCHEMA 23-AUG-06 0 0
SYS 23-AUG-06 2317 709
SYSMAN 24-OCT-06 1161 337
SYSTEM 23-AUG-06 418 142
TSMSYS 23-AUG-06 2 1
WMSYS 23-AUG-06 105 40
XDB 23-AUG-06 33 11
Elapsed: 00:00:00.51Solution with two Scalar Subqueries:
select username,
created,
(select count(*)
from all_constraints
where owner = username) cons,
(select count(*)
from all_tables
where owner = username) tables
from all_users
order by username
/
USERNAME CREATED CONS TABLES
------------------------------ --------- ---------- ----------
ANONYMOUS 23-AUG-06 0 0
CTXSYS 23-AUG-06 92 37
DBSNMP 23-AUG-06 88 21
DIP 23-AUG-06 0 0
DMSYS 23-AUG-06 7 2
EXFSYS 23-AUG-06 89 44
F1 23-OCT-06 0 0
G1 23-OCT-06 0 0
MDDATA 23-AUG-06 0 0
MDSYS 23-AUG-06 170 49
MGMT_VIEW 24-OCT-06 0 0
NAVSEARCH 23-OCT-06 12 7
OLAPSYS 23-AUG-06 568 126
ORDPLUGINS 23-AUG-06 0 0
ORDSYS 23-AUG-06 13 4
OUTLN 23-AUG-06 0 3
SCOTT 23-AUG-06 57 7
SI_INFORMTN_SCHEMA 23-AUG-06 0 0
SYS 23-AUG-06 2317 709
SYSMAN 24-OCT-06 1161 337
SYSTEM 23-AUG-06 418 142
TSMSYS 23-AUG-06 2 1
WMSYS 23-AUG-06 105 40
XDB 23-AUG-06 33 11
Elapsed: 00:00:02.32As you can see, in this case, the Outer Join / Inline View solution is much faster.
Inserting into Tables, based on Values from other Tables
Scalar subqueries are also handy for inserting into tables, based on values from other tables.
create table summary (
sum_sal number,
max_sal number,
min_sal number,
avg_sal number
);
insert into summary (
sum_sal,
max_sal,
min_sal,
avg_sal)
values (
(select sum(sal) from emp),
(select max(sal) from emp),
(select min(sal) from emp),
(select avg(sal) from emp)
);1 row created.
Conclusion
Scalar subqueries are restricted to returning a single value because they select a finite value. Scalar subqueries can be used in almost any place where an expression can be used, including:
- CASE expressions
- SELECT statements
- VALUES clauses of INSERT statements
- WHERE clauses
- ORDER BY clauses
- Parameters of a function
There are also important restrictions on scalar subqueries. Scalar subqueries can’t be used for:
- Default values for columns
- RETURNING clauses
- Hash expressions for clusters
- Functional index expressions
- CHECK constraints on columns
- WHEN condition of triggers
- GROUP BY and HAVING clauses
- START WITH and CONNECT BY clauses