|
ON vs. ON
Published: November 28, 2007
Hey, Professional:
In ON vs. WHERE, I demonstrated that an outer join yields different results depending on whether you place a selection expression in the ON clause or the WHERE clause for a secondary file. Now I want to follow up on that tip by looking at the difference between the ON and WHERE clauses for a primary table.
Here's the invoicing data I used in the previous tip. 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. Watch what happens if I 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. 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.
Contrast the use of an expression involving a field of the primary file with the use of an expression involving a field of the secondary file. Here's a query from the previous article. The ON clause refers to the item number, which is stored in the secondary file:
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 D.ITEM = 'AB1441'
47566 1 44 2004-05-03 1 AB1441 3
47567 2 5 2004-05-03 - - -
47568 1 10001 2004-05-03 - - -
47569 7 777 2004-05-03 - - -
47570 7 777 2004-05-04 2 AB1441 100
47571 2 5 2004-05-04 - - -
A selection expression in the ON clause does not behave identically for primary and secondary tables in an outer join. In both cases, ON restricts the rows that are allowed to participate in the join. However, all rows from the primary file, but not all secondary rows, are returned through the result set.
--Ted
RELATED STORY
ON vs. WHERE
 Post this story to del.icio.us
 Post this story to Digg
 Post this story to Slashdot
|
