The on Clause of an ANSI Inner Join

The on clause of an ANSI inner join specifies the conditions used when the tables or views are joined. Although you can use a join on any column of a table, performance may be better if the columns are indexed.

Often, you must use qualifiers (table or correlation names) to uniquely identify the columns and the tables to which they belong. For example:

from titles t left join titleauthor ta
on t.title_id = ta.title_id

This on clause eliminates rows from both tables where there is no matching title_id.

The on clause often compares the ANSI joins tables, as in the third and fourth line of this query:

select title, price, pub_name
from titles inner join publishers
on titles.pub_id = publishers.pub_id
and total_sales > 300

The join restriction specified in this on clause removes all rows from the join table that do not have sales greater than 300. The on clause can include an and qualifier to furthor specify search arguments, as illustrated in the fourth line of the query.

ANSI inner joins restrict the result set similarly whether the condition is placed in the on clause or the where clause (unless they are nested in an outer join). That is, these two queries produce the same result sets:

select stor_name, stor_address, ord_num, qty
from salesdetail inner join stores
on salesdetail.stor_id = stores.stor_id
where qty > 3000

and:

select stor_name, stor_address, ord_num, qty
from salesdetail inner join stores
on salesdetail.stor_id = stores.stor_id
and qty > 3000

A query is usually more readable if the restriction is placed in the where clause; this explicitly tells users which rows of the join table are included in the result set.