On Vs Where:

Here is an invoicing data that we can use for our understanding. We have header information:
SELECT H.* FROM INVHDR AS H

Invoice Company Customer Date
47566 1 44 2004-05-03
47567 2 5 2004-05-03
47568 1 10001 2004-05-03
47569 7 777 2004-05-03
47570 7 777 2004-05-04
47571 2 5 2004-05-04

And we have related details:
SELECT D.* FROM INVDTL AS D

Invoice Line Item Price Quantity
47566 1 AB1441 25.00 3
47566 2 JJ9999 20.00 4
47567 1 DN0120 .35 800
47569 1 DC2984 12.50 2
47570 1 MI8830 .10 10
47570 2 AB1441 24.00 100
47571 1 AJ7644 15.00 1

Notice that the following query contains a selection expression in the WHERE clause:
SELECT H.INVOICE, H.COMPANY, H.CUSTNBR, H.INVDATE,
D.LINE, D.ITEM, D.QTY
FROM INVHDR AS H
LEFT JOIN INVDTL AS D
ON H.INVOICE = D.INVOICE
WHERE H.COMPANY = 1

Invoice Company Customer Date Line Item Quantity
47566 1 44 2004-05-03 1 AB1441 3
47566 1 44 2004-05-03 2 JJ9999 4
47568 1 10001 2004-05-03 - - -

The result set includes data for company one invoices only. If we move the selection expression to the ON clause:
SELECT H.INVOICE, H.COMPANY, H.CUSTNBR, H.INVDATE,
D.LINE, D.ITEM, D.QTY
FROM INVHDR AS H
LEFT JOIN INVDTL AS D
ON H.INVOICE = D.INVOICE
AND H.COMPANY = 1

Invoice Company Customer Date Line Item Quantity
47566 1 44 2004-05-03 1 AB1441 3
47566 1 44 2004-05-03 2 JJ9999 4
47567 2 5 2004-05-03 - - -
47568 1 10001 2004-05-03 - - -
47569 7 777 2004-05-03 - - -
47570 7 777 2004-05-04 - - -
47571 2 5 2004-05-04 - - -

This query differs from the previous one in that all invoice headers are in the resulting table, not just those for company number one. Notice that details are null for other companies, even though some of those invoices have corresponding rows in the details file. What’s going on?

Here’s the difference. When a selection expression is placed in the WHERE clause, the resulting table is created. Then the filter is applied to select the rows that are to be returned in the result set. When a selection expression is placed in the ON clause of an outer join, the selection expression limits the rows that will take part in the join, but for a primary table, the selection expression does not limit the rows that will be placed in the result set. ON restricts the rows that are allowed to participate in the join. In this case, all header rows are placed in the result set, but only company one header rows are allowed to join to the details.