ASA SQL User's Guide
  Using Subqueries
    How subqueries work

Set membership test

A query containing a subquery that follows the keyword IN is converted into a join only if:

• The main query does not contain a GROUP BY clause, and is not an aggregate query, or the subquery returns exactly one value.

• The subquery does not contain a GROUP BY clause.

• The subquery does not contain the keyword DISTINCT.

• The subquery is not a UNION query.

• The subquery is not an aggregate query.

• The conjunct 'expression IN (subquery)' must not be negated.

Example 

So, the request "Find the names of the employees who are also department heads", expressed by the query:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_id IN (
   SELECT dept_head_id
   FROM department
   WHERE (dept_name='Finance' or dept_name = 'Shipping'))

would be converted to a joined query, as it satisfies the conditions. However, the request, "Find the names of the employees who are either department heads or customers" would not be converted to a join if it were expressed by the UNION query.

A UNION query following the IN operator can't be converted 

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_id IN (
   SELECT dept_head_id
   FROM department
   WHERE (dept_name='Finance' or dept_name = 'Shipping')
   UNION
   SELECT cust_id
   FROM sales_order)

Similarly, the request "Find the names of employees who are not department heads" is formulated as the negated subquery

SELECT emp_fname, emp_lname
FROM employee
  WHERE NOT emp_id IN (
   SELECT dept_head_id
   FROM department
   WHERE (dept_name='Finance' OR dept_name = 'Shipping'))

and would not be converted.

The conditions that must be fulfilled for a subquery that follows the IN keyword and the ANY keyword to be converted to a join are identical. This is not a coincidence, and the reason for this is that the expression

A query with an IN operator can be converted to one with an ANY operator 

WHERE column-name IN( subquery )

is logically equivalent to the expression

WHERE column-name = ANY( subquery )

So the query

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_id IN (
   SELECT dept_head_id
   FROM department
   WHERE (dept_name='Finance' or dept_name = 'Shipping'))

is equivalent to the query

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_id = ANY (
   SELECT dept_head_id
   FROM department
   WHERE (dept_name='Finance' or dept_name = 'Shipping'))

Conceptually, Adaptive Server Anywhere converts a query with the IN operator to one with an ANY operator, and decides accordingly whether to convert the subquery to a join.