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

Join conditions

Tables can be joined using join conditions. A join condition is simply a search condition. It chooses a subset of rows from the joined tables based on the relationship between values in the columns. For example, the following query retrieves data from the product and sales_order_items tables.

SELECT *
FROM product JOIN sales_order_items
   ON product.id = sales_order_items.prod_id

The join condition in this query is

product.id = sales_order_items.prod_id

This join condition means that rows can be combined in the result set only if they have the same product ID in both tables.

Join conditions can be explicit or generated. An explicit join condition is a join condition that is put in an ON phrase or a WHERE clause. The following query uses an ON phrase. It produces a cross product of the two tables (all combinations of rows), but with rows excluded if the id numbers do not match. The result is a list of customers with details of their orders.

SELECT *
FROM customer JOIN sales_order
  ON sales_order.cust_id = customer.id

A generated join condition is a join condition that is automatically created when you specify KEY JOIN or NATURAL JOIN. In the case of a key join, the generated join condition is based on the foreign key relationships between the tables. In the case of a natural join, the generated join condition is based on columns that have the same name.

When you use an ON phrase with a key join or natural join, the join condition that is used is the conjunction of the explicitly specified join condition with the generated join condition. This means that the join conditions are combined with the keyword AND.