ASA SQL User's Guide
  Using Subqueries
    How subqueries work

Converting subqueries in the WHERE clause to joins

The Adaptive Server Anywhere query optimizer converts some multi-level queries to use joins. The conversion is carried out without any user action. This section describes which subqueries can be converted to joins so you can understand the performance of queries in your database.

Example 

The question "When did Mrs. Clarke and Suresh place their orders, and by which sales representatives?" can be written as a two-level query:

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

An alternate, and equally correct way to write the query uses joins:

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

The criteria that must be satisfied in order for a multi-level query to be able to be rewritten with joins differ for the various types of operators. Recall that when a subquery appears in the query's WHERE clause, it is of the form

SELECT select-list 
FROM table 
WHERE
[NOT] expression comparison-operator ( subquery ) 
| [NOT] expression comparison-operator { ANY | SOME } ( subquery ) 
| [NOT] expression comparison-operator ALL ( subquery ) 
| [NOT] expression IN ( subquery )
| [NOT] EXISTS ( subquery ) 
GROUP BY group-by-expression 
HAVING search-condition

Whether a subquery can be converted to a join depends on a number of factors, such as the type of operator and the structures of the query and of the subquery.