Outer join with IN clause 1

[BJCooperIT]

I found this bit of code in a package whose SQL I am reviewing:

FROM   ch_entity  i,
       rf_perf    t
WHERE  i.ch_entity_type    = 'INSURER'
AND    t.insurer_id(+)     = i.ch_entity_id
AND    t.ipsmry_id IN
       (SELECT ips.inspsmry_id
         FROM  inspsmry ips
         WHERE ips.inspsmry_created_dt 
               BETWEEN add_months(to_date('08/01/2008', 'MM/DD/YYYY'), +1) 
               AND     add_months(to_date('08/08/2008', 'MM/DD/YYYY'), +1))

FYI: t.insurer_id has a foreign key reference to i.ch_entity_id.

Let me see if I can ask my question clearly. Table "T" is outer joined to table "I" on t.insurer_id. So, if there is no match, the programmer wanted a row to be selected anyway. However, wouldn't the row be filtered out since t.ipsmry_id would be null and thus not IN the select from inspsmry?

Unfortunately, I have no documentation showing business rules, nor do I have any data, just empty tables. I think I have proven my case with the following test, but I would just like to be sure:

SQL> SELECT i.dummy i_value, t.dummy t_value
  2  FROM   dual  i,
  3         dual  t
  4  WHERE  t.dummy(+) = i.dummy||'z';
 
I_VALUE T_VALUE
------- -------
X                 [COLOR=green yellow]<--row returned due to outer join[/color]
 
SQL> 
SQL> SELECT i.dummy i_value, t.dummy t_value
  2  FROM   dual  i,
  3         dual  t
  4  WHERE  t.dummy(+) = i.dummy||'z'
  5  AND    t.dummy IN
  6          (SELECT dummy FROM  dual ips);
 
I_VALUE T_VALUE
------- -------
                  [COLOR=green yellow]<--no row returned[/color]
SQL>

Am I correct in thinking that either the statement needs reworked if they really intended for a row to be returned or the outer join is not necessary? Or is it just the end of a very long week?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[jimirvine]

You are correct. The restriction on the table t negates the outer join.

 

[BJCooperIT]

Thanks Jim! I just needed the extra pair of eyes.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com