Donald K. Burleson Oracle Tips

New Oracle9i Scalar subqueries

Oracle9i scalar queries are one of the strangest changes in all of Oracle9i.  Oracle started by allowing SQL sub-queries in the FROM clause in SQL statements and Oracle9i has now extended this functionality to allow SQL sub-queries inside the SELECT statement:

For example, consider the following SQL.  Note the embedded SELECT statements inside the outer SELECT and inside the FROM clause.

select

   (select max(salary) from emp) highest_salary,

   emp_name,

   (select avg(bonus) from commission) avg_comission,

   dept_name

from

   emp,

   (select dept_name from dept where dept = ‘finance’)

;

From this example, we see that Oracle allows for single-row subqueries to be included in the SELECT clause of any SQL statement.  While this ability may be of interest for obtuse queries, this new feature is of little interest to Oracle9i developers.

However, scalar subqueries can also be used by DML.  Consider the example below where we include a subquery inside an insert statement:

Insert into
   customer
(
   name,
   max_credit
)
values (
   ‘Sam’,
   (select max(credit) from credit_table where name = SAM’)
       );

Here we see that scalar subqueries can useful for streamlining INSERT and UPDATE statements that might otherwise be forced to use subqueries in their WHERE clause to get the required data. 

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.