NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-23
SELECT Statement
colname and colnum are as previously described for the GROUP BY clause,
with the following additional restrictions:
•
If you specify DISTINCT, colname must be in select-list.
•
If you specify a GROUP BY or HAVING clause, the ordering column must also
be a grouping column.
•
If an ORDER BY clause applies to a union of SELECT statements, the ordering
column must be explicitly referenced, outside a function or an expression, in
select-list of the leftmost SELECT statement.
ASCENDING and DESCENDING specify the sort order. If you specify the ORDER
BY clause without ASCENDING or DESCENDING, the default is ASCENDING.
Be sure to specify the ORDER BY clause for each column you need ordered.
Otherwise, SQL determines the order of the column and does not guarantee a
specific or consistent order of rows. ORDER BY can reduce performance, however,
so use it only if you require a specific order.
For the purpose of ordering a result table on a column that can contain null values, a
null value is considered equal to other null values but greater than all other nonnull
values.
ORDER BY is valid only for the SELECT part of an INSERT statement or for a
SELECT statement used in a cursor declaration in a host program.
UNION [ ALL ] select-statement
specifies a set UNION operation between the result table of this SELECT statement
and the result table of another SELECT statement. The select-lists in the two
SELECT statements must have the same number of columns, and columns in
corresponding positions within the select-lists must have comparable data
types (for example, both numeric or both character types).
The result of UNION is a table that contains rows belonging to either of the two
tables. If you specify UNION ALL, the table contains all the rows retrieved by each
SELECT statement; otherwise, duplicate rows are removed.
The number of columns in the table is the same as the number of columns in each
select-list. The column names in the table are the same as the corresponding
names in the select-list of the leftmost SELECT statement. A column
resulting from the UNION or expressions or constants has the name EXPR. For the
characteristics of data in the individual columns, see Considerations for UNION
on
page S-25.
A UNION operation is not allowed with SELECT INTO.
If the UNION of SELECT statements is part of a view definition or a cursor
declaration, then the view or cursor cannot be updated.