SELECT
selection
table-expression
|
A select-expression
specifies a
result table. It is used in the following Natural SQL statements:
INSERT
|
SELECT
|
UPDATE
This document covers the following topics:
, | ||||||||
* |
A selection
specifies the
columns of the result set tables to be selected.
Syntax Element Description:
Syntax Element | Description |
---|---|
ALL|DISTINCT |
Elimination of Duplicate Rows:
Duplicate rows are not automatically eliminated from the result of
a The alternative to |
scalar-expression |
Scalar Expression:
Instead of, or as well as, simple column names, a selection can also include general scalar expressions containing scalar operators and scalar functions which provide computed values (see also the section Scalar Expressions). Example: SELECT NAME, 65 - AGE FROM SQL-PERSONNEL ... |
AS |
The optional keyword AS introduces a
correlation-name
for a column.
|
correlation-name |
Correlation Name:
A The |
* |
Asterisk Notation:
All columns of the result table are selected. Example: SELECT * FROM SQL-PERSONNEL, SQL-AUTOMOBILES ... |
from-clause [where-clause] |
[group-by-clause] [having-clause] |
[order-by-clause] [fetch-first-clause] |
The table-expression
specifies
from where and according to what criteria rows are to be selected.
The following topics are covered below:
FROM
table-reference,…
|
This clause specifies from which tables the result set is built.
The tables specified in the FROM
clause must contain the
column fields used in the selection list.
You can either specify a single table or produce an intermediate table resulting from a subquery or a "join" operation (see below).
Since various tables (that is, DDMs) can be addressed in one
FROM
clause and since a
table-expression
can contain several
FROM
clauses if subqueries
are specified, the database ID (DBID) of the first DDM specified in the first
FROM
clause of the whole expression is used to identify the
underlying database involved.
The TABLE function-name
clause
belongs to the SQL Extended
Set and requires a
correlation-clause
with a column-name
list.
The period-specification
clause belongs to the SQL Extended
Set.
FOR
|
period-specification
optionally specifies that a period specification applies to the temporal table
table-name
. The same period name
(SYSTEM_TIME
or BUSINESS_TIME
) must not be specified
more than one time for the same table.
Optionally, a
correlation-clause
can be assigned to a
table-name
. For a
subquery
, a
correlation-clause
must be
assigned.
[AS ]
correlation-name
[(column-name,...)]
|
A correlation-clause
consists
of optional keyword AS
and a
correlation-name
and is optionally followed by a plain
column-name
list. The column-name
list belongs to
the SQL Extended
Set.
table-reference |
|
JOIN
table-reference
ON
join-condition |
||||||
(joined-table) |
A joined-table
specifies an
intermediate table resulting from a "join" operation.
The "join" can be an INNER
, LEFT
OUTER
, RIGHT OUTER
or FULL OUTER
JOIN
. If you do not specify anything, INNER
applies.
Multiple "join" operations can be nested; that is, the
tables which create the intermediate result table can themselves be
intermediate result tables of a "join" operation or a
subquery
; and the latter, in turn, can
also have a joined-table
or another
subquery
in its FROM
clause.
For INNER
, LEFT OUTER
, and RIGHT
OUTER
joins:
search-condition |
For FULL OUTER
joins:
full-join-expression =
full-join-expression [AND
]
|
column-name | |||||
|
(column-name
,
)
|
Within a
join-expression
only
column-names
and the
scalar-function
VALUE
(or its synonym
COALESCE
)
are allowed.
See details on column-name
.
The
data-change-table-reference
clause
belongs to the SQL Extended
Set.
FINAL TABLE (INSERT-statement)
|
|||||
TABLE (searched-UPDATE-statement)
|
|||||
OLD TABLE (searched-DELETE-statement)
|
|||||
FINAL TABLE (MERGE-statement)
|
A data-change-table-reference
specifies an intermediate result table, which is based on the rows that are
changed by the SQL change statement specified in the clause. A
data-change-table-reference
can only be
specified as the only table reference in the FROM
clause.
Syntax Element Description:
The xmltable-function
clause
belongs to the SQL Extended
Set.
The item xmltable-function
specifies an invocation of the built-in XMLTABLE
function.
[WHERE
search-condition]
|
The WHERE
clause is used to specify the selection criteria
(search-condition
) for the rows to be
selected.
Example:
DEFINE DATA LOCAL 01 NAME (A20) 01 AGE (I2) END-DEFINE ... SELECT * INTO NAME, AGE FROM SQL-PERSONNEL WHERE AGE = 32 END-SELECT ...
For further information, see Search Conditions.
[GROUP BY
column-reference,]
|
The GROUP BY
clause rearranges the table represented by
the FROM
clause into groups in a way that all rows within each
group have the same value for the GROUP BY
columns.
Each column-reference
in the
selection list must be either a GROUP BY
column or specified
within an aggregate-function
.
Aggregate functions are applied to the individual groups (not to the entire
table). The result table contains as many rows as groups.
For further information, see Column Reference and Aggregate Function.
Example:
DEFINE DATA LOCAL 1 #AGE (I2) 1 #NUMBER (I2) END-DEFINE ... SELECT AGE , COUNT(*) INTO #AGE, #NUMBER FROM SQL-PERSONNEL GROUP BY AGE ...
If the GROUP BY
clause is preceded by a WHERE
clause, all rows that do not satisfy the WHERE
clause are excluded
before any grouping is done.
[HAVING
search-condition]
|
If the HAVING
clause is specified, the GROUP
BY
clause should also be specified.
Just as the WHERE
clause is used to exclude rows from a
result table, the HAVING
clause is used to exclude groups and
therefore also based on a
search-condition
. Scalar expressions in
a HAVING
clause must be single-valued per group.
For further information, see Scalar Expressions and Search Conditions.
Example:
DEFINE DATA LOCAL 1 #NAME (A20) 1 #AVGAGE (I2) 1 #NUMBER (I2) END-DEFINE ... SELECT NAME, AVG(AGE), COUNT(*) INTO #NAME, #AVGAGE, #NUMBER FROM SQL-PERSONNEL GROUP BY NAME HAVING COUNT(*) > 1 ...
ORDER BY
|
sort-key |
|
, | ||||
INPUT
SEQUENCE |
|||||||
ORDER
OF table-designator |
The order-by
clause
specifies row ordering of the result table.
column-name | ||
integer | ||
sort-key-expression |
A sort-key-expression
is an
expression which is more than a
column-name
or an unsigned
integer
constant.
INPUT SEQUENCE
belongs to the
SQL Extended Set.
INPUT SEQUENCE
indicates that the result table reflects
the input order of the rows specified in the VALUES
clause of an
INSERT
statement.
INPUT SEQUENCE
can only be specified if an
INSERT
statement is specified in the
from-clause
.
ORDER OF table-designator
belongs to the SQL Extended
Set.
ORDER OF
specifies that the row ordering of the designated
table by the table-designator
is
applied to the result table of the query.
table-designator
uniquely
identifies a base table, a view, or the nested table expression of a subselect.
The fetch-first
clause
belongs to the SQL Extended
Set.
FETCH FIRST
|
1 integer |
ROWS ROW |
ONLY
|
The fetch-first
clause
limits the number of rows that can be fetched. It improves the performance of
queries when only a limited number of rows are needed.
Example 1:
DEFINE DATA LOCAL 01 #NAME (A20) 01 #FIRSTNAME (A15) 01 #AGE (I2) ... END-DEFINE ... SELECT NAME, FIRSTNAME, AGE INTO #NAME, #FIRSTNAME, #AGE FROM SQL-PERSONNEL WHERE NAME IS NOT NULL AND AGE > 20 ... DISPLAY #NAME #FIRSTNAME #AGE END-SELECT ... END
Example 2:
DEFINE DATA LOCAL 01 #COUNT (I4) ... END-DEFINE ... SELECT SINGLE COUNT(*) INTO #COUNT FROM SQL-PERSONNEL ...