Many common joins are between two tables related by a foreign key. The most common join restricts foreign key values to be equal to primary key values. The KEY JOIN operator joins two tables based on a foreign key relationship. In other words, SQL Anywhere generates an ON clause that equates the primary key column from one table with the foreign key column of the other.
When you specify KEY JOIN, SQL 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.
A key join can be considered a shortcut for the ON clause; the two queries are identical. However, you can also use the ON clause with a KEY JOIN. Key join is the default when you specify JOIN, but do not specify CROSS, NATURAL, KEY, or use an ON clause. If you look at the diagram of the SQL Anywhere sample database, lines between tables represent foreign keys. You can use the KEY JOIN operator anywhere two tables are joined by a line in the diagram. For more information on the SQL Anywhere sample database, see Tutorial: Using the Sample Database.
Key join is the default in SQL Anywhere when all of the following apply:
the keyword JOIN is used.
the keywords CROSS, NATURAL or KEY are not specified.
there is no ON clause.
For example, the following query joins the tables Products and SalesOrderItems based on the foreign key relationship in the database.
SELECT * FROM Products KEY JOIN SalesOrderItems;
The next query is equivalent. It leaves out the word KEY, but by default a JOIN without an ON clause is a KEY JOIN.
SELECT * FROM Products JOIN SalesOrderItems;
The next query is also equivalent because the join condition specified in the ON clause happens to be the same as the join condition that SQL Anywhere generates for these tables based on their foreign key relationship in the SQL Anywhere sample database.
SELECT * FROM Products JOIN SalesOrderItems ON SalesOrderItems.ProductID = Products.ID;
Key joins with an ON clause
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