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