ASA SQL User's Guide
Using Subqueries
The subquery optimizer automatically rewrites as joins many of the queries that make use of subqueries.
Consider the request, "When did Mrs. Clarke and Suresh place their orders, and by which sales representatives?" It can be answered with the following 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 |
---|---|
2001-01-05 | 1596 |
2000-01-27 | 667 |
2000-11-11 | 467 |
2001-02-04 | 195 |
... | ... |
The subquery yields a list of customer IDs 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.
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 Clarke.
Both of these queries 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 IDs, and because it might seem odd to join the sales_order and customer tables together to answer the question.
If, however, the request changes to include some information from the customer table, the subquery form no longer works. 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 | 1/5/01 | 1596 |
Belinda | Clarke | 1/27/00 | 667 |
Belinda | Clarke | 11/11/00 | 467 |
Belinda | Clarke | 2/4/01 | 195 |
... | ... | ... | ... |
Similarly, there are cases where a subquery will work but a join will not. For example:
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 to combine the two queries by a simple join.
For more information on joins, see Joins: Retrieving Data from Several Tables.