User's Guide
   PART 1. Working with Databases
     CHAPTER 7. Using Subqueries       

Subqueries and joins

Many queries that make use of subqueries are automatically rewritten by the subquery optimizer as joins.

Example 

Consider the request, "When did Mrs. Clarke and Suresh place their orders, and by which sales representatives?" It can be handled by this query:

SELECT order_date, sales_rep 
FROM sales_order 
WHERE cust_id IN (
   SELECT id 
   FROM customer 
   WHERE lname = 'Clarke' OR fname = 'Suresh')

order_date	sales_rep
1994-01-05	1596
1993-01-27	667
1993-11-11	467
1994-02-04	195
1994-02-19	195
1994-04-02	299
1993-11-09	129
1994-01-29	690
1994-05-25	299

The subquery yields a list of customer ID's that correspond to the two customers whose names are listed in the WHERE clause, and the main query finds the order dates and sales representatives corresponding to those two people's orders.

Replacing a subquery with a join 

The same question can be answered using joins. Here is an alternative form of the query, using a two-table join:

SELECT order_date, sales_rep 
FROM sales_order, customer 
WHERE cust_id=customer.id AND (lname = 'Clarke' OR fname = 'Suresh')

This form of the query joins the sales_order table to the customer table to find the orders for each customer, and then returns only those records for Suresh and Mrs. Clarke.

Some joins cannot be written as subqueries 

Both of these queries will find the correct order dates, and sales representatives, and neither is more right than the other. Many people will find the subquery form more natural, because the request doesn't ask for any information about customer ID's, and because it might seem odd to join the sales_order and customer tables together to answer the question. If, however, the request is changed to include some information from the customer table, the subquery form will no longer work. For example, the request "When did Mrs. Clarke and Suresh place their orders, and by which representatives, and what are their full names?", it is necessary to include the customer table in the main WHERE clause:

SELECT fname, lname, order_date, sales_rep 
FROM sales_order, customer 
WHERE cust_id=customer.id AND (lname = 'Clarke' OR fname = 'Suresh')

fname	lname	order_date	sales_rep
Belinda	Clarke	1994-01-05	1596
Belinda	Clarke	1993-01-27	667
Belinda	Clarke	1993-11-11	467
Belinda	Clarke	1994-02-04	195
Belinda	Clarke	1994-02-19	195
Suresh	Naidu	1994-04-02	299
Suresh	Naidu	1993-11-09	129
Suresh	Naidu	1994-01-29	690
Suresh	Naidu	1994-05-25	299

Some subqueries cannot be written as joins 

Similarly, there are cases when a subquery will work, but a join will not. One example is the following query:

SELECT name, description, quantity
FROM product 
WHERE quantity <  2 * (
   SELECT avg (quantity) 
   FROM sales_order_items)

name	description	quantity
Tee Shirt	Tank Top	28
Baseball Cap	Wool cap	12
Visor	Cloth Visor	36
...	...	...

In this case, the inner query is a summary query and the outer query is not, so there is no way the two queries can be combined by a simple join.

For more on joins, see. Queries: Selecting Data from a Table