The FROM Clause and Table-reference

www.mimer.com/developer

The FROM Clause and Table-reference

The FROM clause defines an intermediate result set for the select-specification, and may define correlation names for the table references used in the result set.

where table-reference is:

General Syntax

All source tables and views referenced in the SELECT clause and at the top level in the WHERE clause (but not in any subselect used in the WHERE clause) must be named in the FROM clause.

Intermediate Result Sets

If a single table or view is named in the FROM clause, the intermediate result set is identical to the table or view.

If the FROM clause names more than one table or view, the intermediate result set may be regarded as the complete Cartesian product of the named tables or views.

Note: The intermediate result set is a conceptual entity, introduced to aid in understanding of the selection process.
The complete result set does not have any direct physical existence, so that the machine resources available do not need to correspond to the (sometimes very large) Cartesian product tables implied by multiple table references in a FROM clause.

Correlation Names

Correlation names introduced in the FROM clause redefine the form of the table name which may be used to qualify column names, see Qualified Object Names.

Correlation names may be used for two purposes:

to shorten table names, which saves typing and makes statements easier to follow and less error-prone.
For example,
 SELECT G.GUESTNO, SUM(AMOUNT)
 FROM BOOKADM.BILL AS B, BOOKADM.BOOK_GUEST AS G
 WHERE B.GUESTNO = G.GUESTNO
 GROUP BY G.GUESTNO
 
to relate a table to a logical copy of itself, as in the following example which selects all unique pairs of hotels located in the same city:
 SELECT HOTEL.NAME, HOTELCOPY.NAME
 FROM HOTEL, HOTEL AS HOTELCOPY
 WHERE HOTEL.CITY = HOTELCOPY.CITY
 AND HOTEL.NAME > HOTELCOPY.NAME
 
A table or view name is exposed in the FROM clause if it does not have a correlation name. The same table or view name cannot be exposed more than once in the same FROM clause.

The same correlation name may not be introduced more than once in the same FROM clause, and it cannot be the same as an exposed table or view name.

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se