Documentation Source Text

<p>^There are two types of simple SELECT statement - aggregate and 
non-aggregate queries. ^A simple SELECT statement is an aggregate query if
it contains either a GROUP BY clause or one or more aggregate functions
in the result-set. ^Otherwise, if a simple SELECT contains no aggregate
functions or a GROUP BY clause, it is a non-aggregate query.

<p><b>1. Calculation of input dataset.</b>

<p>^If a FROM clause is specified, the data on which a simple SELECT query
operates comes from the one or more tables or subqueries (SELECT statements
in parenthesis) specified following the FROM keyword. ^If there is only a
single table or subquery following the FROM clause, then the SELECT statement
operates on the contents of the table or the results of the subquery. If
there is more than one table or subquery following the FROM clause, then the
data from each table or subquery is combined into a single dataset for the
simple SELECT statement to operate on. Exactly how the data is combined
depends on the specific [join-op|join-ops] and 
[join-constraint|join-constraints] used to connect the tables or subqueries
together, as follows:

<ul>
  <li> <p>^If the join-op is a comma (","), then the composite dataset is
       the cartesian product of the sets of records from the left and right
       sides of the join-op.

  <li> <p>^If the join-op is a "CROSS JOIN" or "INNER JOIN", then the composite
       dataset is created in the same way as for the comma join-op and
       the result filtered based on the ON() and USING join-constraint clauses
       (if any).



       <p>^If there is an ON clause specified, then the ON expression is
       evaluated for each row of the cartesian product and the result cast to 
       an integer as if by a [CAST expression]. All rows for which the
       expression evaluates to NULL or zero (integer value 0) are excluded from
       the composite dataset.

       <p>^If there is a USING clause specified as part of the join-constraint,
       then each of the column names specified must exist in the datasets to 
       both the left and right of the join-op. ^(For each pair of namesake
       columns, the expression "lhs.X = rhs.X" is evaluated and the result

       cast to an integer. All rows for which one or more of the expressions
       evaluates to NULL or zero are excluded from the result set.)^ ^When
       comparing values as a result of a USING clause, the normal rules for
       handling affinities, collation sequences and NULL values in comparisons
       apply.




       <p>^For each pair of columns identified by a USING clause, only a
       single result column appears in the output dataset. ^This is the only

       difference between a USING clause and its equivalent ON constraint.


  <li> <p>^If the join-op is a "LEFT JOIN" or "LEFT OUTER JOIN", then the
       composite dataset is created as for a "CROSS JOIN". ^Except, after
       the ON and USING filtering clauses have been applied, an extra row is 
       added to the output for each row in the original left-hand input 
       dataset (if any) that corresponds to no rows at all in the composite
       dataset. ^The added rows contain NULL values in the columns that would
       normally contain values copied from the right-hand input dataset.

  <li> <p>^If the NATURAL keyword is added to any of the join-ops, then an
................................................................................
operates as if there were an implicit FROM clause that returns a dataset
consisting of a single row with zero columns.

<p><b>2. WHERE clause filtering.</b>

<p>^If a WHERE clause is specified, the WHERE expression is evaluated for 
each row in the dataset computed by the explicit or implicit FROM clause and
the result cast to an integer value. ^All rows for which the WHERE clause
expression evaluates to a NULL value or to zero (integer value 0) are excluded
from the dataset before continuing.

<p><b>3. Generation of the set of result rows.</b>

<p>Once the input dataset from the FROM clause has been filtered by the
WHERE clause expression (if any), the set of result rows for the simple 
SELECT are calculated. Exactly how this is done depends on whether the simple 
SELECT is an aggregate or non-aggregate query, and whether or not a GROUP
................................................................................
    <p>^Each group of input dataset rows contributes a single row to the 
    set of result rows. ^Subject to filtering associated with the DISTINCT
    keyword, the number of rows returned by an aggregate query with a GROUP
    BY clause is the same as the number of groups of rows produced by applying
    the GROUP BY and HAVING clauses to the filtered input dataset.
</ul>

<p><b>4. Removal of duplicate rows.</b>

<p>^(One of the ALL or DISTINCT keywords may follow the SELECT keyword in a 
simple SELECT statement. If neither of these are present, then the behaviour
is as if ALL were specified.)^ ^If the simple SELECT is a SELECT ALL, then the
entire set of result rows are returned by the SELECT. ^If the simple SELECT
is a SELECT DISTINCT, then duplicate rows are removed from the set of result
rows before it is returned. ^For the purposes of detecting duplicate rows,

<p>^There are two types of simple SELECT statement - aggregate and 
non-aggregate queries. ^A simple SELECT statement is an aggregate query if
it contains either a GROUP BY clause or one or more aggregate functions
in the result-set. ^Otherwise, if a simple SELECT contains no aggregate
functions or a GROUP BY clause, it is a non-aggregate query.

<p><b>1. Calculation of input data (FROM clause processing).</b>

<p>If a FROM clause is specified, the data on which a simple SELECT query
operates comes from the one or more tables or subqueries (SELECT statements
in parenthesis) specified following the FROM keyword. ^If there is only a
single table or subquery following the FROM clause, then the SELECT statement
operates on the contents of the table or the results of the subquery. ^If
there is more than one table or subquery following the FROM clause, then the
data from each table or subquery is combined into a single dataset for the
simple SELECT statement to operate on. Exactly how the data is combined
depends on the specific [join-op|join-ops] and 
[join-constraint|join-constraints] used to connect the tables or subqueries
together, as follows:

<ul>
  <li> <p>^If the join-op is a comma (","), then the composite dataset is
       the cartesian product of the sets of records from the left and right
       sides of the join-op.

  <li> <p>^If the join-op is a "CROSS JOIN" or "INNER JOIN", then the composite
       dataset is created in the same way as for the comma join-op. There
       is no difference between the "INNER JOIN" and "," join operators. The
       "CROSS JOIN" join operator produces the same data as the "INNER JOIN"
       and "," operators, but is <a href=optoverview.html#manctrl>handled 
       slightly differently by the query optimizer</a>.

  <li> <p>^(If there is an ON clause specified, then the ON expression is
       evaluated for each row of the cartesian product and the result cast to 
       a numeric value as if by a [CAST expression]. All rows for which the
       expression evaluates to NULL or zero (integer value 0 or real value 0.0)
       are excluded from the composite dataset.)^

  <li> <p>^If there is a USING clause specified as part of the join-constraint,
       then each of the column names specified must exist in the datasets to 
       both the left and right of the join-op. ^(For each pair of namesake
       columns, the expression "lhs.X = rhs.X" is evaluated for each row of
       the cartesian product and the result cast to a numeric value. All rows
       for which one or more of the expressions evaluates to NULL or zero are
       excluded from the result set.)^ ^When comparing values as a result of a
       USING clause, the normal rules for handling affinities, collation
       sequences and NULL values in comparisons apply. ^The column from the

       dataset on the left-hand side of the join operator is considered to
       be on the left-hand side of the comparison operator (=) for the purposes
       of collation sequence and affinity precedence.

       <p>^For each pair of columns identified by a USING clause, the column

       from the right-hand dataset is omitted from the joined dataset. ^This 
       is the only difference between a USING clause and its equivalent ON
       constraint.

  <li> <p>^If the join-op is a "LEFT JOIN" or "LEFT OUTER JOIN", then the
       composite dataset is created as for an "INNER JOIN". ^Except, after
       the ON and USING filtering clauses have been applied, an extra row is 
       added to the output for each row in the original left-hand input 
       dataset (if any) that corresponds to no rows at all in the composite
       dataset. ^The added rows contain NULL values in the columns that would
       normally contain values copied from the right-hand input dataset.

  <li> <p>^If the NATURAL keyword is added to any of the join-ops, then an
................................................................................
operates as if there were an implicit FROM clause that returns a dataset
consisting of a single row with zero columns.

<p><b>2. WHERE clause filtering.</b>

<p>^If a WHERE clause is specified, the WHERE expression is evaluated for 
each row in the dataset computed by the explicit or implicit FROM clause and
the result cast to a numeric value. ^All rows for which the WHERE clause
expression evaluates to a NULL value or to zero (integer value 0 or real value
0.0) are excluded from the dataset before continuing.

<p><b>3. Generation of the set of result rows.</b>

<p>Once the input dataset from the FROM clause has been filtered by the
WHERE clause expression (if any), the set of result rows for the simple 
SELECT are calculated. Exactly how this is done depends on whether the simple 
SELECT is an aggregate or non-aggregate query, and whether or not a GROUP
................................................................................
    <p>^Each group of input dataset rows contributes a single row to the 
    set of result rows. ^Subject to filtering associated with the DISTINCT
    keyword, the number of rows returned by an aggregate query with a GROUP
    BY clause is the same as the number of groups of rows produced by applying
    the GROUP BY and HAVING clauses to the filtered input dataset.
</ul>

<p><b>4. Removal of duplicate rows (DISTINCT processing).</b>

<p>^(One of the ALL or DISTINCT keywords may follow the SELECT keyword in a 
simple SELECT statement. If neither of these are present, then the behaviour
is as if ALL were specified.)^ ^If the simple SELECT is a SELECT ALL, then the
entire set of result rows are returned by the SELECT. ^If the simple SELECT
is a SELECT DISTINCT, then duplicate rows are removed from the set of result
rows before it is returned. ^For the purposes of detecting duplicate rows,