User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

How joins are structured

A join operation may appear within a variety of statements, such as within the from clause of a select statement. The columns named after the FROM keyword are the columns to be included in the query results, in your desired order.

When two or more tables contain a column with the same name, you must qualify the column name explicitly to avoid ambiguity. For example, the product table and the sales_order_items table in the sample database both contain a column named id. If you wish to select either of these two columns, you need to identify the column you mean explicitly. If only one table uses a particular column name, the column name alone suffices.

SELECT product.id, sales_order_items.id, size
FROM ...

You do not have to qualify the column name size because there is no ambiguity about the table to which it belongs—although these qualifiers often make your statement clearer, so it is a good idea to get in the habit of including them.

As in any select statement, column names in the select list and table names in the FROM clause must be separated by commas.

     For information about queries use a single table, see Queries: Selecting Data from a Table.

  The FROM clause

Use the FROM clause to specify which tables and views to join. You can name any two or more tables or views.

Join operators 

Adaptive Server Anywhere provides four join operations:

• key joins

• natural joins

• joins using a condition, such as equality

• cross joins

Key joins, natural joins and joins on a condition may be of type inner, left-outer, or right-outer. These join types differ in the way they treat rows that have no matching row in the other table.

  Data types in join columns

The columns being joined must have the same or compatible data types. Use the convert function when comparing columns whose datatypes cannot be implicitly converted.

If the datatypes used in the join are compatible, Adaptive Server Anywhere automatically converts them. For example, Anywhere converts among any of the numeric type columns, such as INT or FLOAT, and among any of the character type and date columns, such as CHAR or VARCHAR.

     For the details of datatype conversions, see Data type conversions.