Contents Index Natural joins of views and derived tables Key joins with an ON phrase

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

Key joins

When you specify a key join, Adaptive Server Anywhere generates a join condition based on the foreign key relationships in the database. To use a key join, there must be a foreign key relationship between the tables, or an error is issued.

The key join is a Sybase extension to the ANSI/ISO SQL standard. It does not provide any greater functionality, but it makes it easier to formulate certain queries.

When key join is the default 

Key join is the default in Adaptive Server Anywhere when all of the following apply:

Example 

For example, the following query is a simple key join that joins the tables product and sales_order_items based on the foreign key relationship in the database.

SELECT *
FROM product KEY JOIN sales_order_items

The next query is equivalent. It leaves out the word KEY, but by default a JOIN without an ON phrase is a KEY JOIN.

SELECT *
FROM product JOIN sales_order_items

The next query is also equivalent, because the join condition specified in the ON phrase happens to be the same as the join condition that Adaptive Server Anywhere generates for these tables based on their foreign key relationship in the sample database.

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

Key joins with an ON phrase
Key joins when there are multiple foreign key relationships
Key joins of table expressions
Key joins of views and derived tables
Rules describing the operation of key joins

Contents Index Natural joins of views and derived tables Key joins with an ON phrase