Whenever an outer join is followed by a filter on the outer joined table, ensure that you are not filtering out the placeholder row.
E.g.
Customer to Address table with filter on current address
SELECT * FROM CUSTOMER C, ADDRESS A
WHERE C.CUSTOMER_ID = A.ADDR_CUST_ID(+)
AND A.CURRENT = 'Y'
The problem with the above query -
| CUSTOMER_ID | CUSTOMER_NAME | ADDRESS_ID | ADDR_CUSTOMER_ID | ADDRESS | CURRENT |
| 2 | Jane Smith | 10 | 2 | 100 Paradise Island | Y |
| 1 | John Smith | NULL | NULL | NULL | NULL |
When the placeholder address cells are added (NULL), the CURRENT column will also be NULL. Now if we filter this by 'Y', then the complete row is filtered out and hence the final result would be same as a inner join of CUSTOMER & ADDRESS table.
How to overcome this -
Check if the primary key of the outer joined table is NULL and dont filter it if NULL.
SELECT * FROM CUSTOMER C, ADDRESS A
WHERE C.CUSTOMER_ID = A.ADDR_CUST_ID(+)
AND (A.ADDRESS_ID IS NULL OR A.CURRENT = 'Y')
Why do we choose the primary key column ?
Because this the only column which would not non-null in all cases expect when in a outer joined output.
