Select From Selects

Here's an interesting sql tip I discovered while looking through Biju's Oracle web site.  Did you know that you could use the results from a select as the "from table" to another select, instead of creating temporary tables to pass between select statements?  To do this, just code your sql statement something like "select columnlist1 from (select columnlist2 from table2 union all select columnlist3 from table3);", which creates a union of records of similar columns from table2 and table3, and passes them as records to the parent select statement, which can then do some further processing of that record union set.  As with subqueries, these subselects cannot contain an "order by" clause (which would be very useful to have for some situations, along with removing other restrictions on the "order by" clause in sql statements (even though it may go against some of the rules of the relational model) - hint, hint, Oracle!).

An example is shown below, which gets the total free space and other statistics about the tablespaces from dba_free_space (like "table2") and the total size from the datafile sizes (like "table3")  and reports the combined (grouped) results in megabytes or percentages (like the parent select):

select a.tablespace_name TSNAME,
     sum(a.tots)/1048576 Tot_Size,
     sum(a.sumb)/1048576 Tot_Free,
     (sum(a.sumb)/sum(a.tots))*100 Pct_Free,
     sum(a.largest)/1048576 Large_Ext,
     sum(a.chunks) Fragments
from (select tablespace_name, 0 tots, sum(bytes) sumb,
          max(bytes) largest, count(*) chunks
          from dba_free_space a
          group by tablespace_name
     union all
          select tablespace_name, sum(bytes), 0, 0, 0
          from dba_data_files
          group by tablespace_name) a
group by a.tablespace_name;

This is similar to using subqueries (subselects) for other tasks, such as the following:

-- Update the missing city names with their zip code city names.
update addresses a set a.city =
     (select z.city from zips z
          where z.zip = a.zip and rownum = 1)
     where a.city is null;
-- Find the zip codes for all cities with names like that for a given
-- zip code.
select distinct c.city,c.state,c.zip from cities c where c.city in
     (select z.city from zips z where z.zip = 72114);

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.