ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Joins overview

Joining two tables

To understand how a simple inner join is computed, consider the following query. It answers the question: which product sizes have been ordered in the same quantity as the quantity in stock?

SELECT DISTINCT name, size,
    sales_order_items.quantity
FROM product JOIN sales_order_items
ON product.id = sales_order_items.prod_id
  AND product.quantity = sales_order_items.quantity

You can interpret the query as follows. Note that this is a conceptual explanation of the processing of this query, used to illustrate the semantics of a query involving a join. It does not represent how Adaptive Server Anywhere actually computes the result set.

• Create a cross product of the product table and sales_order_items table. A cross product contains every combination of rows from the two tables.

• Exclude all rows where the product IDs are not identical (because of the join condition product.id    =    sales_order_items.prod_id).

• Exclude all rows where the quantity is not identical (because of the join condition product.quantity    =    sales_order_items.quantity).

• Create a result table with three columns: product.name, product.size, and sales_order_items.quantity.

• Exclude all duplicate rows (because of the DISTINCT keyword).

For a description of how outer joins are computed, see Outer joins.