ALLBASE/SQL Reference Manual (36216-90216)

514 Chapter12
SQL Statements S - Z
SELECT
• If this result table is used as input to a NATURAL....JOIN clause or a JOIN USING
(
ColumnList ) clause, and the column appearing twice in the result table is named
as a join column in the JOIN USING (
ColumnList ) clause or is selected by
ALLBASE/SQL as the join column in the NATURAL JOIN, an error will result. This
happens because it is impossible to specify which of the two common columns in the
result table is to participate in the following join.
• When writing a three or more table join with explicit join syntax, make sure that for
any single result table participating in a join, there are no duplicate column names
which will be named as a join column. To ensure this, make each join clause a
NATURAL...JOIN or a JOIN...USING (
ColumnList ), except for the final join, which
may contain these types or a JOIN...ON
SearchCondition3 clause. Otherwise,
ensure that each join clause is a JOIN...ON
SearchCondition3 clause.
• To join tables, without using explicit JOIN syntax, list the tables in the FROM clause,
and specify a join predicate in the WHERE clause.
• If you specify SELECT * and in the WHERE clause an equal predicate specifies the
join but there are no other limiting predicates, the result of this procedure is the
same as that obtained when using the INNER JOIN described above. The common
column appears twice in the query result, once for each table from which it was
obtained.
• If you select each column explicitly, naming each column only once (and
appropriately fully qualify a single column name for each pair of column names that
is common to both tables) the result is the same as that obtained when using the
NATURAL INNER JOIN, above. The common column appears only once in the
query result, and is taken from the table specified in the fully qualified column
name.
• To join a table with itself, define correlation names for the table in the FROM clause;
use the correlation names in the select list and the WHERE clause to qualify columns
from that table.
• NULLs affect joins and Cartesian products as follows:
• Rows are only selected for an inner join when the join predicate evaluates to true.
Since the value of NULL is undetermined, the value of the predicate NULL = NULL
is unknown. Thus, if the value in the common columns being joined is NULL, the
rows involved will not be selected.
• Rows excluded from the inner part of an outer join because the common column
values are NULL, are included in the outer part of the outer join.
• The existence of NULLs does not exclude rows from being included in a Cartesian
product. See the "SQL Queries" chapter for more information.
• When you use the GROUP BY clause, one answer is returned per group, in accord with
the select list:
• The WHERE clause eliminates rows before groups are formed.
• The GROUP BY clause groups the resulting rows.
• The HAVING clause eliminates groups.