Speaking in Code

Monday, November 05, 2007

SQL Outer Join with a condition on the outer table

Here is a situation that confronts me frequently. I need to create an outer join that requires a condition on one of the columns in the outer table. Using the conventional syntax, meaning the join conditions are in the WHERE clause, causes the outer join to become an inner join and return only the rows that match the condition on the outer table.

The example below shows the bad SQL, and two solutions for it:

/* this SQL is bad: the PreferredFlag condition turns the
LEFT JOIN into an INNER JOIN
which returns only those tblPerson records
matched to a tblPersonAddress having the
PreferredFlag set to 1
*/
FROM tblPerson AS p,
tblPersonAddress AS a
WHERE p.PersonKey = a.PersonKey (+)
AND a.PreferredFlag = 1
AND p.PersonKey = 1234

/* my old solution: a nested subquery */
FROM tblPerson AS p,
(SELECT PersonKey,
FROM tblPersonAddress
WHERE PreferredFlag = 1) AS a
WHERE p.PersonKey = a.PersonKey (+)
AND p.PersonKey = 1234

/* using ANSI 92: the PreferredFlag condition
becomes part of the JOIN,
not part of the WHERE clause */
FROM tblPerson AS p
LEFT OUTER JOIN tblPersonAddress AS a
ON p.[PersonKey] = a.[PersonKey]
AND a.[PreferredFlag] = 1
WHERE p.PersonKey = 1234

Labels: