The problem of specifying a condition in a LEFT OUTER JOIN has been discussed before. LEFT OUTER JOIN with ON condition or WHERE condition? (September 16, 2005) explains the difference.
Your query probably looked something like this:
SELECT t1.foo
, t2.bar
FROM table1 AS t1
LEFT OUTER
JOIRequires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
N table2 AS t2
ON t2.table1_id = t1.id
WHERE t2.datefld =
( SELECT MIN(datefld)
FROM table2
WHERE table1_id = t1.id )
The problem here is that the WHERE condition will surely filter out all unmatched rows from table1. If there is no matching row in table2, then the MIN will be NULL, and so the WHERE condition fails (nothing is equal to NULL).
SELECT t1.foo
, t2.bar
FROM table1 AS t1
LEFT OUTER
JOIN table2 AS t2
ON t2.table1_id = t1.id
AND t2.datefld =
( SELECT MIN(datefld)
FROM table2
WHERE table1_id = t1.id )
Now, the MIN condition has been moved into the ON clause. In effect, the LEFT OUTER JOIN now says "get matching rows based on the keys and on the matching row being the MIN matching row." In other words, if there is no matching row in table2, the row from table1 is still returned.
Oracle White Papers: Fusion Middleware