ASA SQL User's Guide
  Using Subqueries
    How subqueries work

Existence test

A subquery that follows the keyword EXISTS is converted to a join only if it satisfies the following two conditions:

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

• The conjunct 'EXISTS (subquery)' is not negated.

• The subquery is correlated; that is, it contains an outer reference.

Example 

Therefore, the request, "Which customers placed orders after July 13, 2001?", which can be formulated by this query whose non-negated subquery contains the outer reference customer.id = sales_order.cust_id, could be converted to a join.

SELECT fname, lname
FROM customer
WHERE EXISTS (
   SELECT *
   FROM sales_order
   WHERE (order_date > '2001-07-13') 
 AND (customer.id = sales_order.cust_id))

The EXISTS keyword essentially tells the database server to check for empty result sets. When using inner joins, the database server automatically displays only the rows where there is data from all of the tables in the FROM clause. So, this query returns the same rows as does the one with the subquery:

SELECT fname, lname
FROM customer, sales_order
WHERE (sales_order.order_date > '2001-07-13') 
AND (customer.id = sales_order.cust_id)