Practical PostgreSQL

Using Sub-Queries

Sub-queries, first introduced to PostgreSQL in version 6.3, add a tremendous amount of flexibility to your SQL statements. Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

Sub-queries are allowed at nearly any meaningful point in a SQL statement, including the target list, the WHERE clause, and so on. A simple sub-query could be used as a search condition. For example, between a pair of tables. Example 4-59 demonstrates such a use of a sub-query.

Example 4-59. A simple sub-query

booktown=# SELECT title FROM books
booktown-#        WHERE author_id = (SELECT id FROM authors
booktown(#                          WHERE last_name='Geisel'
booktown(#                          AND first_name='Theodor Seuss');
            title
-----------------------------
 The Cat in the Hat
 Bartholomew and the Oobleck
(2 rows)

Example 4-59 uses the equal-to operator to compare the one row result of a sub-query on the authors table with the author_id column in the books table. In a single statement, the author identification number is acquired from the authors table by a WHERE clause specifying the name of Theodor Seuss Geisel, and the single identifier field returned is compared against the author_id column of the books table to return any books by Dr. Seuss.

Note that caution should be taken with this sort of sub-query: to use a normal value operator on the results of a sub-query, only one field must be returned. For example, if a more general sub-query were used to check for an author identifier, and several rows were found, you might see an error such as the following:

booktown=# SELECT title FROM books
booktown-#        WHERE author_id = (SELECT id FROM authors
booktown(#                          WHERE last_name ~ 'G');
ERROR: More than one tuple returned by a subselect used as an
expression.

Normal comparison operators cannot check for a single value being equal to multiple values, so a check for equivalence between the author_id column and multiple rows causes an error. This could be solved with a LIMIT 1 clause to ensure that the sub-query never returns more than a single row.

If you are interested in checking for the existence of a single value within a set of other values, use the IN keyword as an operator upon the result set from a sub-query. Example 4-60 illustrates comparing a sub-query which produces several results (the authors whose names begin with A through E) to the author_id column via the IN keyword (see the section titled Operators in Chapter 5, for more about the regular expression being employed).

Example 4-60. A sub-query using IN

booktown=# SELECT title FROM books
booktown-#        WHERE author_id IN (SELECT id FROM authors
booktown(#                            WHERE last_name ~ '^[A-E]');
         title
-----------------------
 2001: A Space Odyssey
 Franklin in the Dark
 Goodnight Moon
 Little Women
 The Velveteen Rabbit
 Perl Cookbook
(6 rows)

As a result of the use of IN, books from several authors may be found in the books table through a comparison against several rows from a sub-query. Note that while the IN keyword allows you to compare against multiple rows, the number of columns against which to be match must be identical.

If you wish to use IN to compare several columns, you may group column names together in the WHERE clause with parentheses immediately preceding IN. The number of columns grouped must be the same as those in the target list of the sub-query, and of the same data type for comparison.

Example 4-61 demonstrates a sub-query which targets the isbn column of the editions table, and an integer constant of 0, for each paperback book (with a type value of p). Those rows are then returned and compared against the isbn column and the stock column of the stock table with the IN keyword, effectively selecting any paperback book that is out of stock.

Example 4-61. A multi-column sub-query using IN

booktown=# SELECT isbn, cost, retail FROM stock
booktown-#        WHERE (isbn, stock)
booktown-#        IN (SELECT isbn, 0 FROM editions
booktown(#            WHERE type = 'p');
    isbn    | cost  | retail
------------+-------+--------
 0394800753 | 16.00 |  16.95
 0394900014 | 23.00 |  23.95
 0451457994 | 17.00 |  22.95
(3 rows)