www.mimer.com/developer

The SELECT Clause

---

The SELECT clause defines which values are to be selected. Values are specified by column references or expressions; where columns are addressed, the value selected is the content of the column.

SELECT *

This form of the SELECT clause specifies all columns in the Cartesian product of the tables specified in the FROM clause. The single asterisk may not be combined with any other value specification.

Example:

 SELECT * FROM HOTEL ...
 

Note: Use of SELECT * is discouraged in embedded SQL programs (except in EXISTS predicates) since the asterisk is expanded to a column list when the statement is compiled, and any subsequent alterations in the table or view definitions may cause the program to function incorrectly.

SELECT table.*

If a named table or view (table-reference or correlation-name) is followed by an asterisk in the SELECT clause, all columns are selected from that table or view.

This formulation may be used in a list of select specifications.

Example:

 SELECT HOTEL.*, ROOMS.ROOMNO
 FROM HOTEL,ROOMS
 WHERE HOTEL.HOTELCODE = ROOMS.HOTELCODE
 

If a correlation-name is used, it must be defined in the associated FROM clause, see The FROM Clause and Table-reference.

Note: Use of SELECT table.* is discouraged in embedded SQL programs (except in EXISTS predicates) since the asterisk is expanded to a column list when the statement is compiled, and any subsequent alterations in the table or view definitions may cause the program to function incorrectly.

SELECT expression

Values to be selected may be specified as expressions (using column references, set functions and literals, see Expressions).

Column names used in expressions must refer to columns in the tables addressed in the FROM clause. A column name must be qualified if more than one column in the set of table references addressed in the FROM clause has the same name.

Example:

 SELECT 'Room type ',ROOMTYPE,' costs ', PRICE * :EXCHANGE_RATE, 'in dollars'
 FROM ROOM_PRICES

SELECT ... AS column-label

A column-label may be added after each separate expression in the SELECT clause. Column-label is an SQL identifier which becomes the name of the column in the result set.

If no name is given the original column name is used, unless the new column was created by an expression, in which case the new column has no name. e.g. "SELECT COLUMN_NAME" would result in column called COLUMN_NAME in the result set, but "SELECT COLUMN_NAME + 1" would result in a column in the result set with no name.

Examples:

 SELECT PRICE AS INTERNAL, PRICE*RATE AS DOLLAR
 FROM ROOM_PRICES, EXCHANGE_RATE
 WHERE  CURRENCY = 'USD'
 
 SELECT PRICE AS INTERNAL
 FROM ROOM_PRICES
 WHERE PRICE > 300
 ORDER BY INTERNAL
 

Note: In the second example, the PRICE column is renamed INTERNAL so the ORDER BY clause must use the new name. However, column-label's cannot be used in a WHERE clause, which is why PRICE is used.

The Keywords ALL and DISTINCT

If ALL is specified or if no keyword is given, duplicate rows are not eliminated from the result of the select-specification.

If DISTINCT is specified, duplicate rows are eliminated. NULL is considered to be equal to NULL in this context.

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