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 ...