First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 17. Introduction to 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 1994:

1. Type the following:

   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

id	order_date	company_name
2473	1994-01-04	Peachtree Active Wear
2474	1994-01-04	Sampson & Sons
2036	1994-01-05	Hermanns
2106	1994-01-05	Salt & Pepper's
2475	1994-01-05	Cinnamon Rainbow's

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 > '1994/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:

1. 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'

company_name	state	MAX(id)
Custom Designs	WA	2547
It's a Hit!	WA	(NULL)

The It's a Hit! company placed no orders, and the subquery returns NULL for this customer. Companies who have not placed an order would not be listed if an inner join was 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