Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Joins: Retrieving Data from Several Tables » Explicit join conditions (the ON clause)

Explicit join conditions (the ON clause) Next Page

Generated joins and the ON clause


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;

For more information about key joins, see Key joins.