ASA SQL User's Guide
Joins: Retrieving Data from Several Tables
Joins overview
Adaptive Server Anywhere supports the following classes of joined tables.
CROSS JOIN A cross join of two tables produces all possible combinations of rows from the two tables. The size of the result set is the number of rows in the first table multiplied by the number of rows in the second table. A cross join is also called a cross product or Cartesian product. You cannot use an ON phrase with a cross join.
KEY JOIN (default) A join condition is automatically generated based on the foreign key relationships that have been built into the database. Key join is the default when the JOIN keyword is used without specifying a join type and there is no ON phrase.
NATURAL JOIN A join condition is automatically generated based on columns having the same name.
Join using an ON phrase You specify an explicit join condition. When used with a key join or natural join, the join condition contains both the generated join condition and the explicit join condition. When used with the keyword JOIN without the keywords KEY or NATURAL, there is no generated join condition. You cannot use an ON clause with a cross join.
Key joins, natural joins and joins with an ON clause may be qualified by specifying INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER. The default is INNER. When using the keywords LEFT, RIGHT or FULL, the keyword OUTER is optional.
In an inner join, each row in the result satisfies the join condition.
In a left or right outer join, all rows are preserved for one of the tables, and for the other table nulls are returned for rows that do not satisfy the join condition. For example, in a right outer join the right side is preserved and the left side is null-supplying.
In a full outer join, all rows are preserved for both of the tables, and nulls are supplied for rows that do not satisfy the join condition.