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