Guide to Oracle SQL (Buffalo State University Version)
Using Subqueries to Solve Problems
At the end of the last chapter I showed a query to display customers who have not yet placed an order. That query used a subquery. This is a very common task. Quite often you do not have the data in a table that you would need to solve a problem. The "customers without orders" is just such a problem. You need to form a subquery to find all the orders for and then pass that result set "up" to the parent query to use as a kind of "temporary table". A subquery can be used in the SELECT, FROM, WHERE, and HAVING statements.
Using a STAR Subquery
Suppose you wanted to identify all products that were sold through a specific channel . The next figure illustrates two (2) different approaches to solve this task. The first example uses a number of conditions on the WHERE clause. The second example uses a "star" query. The "star" query looks more elegant and actually is more efficient.
Using a Subquery To Create a Temporary Table
Suppose you wanted to identify the average salary for employees who are in different departments, locatoins, and cuintries. To solve this task you could use a subquery that uses multiple NATURAL JOIN clauses to create a result set (temporary table). which you can then query for the average salary.
Using NATURAL JOINS To Create a Temporary Table
You can also use a subquery to create a field for your SEECT statement.
Using Subqueries To Insert Records From One Table to Another
A common new year task is to produce the W-2 forms for that past year for all your employees and then to move all your employees who are no longer with the company into an "Inactive Employees" table. You can use a subquery on your INSERT query to insert these records and then use a DELETE query to remove those records from the "active employees" table.
Using Correlated Subqueries
Some tasks require that you create a subquery that uses a field from the parent query. You can use a Correlated" subquery for this. It is also known as a " repeating subquery" or a "synchronized subquery". In the next figure I use the "ep" alias to refer to the employees table in the parent query and "ec" as the employees table in the child subquery.