Scalar subqueries are a powerful
enhancement to Oracle9i SQL. They allow
for quick formulation of extremely complex
SQL statements. Oracle's introduction of
scalar subquery support is another example
of the company's commitment to keeping
pace with the evolution of the SQL
language.
Scalar subquery examples
Once you become acquainted with the
syntax, you'll find scalar subqueries to
be very powerful. Scalar subqueries are
especially useful for combining multiple
queries into a single query. In Listing C,
we use scalar subqueries to compute
several different types of aggregations
(max and avg) all in the same SQL
statement. Note that this query uses both
scalar subqueries and in-line views.
select
(select max(salary) from emp) highest_salary,
emp_name employee_name,
(select avg(bonus) from commission) avg_comission,
dept_name
from
emp,
(select dept_name from dept where dept =
'finance');
