Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

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

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI