Listing A

col "Tablespace" for a13 col "Used MB"    for 99,999,999 col "Free MB"    for 99,999,999 col "Total MB"   for 99,999,999 col "Block Size" for 9,999,999   select    df.tablespace_name                          "Tablespace",    block_size                                  "Block Size",    (df.totalspace - fs.freespace)              "Used MB",    fs.freespace                               "Free MB",    df.totalspace                               "Total MB",    round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from    dba_tablespaces                               ts,    (select tablespace_name,         round(sum(bytes) / 1048576) TotalSpace       from dba_data_files       group by tablespace_name)                  df,    (select tablespace_name,         round(sum(bytes) / 1048576) FreeSpace       from dba_free_space       group by tablespace_name)                 fs where    ts.tablespace_name = fs.tablespace_name and    df.tablespace_name = fs.tablespace_name(+) ;

Listing B contains the output from this in-line view query against the data dictionary.

Listing B

Tablespace    Block Size     Used MB     Free MB    Total MB  Pct. Free         ------------- ---------- ----------- ----------- ----------- ----------         CWMLITE            4,096           6          14          20         70         DRSYS              4,096           8          12          20         60         EXAMPLE            4,096         153           0         153          0         INDX               4,096           0          25          25        100         SYSTEM             4,096         241          84         325         26         TOOLS              4,096           7           3          10         30         TS_16K            16,384           3           7          10         70         UNDOTBS            4,096           1         199         200        100         USERS              4,096           1          24          25         96

In the simple example in Listing A, the SQL subqueries are placed inside the FROM clause and assigned the aliases of df and fs. The df and fs subquery values are then referenced inside the SELECT clause. If you examine this query, you’ll see that it sums and compares two ranges of values from two tables, all in a single query. For some readers, seeing SQL inside the FROM clause is probably quite strange, and the scalar subquery is even stranger! The scalar subquery is a take-off of the in-line view whereby SQL subqueries can be placed inside the SELECT clause. Let’s take a look at a few examples.

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.

Listing C

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’) ;

Scalar subqueries are also handy for inserting into tables, based on values from other tables. In Listing D, we use a scalar subquery to compute the maximum credit for BILL and insert this value into a max_credit table.

Listing D

insert into    max_credit (    name,    max_credit ) values (    ‘Bill’,    select max(credit) from credit_table where name = ‘BILL’ );

The scalar subquery in Listing D is quite useful for Oracle data warehouse applications. In an Oracle data warehouse, it’s common for the DBA to pre-aggregate values to speed up query execution, and scalar subqueries are a powerful helper in aggregation. In Listing E, we populate an emp_salary_summary table with many types of aggregate values from the base tables.

Listing E

insert into    emp_salary_summary (    sum_salaries    max_salary,    min_salary,    avg_salary, values (    (select sum(salary) from emp),    (select max(salary) from emp),    (select min(salary) from emp),    (select avg(salary) from emp) ;

Restriction and usage

Scalar subqueries are restricted to returning a single value because they select a finite value. Scalar subqueries could be used in previous versions of Oracle in some parts of a SQL statement, but Oracle9i extends their use to almost any place where an expression can be used, including: