ASA SQL User's Guide
  Using Subqueries
    How subqueries work

Comparison operators

A subquery that follows a comparison operator (=, <>, <, <=, >, >=) must satisfy certain conditions if it is to be converted into a join. Subqueries that follow comparison operators are valid only if they return exactly one value for each row of the main query. In addition to this criterion, a subquery is converted to a join only if the subquery

• does not contain a GROUP BY clause

• does not contain the keyword DISTINCT

• is not a UNION query

• is not an aggregate query

Example 

Suppose the request "When were Suresh's products ordered, and by which sales representative?" were phrased as the subquery

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

This query satisfies the criteria, and therefore, it would be converted to a query using a join:

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

However, the request, "Find the products whose in-stock quantities are less than double the average ordered quantity" cannot be converted to a join, as the subquery contains the aggregate function avg:

SELECT name, description
FROM product
WHERE quantity <  2 * (
   SELECT avg(quantity)
   FROM sales_order_items)