You determine the columns in the derived table with the SELECT list.
SELECT [ALL | DISTINCT] select_list ...
select_list ::= { * | { table .* | expression [[AS] column ] } } ...
ALL
Duplicate rows in the derived table are retained.
DISTINCT
Duplicate rows are removed.
* Select all columns. The order and the names of the columns in the table specified in the FROM clause are used. If several tables are involved, the order of the tables in the FROM clause is used. At least one column must exist.
table.*
All the columns in table are selected. table must be included in the FROM clause. The order and the names of the columns in table are used. table may not be the correlation name for a DEE() table function.
expression
Expression denoting a derived column. If expression contains a column specification, the table to which the column belongs must be included in the FROM clause of this SELECT expression.
The names of the columns in the SELECT list must be unique. If you join tables and these base tables have columns with identical names, you must insert the appropriate table or correlation name in front of the column names in order to ensure unique identification.
If SELECT DISTINCT is specified, expression cannot consist of a multiple column specification.
If an aggregate function (AVG, COUNT, MAX, MIN, SUM) occurs in a column selection, the following restrictions apply:
Only column names that are specified in the GROUP BY clause or which are arguments in the aggregate function can be included in the SELECT list.
Only one aggregate function can be used with DISTINCT on the same level of a SELECT query. For example, you must not enter:
SELECT COUNT(DISTINCT ...) ...SUM(DISTINCT ...) ...
[AS] column
Name of the derived column specified with expression.
Example
SELECT order_num AS order_no, COUNT(*) AS total FROM orders GROUP BY order_num
|
|
column omitted:
If expression is a column name, the derived column is assigned this name, otherwise, the column name is not defined.
Example
SELECT order_num, COUNT(*) FROM orders GROUP BY order_num
|
|
Columns in the derived table
The order of the columns in the derived table corresponds to the order of the columns in the SELECT list.
The attributes of a derived column (data type, length, precision, digits to the right of the decimal point) are either taken from the underlying column or result from the specified expression.
A result column can return the NULL value if one of the following conditions is satisfied:
One of the columns used can return the NULL value.
This is always the case for columns of table functions. This is only the case for columns of base tables if a NOT NULL condition applies for the column.The expression that describes the result column contains at least one of the following operands or elements:
an indicator variable
a subquery
the aggregate function AVG, MAX, MIN or SUM
a CAST expression of the form CAST (NULL AS data_type)
a CASE containing the NULL value in at least one THEN or ELSE clause
a CASE expression with NULLIF
a CASE expression with COALESCE, where at least one operand of COALESCE (expression1 ... expressionn) contains one of the operands or elements listed above
Examples
“*” selects all the columns of the tables specified in the FROM clause. The sequence of the columns in the derived table is determined by the sequence of the tables in the from clause and by the defined sequence of columns within the tables.
SELECT * FROM orders, customers
CUSTMERS.* selects all columns from the CUSTOMERS table. DISTINCT specifies that duplicate rows are not to be included in the derived table.
SELECT DISTINCT order_num, customers.* FROM orders, customers
This selects the order numbers from the SERVICE and ORDERS tables. The column names must be unique. If tables with identical column names are linked, the column names must be qualified by the table name or correlation name. If you specify ALL (default), duplicate rows are included in the derived table.
SELECT ALL S.order_num, O.order_num FROM service S, orders O
This selects the name of the service and the price per service unit including VAT. If expression without the [AS] column specification is a column name, the column in the derived table is assigned this name (SERVICE_TEXT in the example).
[AS] column can be used to assign a name for the derived column, which is then referenced by expression (in the example this is GROSS_PRICE). The properties of a column in the derived table (data type, length, precision and scale) are either taken from the underlying column (SERVICE_TEXT) or are derived from the specified expression (service_price*(1.0+vat)).
SELECT service_text, service_price*(1.0+vat) AS gross_price
The derived table contains a single row. There is one column only in this row, which contains the sum of all the non-NULL values in SERVICE.SERVICE_PRICE, or NULL if there is no row matching this criterion. If the SELECT list includes an aggregate function, the list may only contain column names which occur within the argument of an aggregate function.
SELECT SUM(service_price) FROM service
The derived table contains a row with a single column containing the number of rows in CONTACTS. If expression without the AS clause does not identify a column, the column name is not defined.
SELECT COUNT(*) FROM contacts