Key joins are the default if the keyword JOIN is used and no join type is specified—unless you use an ON clause. If you use an ON clause with an unspecified JOIN, key join is not the default and no generated join condition is applied.

For example, the following is a key join, because key join is the default when the keyword JOIN is used and there is no ON clause:

SELECT *
FROM A JOIN B;

The following is a join between table A and table B with the join condition A.x = B.y. It is not a key join.

SELECT *
FROM A JOIN B ON A.x = B.y;

If you specify a KEY JOIN or NATURAL JOIN and use an ON clause, the final join condition is the conjunction of the generated join condition and the explicit join condition(s). For example, the following statement has two join conditions: one generated because of the key join, and one explicitly stated in the ON clause.

SELECT *
FROM A KEY JOIN B ON A.x = B.y;

If the join condition generated by the key join is A.w = B.z, then the following statement is equivalent:

SELECT *
FROM A JOIN B
  ON A.x = B.y
  AND A.w = B.z;

 See also