Replacing joins with subqueries

A join returns a result table constructed from data from multiple tables. You can also retrieve the same result table using a subquery. A subquery is simply a SELECT statement within another select statement. This is a useful tool in building more complex and informative queries.

For example, suppose you need a chronological list of orders and the company that placed them, but would like the company name instead of their customer ID. You can get this result using a join as follows:

To list the order_id, order_date, and company_name for each order since the beginning of 1994, type:

SELECT	  sales_order.id,
sales_order.order_date,
customer.company_name
FROM sales_order 
KEY JOIN customer
WHERE order_date > '1994/01/01'
ORDER BY order_date

The join in previous sections of the tutorial is more fully called an inner join.

You specify an outer join explicitly. In this case, a GROUP BY clause is also required:

SELECT  company_name,
MAX( sales_order.id ),state
FROM customer
KEY LEFT OUTER JOIN sales_order 
WHERE state = 'WA'
GROUP BY company_name, state

To list order items for products low in stock, type:

SELECT *
FROM sales_order_items
WHERE prod_id IN
 ( SELECT id
FROM product
WHERE quantity < 20 )
ORDER BY ship_date DESC 

The subquery in the statement is the phrase enclosed in parentheses:

(  SELECT id
FROM product
WHERE quantity < 20 )

By using a subquery, the search can be carried out in just one query, instead of using one query to find the list of low-stock products and a second to find orders for those products.

The subquery makes a list of all values in the id column in the product table satisfying the WHERE clause search condition.

Consider what would happen if an order for ten tank tops were shipped so that the quantity column for tank tops contained the value 18. The query using the subquery would list all orders for both wool caps and tank tops. On the other hand, the first statement you used would have to be changed to the following:

SELECT *
FROM sales_order_items
WHERE prod_id IN ( 401, 300 )
ORDER BY ship_date DESC

The command using the subquery is an improvement because it still works even if data in the database is changed.

Remember the following notes about subqueries:

• Subqueries may also be useful in cases where you may have trouble constructing a join, such as queries that use the NOT EXISTS predicate.

• Subqueries can only return one column.

• Subqueries are allowed only as arguments of comparisons, IN, or EXISTS clauses.

• Subqueries cannot be used inside an outer join ON clause.