Replacing NOT IN with an outer join

From: Joshua Powers <jpowers_at_ironlight.com>
Date: Fri, 04 Sep 1998 10:19:06 -0700
Message-ID: <35F02109.6F71E506@ironlight.com>

Can anyone explain to me in detail why the following two statements are equivalent? (This example is taken from page 289 of the Oracle 8 Reference Manual). I understand everything up to the last line (and B.Skill(+) = 'SMITHY'...). The reference manual does not explain what this statement is doing, and I can not figure out why this statement ends up selecting everyone who is not a smithy. It seems completely counter-intuitive to me. Thanks.

select A.Name, Lodging
  from WORKER A
where A.Name NOT IN

         (select Name
            from WORKERSKILL
          where Skill = 'SMITHY')

order by A.Name;

___AND___ select A.Name, Lodging
 from WORKER A, WORKERSKILL B
where A.Name = B.Name(+)
  and B.Name is NULL
and B.Skill(+) = 'SMITHY' order by A.Name; Received on Fri Sep 04 1998 - 12:19:06 CDT