Using the WHERE clause for join conditions

Except when using outer joins, you can specify join conditions in the WHERE clause instead of the ON clause. However, you should be aware that there may be semantic differences between the two if the query contains outer joins.

The ON clause is part of the FROM clause, and so is processed before the WHERE clause. This does not make a difference to results except in the case of outer joins, where using the WHERE clause can convert the join to an inner join.

When deciding whether to put join conditions in an ON clause or WHERE clause, keep the following rules in mind:

When you specify an outer join, putting a join condition in the WHERE clause may convert the outer join to an inner join.
For more information about the WHERE clause and outer joins, see Outer joins and join conditions.
Conditions in an ON clause can only refer to tables that are in the table expressions joined by the associated JOIN. However, conditions in a WHERE clause can refer to any tables, even if they are not part of the join.
You cannot use an ON clause with the keywords CROSS JOIN, but you can always use a WHERE clause.
When join conditions are in an ON clause, key join is not the default. However, key join can be the default if join conditions are put in a WHERE clause.
For more information about the conditions under which key join is the default, see When key join is the default.

In the examples in this documentation, join conditions are put in an ON clause. In examples using outer joins, this is necessary. In other cases it is done to make it obvious that they are join conditions and not general search conditions.

Send feedback about this page via email or DocCommentXchange