ASA Getting Started
  Selecting Data Using Subqueries

Using subqueries instead of joins

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:

Using a join 

To list the order id, date, and company name for each order since the beginning of 2001, type the following:

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

For more on joins, see Selecting Data from Multiple Tables.

Using a subquery 

The following statement obtains the same results using a subquery instead of a join:

SELECT sales_order.id,
   sales_order.order_date,
   (   SELECT company_name FROM customer
       WHERE customer.id = sales_order.cust_id )
FROM sales_order
WHERE order_date > '2001/01/01'
ORDER BY order_date

The subquery refers to the cust_id column in the sales_order table even though the sales_order table is not part of the subquery. Instead, the sales_order.cust_id column refers to the sales_order table in the main body of the statement. This is called an outer reference. Any subquery that contains an outer reference is called a correlated subquery.

A subquery can be used instead of a join whenever only one column is required from the other table. (Recall that subqueries can only return one column.) In this example, you only needed the company_name column so the join could be changed into a subquery.

If the subquery might have no result, this method is called an outer join. The join in previous sections of the tutorial is more fully called an inner join.

Using an outer join 

To list all customers in Washington state, together with their most recent order ID, type the following:

SELECT    company_name, state,
   (   SELECT MAX( id )
       FROM sales_order
       WHERE sales_order.cust_id = customer.id )
FROM customer
WHERE state = 'WA'

The It's a Hit! company placed no orders, and the subquery returns NULL for this customer. Companies who have not placed an order are not listed when inner joins are used.

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

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