User's Guide
   PART 1. Working with Databases
     CHAPTER 7. 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 are not concerned with which results the subquery returns, but simply with whether the subquery returns any 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 that can be expressed using a subquery: "Which customers placed orders after July 13, 1994?"

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

fname	lname
Grover	Pendelton
Ling Ling	Andrews
Bubba	Murphy
Almen	de Joie

Explanation of the existence test 

Here, for each row in the customer table, the subquery checks if that record's customer ID corresponds to one that has placed an order after July 13, 1994. 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 following two subqueries both produce the same results:

SELECT * 
FROM sales_order 
WHERE (order_date > '1994-07-13') AND (customer.id = sales_order.cust_id)
SELECT ship_date 
FROM sales_order 
WHERE (order_date > '1994-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. References to columns or expressions in the main table(s) are called outer references. 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, 1994, and the customer ID in the customer and sales_order tables match, then the first and last names from the customer table are displayed. Since the subquery references the main query, the subquery in this section, unlike those from previous sections, will return an error if you attempt to run it by itself.