ALLBASE/SQL Reference Manual (36216-90216)
512 Chapter12
SQL Statements S - Z
SELECT
an expression involving only constants.
• In addition to specifying how the result columns are derived, the select list also controls
their relative position from left to right in the result table. The first result column
specified by the select list becomes the leftmost column in the result table.
• The maximum number of columns in a query result is 1024, except when the query
contains the DISTINCT option or is within a UNION query expression. In this case, the
maximum number of columns is 1023. The maximum number of LONG data type
columns which can be directly selected or fetched in a select list is 40. However, any
number can be referenced in long string functions. They must be referenced by column
name only and cannot participate in an expression in the select list, unless they are
being accessed through long string functions.
• Result columns in the select list are numbered from left to right. The leftmost column is
number 1. Result columns can be referred to by column number in the ORDER BY
clause; this is especially useful if you want to refer to a column defined by an arithmetic
expression.
• When you specify the NATURAL....JOIN:
• You can not use the ON
SearchCondition3
or USING (
ColumnList
) clauses.
• Each pair of columns with the same column name, which are common to the two
tables being joined, will be coalesced into a single common column in the query
result. ALLBASE/SQL will automatically determine which columns to use for the
join. All columns which have the same column name in each of the tables being
joined will be used for the join.
• When common columns are referenced in the query, such as in the select list, you
must use only the unqualified name of the column.
• Each pair of columns common to two tables being joined must have the same or
compatible data types.
• For a SELECT *, each pair of columns, common to the two tables being joined, will be
coalesced into a single common column and will be the first columns displayed in the
result, in the order in which they were defined in the left hand table. They will be
followed by the columns from the left hand table that were not used for the join. The
last columns displayed will be those from the right hand table not participating in
the join. Columns not used for the join will be displayed in the order in which they
are defined in their respective tables.
• For any other SELECT, the columns displayed will be those specified in the select list,
in the order specified.
• If there are no common columns between the tables being joined, the columns
resulting from the join are the same as the columns that would result from the
Cartesian product of the joined tables. See the "SQL Queries" chapter.
• When you specify JOIN....ON
SearchCondition3
:
• You cannot use the keyword NATURAL or the USING
ColumnList
clause.
• Column Names from common columns used in the join predicate in
SearchCondition3
must be fully qualified. If additional predicates are used in