Sub-queries are most often used to test the search condition given in a where clause. We will refer to the book and author tables for the examples below. The author table contains these data values:

 a_id | lastname |  firstname  |          email
------+----------+-------------+-------------------------
  136 | Brown    | Margaret W. | mwbrown@littletykes.com
  220 | Williams | Margary     | mwilliams@goto.com
  142 | Burgeous | Paulette    | pburgeous@yahoo.com
  110 | Seuss    |             | seuss@childprodigy.com
  231 | Alcott   | Luoisa May  | lalcott@bestbooks.com
(5 rows)

This simple example uses the equal sign to find only one instance (one row) of an author's first and last name where the author identification number is the highest.

Example 4-28. Sub-query using Equal

  SELECT lastname, firstname
    FROM author
   WHERE a_id =
        (SELECT MAX(author_id)
           FROM book);

This query returns:

 lastname | firstname
----------+------------
 Alcott   | Luoisa May
(1 row)

	Using the Equivalence Operator with Aggregates
	You may only use the equivalence ( = ) operator against one row. This is because a value can only be equal to another value, not to several other values. If the sub-select specified returns more than one row, an error will be returned requesting that the referenced values be operated upon by an aggregate function, or that the column in question be grouped by the GROUP BY clause.

A sub-query also gives more functionality to the idea of performing joins. This example looks for titles that begin with "The" from the book table. It also searches for the author idenfication numbers that match the specified condition. For each author_id returned, a lastname and firstname is taken from the author table and consequently displayed.

Example 4-29. Sub-queries Using IN

  SELECT lastname, firstname
    FROM author
   WHERE a_id IN
        (SELECT author_id 
           FROM book
          WHERE title
           LIKE 'The %');

The result of this sub-query is:

 lastname | firstname
----------+------------
 Williams | Margary
 Alcott   | Luoisa May
 Seuss    |
(3 rows)  

The keyword IN specified in the where condition tells the parser to search for that condition in the set of the sub-select statement. Because it is comparing to a set of values, it can be used to handle and return more than one row. On the other hand, the equal ( = ) to operator can only handle and return one row.

An example of a nested select statement using the LIKE operator is this:

Example 4-30. A Nested Select

  SELECT sugg_price
    FROM publish
   WHERE isbn IN
         (SELECT isbn
            FROM book
           WHERE author_id IN
                 (SELECT a_id
                    FROM author
                   WHERE lastname 
                    LIKE 'B%'));
   

This select statement searches for the author's last name, which starts with a B, and then refers to the book table using that author's identification number. It then uses that author id to look for isbn's of books that the author has written. Using the isbn, the parser then compares to the publish table and extracts the suggested price for the isbn number. In essence, it is asking what is the suggested price for books written by authors whose last name starts with a capital B.

The result is:

   sugg_price
------------
      $8.00
      $4.55
(2 rows) 

	Designing Sub-selects
	When deciphering a sub-select statement, or in the process of writing one, the best way is to read or write from the bottom, then work your way up. The reason you want to read/write statements in that particular order is because each outer select statement narrows down the possible true values. If you read from top to bottom, you may be expecting the wrong result.