ASA SQL User's Guide
  Using Subqueries

Existence test

Subqueries used in the subquery comparison test and set membership test both return data values from the subquery table. Sometimes, however, you may be more concerned with whether the subquery returns any results, rather than which results. The existence test (EXISTS) checks whether a subquery produces any rows of query results. If the subquery produces one or more rows of results, the EXISTS test returns TRUE. Otherwise, it returns FALSE.

Example 

Here is an example of a request expressed using a subquery: "Which customers placed orders after July 13, 2001?"

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

Explanation of the existence test 

Here, for each row in the customer table, the subquery checks if that customer ID corresponds to one that has placed an order after July 13, 2001. If it does, the query extracts the first and last names of that customer from the main table.

The EXISTS test does not use the results of the subquery; it just checks if the subquery produces any rows. So the existence test applied to the following two subqueries return the same results. These are subqueries and cannot be processed on their own, because they refer to the customer table which is part of the main query, but not part of the subquery.

For more information, see Correlated subqueries.

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

It does not matter which columns from the sales_order table appear in the SELECT statement, though by convention, the "SELECT *" notation is used.

Negating the existence test 

You can reverse the logic of the EXISTS test using the NOT EXISTS form. In this case, the test returns TRUE if the subquery produces no rows, and FALSE otherwise.

Correlated subqueries 

You may have noticed that the subquery contains a reference to the id column from the customer table. A reference to columns or expressions in the main table(s) is called an outer reference and the subquery is said to be correlated. Conceptually, SQL processes the above query by going through the customer table, and performing the subquery for each customer. If the order date in the sales_order table is after July 13, 2001, and the customer ID in the customer and sales_order tables match, then the first and last names from the customer table appear. Since the subquery references the main query, the subquery in this section, unlike those from previous sections, returns an error if you attempt to run it by itself.