Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Subqueries

A subquery is a SELECT statement embedded within another SQL statement. You can use subqueries in place of column references, in place of table references, to generate values for comparison in a WHERE or HAVING condition, and to generate values for inserts and updates.

Treating Rowsets as Tables

So far in this chapter, you've selected data from tables. A SELECT statement is executed, and a set of rows comes back as the result. Imagine if you could further treat that set of rows as a table against which you issue another SELECT statement or perhaps an UPDATE or a DELETE. Using a subquery, you can do just what I've described, and that can lead to some interesting and elegant solutions to SQL problems.

One use for a subquery in the FROM clause is to aggregate data that has already been aggregated. For example, you might be faced with the following business problem:

Find all employees who have worked on projects 1001 and 1002 during the year 2004. Sum the number of hours each of those employees has worked on each project during that year. Report the ranges. Show the lowest number of hours that any employee worked on each project during 2004, as well as the highest number of hours.

This is an interesting problem to solve because you must sum the hours for employee/project combinations using the SUM aggregate function, and you must apply MIN and MAX to your sums. Example 4-32 shows one way to approach this problem using a subquery.

Example 4-32. Aggregating aggregated data

                  SELECT ...