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. | email@example.com 220 | Williams | Margary | firstname.lastname@example.org 142 | Burgeous | Paulette | email@example.com 110 | Seuss | | firstname.lastname@example.org 231 | Alcott | Luoisa May | email@example.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)
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.