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

Key joins

The simplest way to join tables is to connect them using the foreign key relationships built into the database. This method is particularly economical in syntax and especially efficient.

Answer the question, "Which orders has Beth Reiser placed?"

SELECT customer.fname, customer.lname,
      sales_order.id, sales_order.order_date
FROM customer KEY JOIN sales_order 
WHERE      customer.fname = 'Beth'
   AND      customer.lname = 'Reiser'

fname	lname	id	order_date
Beth	Reiser	2142	1993-01-22
Beth	Reiser	2318	1993-09-04
Beth	Reiser	2338	1993-09-24
Beth	Reiser	2449	1993-12-14
Beth	Reiser	2562	1994-03-17
Beth	Reiser	2585	1994-04-08
Beth	Reiser	2002	1993-03-20

When the option to use a key join is available, it's generally a good idea to use it as opposed to another type.

A key join is valid if and only if exactly one foreign key is identified between the two tables. Otherwise, an error indicating the ambiguity is reported. Some constraints on these joins mean that they will not always be an available option.

• A foreign-key relationship must exist in the database. You cannot use a key join to join two tables that are not related through a foreign key.

• Only one foreign key relationship can exist between the two tables. If more than one such relationship exists, Adaptive Server Anywhere cannot decide which relationship to use and will generate an error indicating the ambiguity. You cannot specify the suitable foreign key in your statement since the syntax of the SQL language does not provide a means to do so.

• A suitable foreign key relationship must exist. You may need to create a join using two particular columns. A foreign-key relationship between the two tables may not suite your purpose.

Key joins are the default 

Key join is the default join type in Adaptive Server Anywhere. Anywhere performs a key join if you do not specify the type of join explicitly, using a keyword such as KEY or NATURAL, or by including an ON phrase.

For example, Adaptive Server Anywhere performs a key join when it encounters the following statement.

SELECT *
FROM product JOIN sales_order_items

Similarly, the following join fails because there are two foreign key relationships between these tables.

SELECT *
FROM employee JOIN department