Skip to Main Content
Practical PostgreSQL
book

Practical PostgreSQL

by Joshua D. Drake, John C. Worsley
January 2002
Intermediate to advanced content levelIntermediate to advanced
640 pages
16h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

PostgreSQL: Up and Running, 2nd Edition

PostgreSQL: Up and Running, 2nd Edition

Regina O. Obe, Leo S. Hsu
Learning PostgreSQL 11 - Third Edition

Learning PostgreSQL 11 - Third Edition

Christopher Travers, Andrey Volkov
Learn PostgreSQL

Learn PostgreSQL

Luca Ferrari, Enrico Pirozzi
PostgreSQL 13 Cookbook

PostgreSQL 13 Cookbook

Vallarapu Naga Avinash Kumar

Publisher Resources

ISBN: 9781449309770Supplemental ContentErrata Page