Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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 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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire