Selecting Sources with the FROM Clause

The FROM clause allows you to choose either a table or a result set as a source for your specified target list. Multiple sources may be entered following the FROM clause, separated by commas. Specifying multiple sources in this fashion is functionally similar to a CROSS JOIN, discussed in the Section called Joining Data Sets with JOIN ."

Take care when specifying multiple FROM sources to PostgreSQL. The result of performing a SELECT on several comma-delimited sources without a WHERE or JOIN clause to qualify the relationship between the sources is that the complete Cartesian product of the sources will be returned. This is a result set where each column from each source is combined in every possible combination of rows between each other source.

Typically a WHERE clause is used to define the relationship between comma-delimited FROM sources, as shown in Example 4-27 (see the Section called Qualifying with the WHERE Clause " for more information about the WHERE clause).

You must be careful when identifying column names and using multiple sources in the FROM clause, as it can introduce ambiguity between identifiers. Consider a SELECT that draws from both the books table and the authors table. Each of these tables has a column called id. If specified, PostgreSQL will be unable to determine if the id column refers to the book, or the author:

booktown=# 
SELECT id FROM books, authors;

ERROR:  Column reference "id" is ambiguous

As a result of the potential for ambiguity, "complete" column names can be referenced through a special syntax called dot-notation . Dot-notation refers to the placement of a dot, or period, between the table name and a column name, in order to explicitly reference a particular column. For example, books.id refers to the id column within the books table.

Dot-notation is only required in instances of ambiguity between data sets. As shown in Example 4-27, you can use the column name as an identifier source, as long as it is unique among the available sets defined by the FROM clause. (In this case, the title column, which is unique to the books table, and the last_name column, which is unique to the authors tables).

booktown=# 
SELECT books.id, title, authors.id, last_name

booktown-# 
       FROM books, authors

booktown-# 
       WHERE books.author_id = authors.id;

  id   |            title            |  id   |  last_name
-------+-----------------------------+-------+--------------
   190 | Little Women                |    16 | Alcott
   156 | The Tell-Tale Heart         |   115 | Poe
 41472 | Practical PostgreSQL        |  1212 | Worsley
  2038 | Dynamic Anatomy             |  1644 | Hogarth
  1608 | The Cat in the Hat          |  1809 | Geisel
  1590 | Bartholomew and the Oobleck |  1809 | Geisel
  4513 | Dune                        |  1866 | Herbert
  4267 | 2001: A Space Odyssey       |  2001 | Clarke
  1501 | Goodnight Moon              |  2031 | Brown
  7808 | The Shining                 |  4156 | King
 41473 | Programming Python          |  7805 | Lutz
 41477 | Learning Python             |  7805 | Lutz
 41478 | Perl Cookbook               |  7806 | Christiansen
 25908 | Franklin in the Dark        | 15990 | Bourgeois
  1234 | The Velveteen Rabbit        | 25041 | Bianco
(15 rows)

If you wish to use a sub-query to generate a result set as a source for your FROM clause, the entire query must be surrounded by parentheses. This instructs PostgreSQL to correctly interpret the query as a sub-SELECT statement and to execute it before the SELECT statement within which it resides.

Example 4-28 demonstrates a peculiar query which retrieves all column values (*) from the books table via a sub-query. The query then retrieves a string constant of test and the id values from that result set (derived from the sub-query).

booktown=# 
SELECT 'test' AS test, id

booktown-# 
       FROM (SELECT * FROM books)

booktown-# 
       AS example_sub_query;

 test |  id
------+-------
 test |  7808
 test |  4513
 test |  4267
 test |  1608
 test |  1590
 test | 25908
 test |  1501
 test |   190
 test |  1234
 test |  2038
 test |   156
 test | 41472
 test | 41473
 test | 41477
 test | 41478
(15 rows)

The query in Example 4-28 is rather peculiar because the net effect is no different than if you had selected from the books table. This occurs because the result set from the sub-query is identical to the set of values in the books table. The use of this query demonstrates the combination of a string constant from one SELECT statement with a value drawn from the result set of a second SELECT statement. See the Section called Using Sub-Queries " for more realistic examples of sub-queries once you have a better understanding of the SELECT statement itself.