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 called Operators in Chapter 5" 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)
