ALLBASE/SQL Reference Manual (36216-90216)
Chapter 12 511
SQL Statements S - Z
SELECT
qualified.
The second type of predicate limits, for the associated join only, the rows
which participate in the inner part of the join. Rows which are excluded
from the inner part of the join will be added to those preserved in the outer
part of the join. This predicate follows all general rules for search
conditions as specified in the "Search Conditions" chapter.
Predicates placed in the ON
SearchCondition3
clause, associated with an
instance of JOIN, apply only to that associated inner join. However,
predicates placed in the WHERE clause of the SELECT statement apply to
the entire query result, after all joins have been evaluated. Therefore you
must consider carefully the placement of limiting predicates to decide
whether they belong in the WHERE clause, or in an ON
SearchCondition3
clause associated with a particular instance of JOIN in
the
FromSpec
. See "Outer Joins" in the "SQL Queries" chapter for specific
examples illustrating the changes to the query result brought about by
changes in placement of the limiting predicates.
USING(
ColumnList
) specifies participating columns common to both tables being joined,
and can only be used if the keyword NATURAL has not been used in the
FromSpec
. The column names must be unqualified because the columns
occur in more than one table.
Description — Query Block Level
• The BULK option and INTO clause cannot be used interactively or in procedures.
• The clauses must be specified in the order given in the syntax diagram.
• A result column in the select list can be derived in any of these following ways:
• A result column can be taken directly from one of the tables or views listed in the
FROM clause.
• Values in a result column can be computed, using an arithmetic expression, from
values in a specified column of a table or view listed in the FROM clause.
• Values in several columns of a single table or view can be combined in an arithmetic
expression to produce the result column values.
• Values in columns of various different tables or views can be combined in an
arithmetic expression to produce the result column values.
• Aggregate functions (AVG, MAX, MIN, SUM, and COUNT) can be used to compute
result column values over groups of rows. Aggregate functions can be used alone or
in an expression. If you specify more than one aggregate function containing the
DISTINCT option, all these aggregate functions must operate on the same column. If
the GROUP BY clause is not specified, the function is applied over all rows that
satisfy the query. If the GROUP BY clause is specified, the function is applied once
for each group defined by the GROUP BY clause. When you use aggregate functions
with the GROUP BY clause, the select list can contain
only
aggregate functions and
columns referenced in the GROUP BY clause.
• A result column containing a fixed value can be created by specifying a constant or