| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Replacing NOT IN with an outer join
A copy of this was sent to Joshua Powers <jpowers_at_ironlight.com>
(if that email address didn't require changing)
On Fri, 04 Sep 1998 10:19:06 -0700, you wrote:
>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;
The (+) is an outer join. An outer join can cause 'missing' rows to be made up for us.
The second query is saying:
join worker to workerskill by (name,'SMITHY"). -- unusual in that its
a join based on a column and a constant instead of just columns...
If for a given row in WORKER with the values (name,'SMITHY') a row does
not exist in WORKERSKILL, make up a 'NULL' row in WORKERSKILL.
Keep the joined row if B.NAME is NULL (if the row from B was 'made up'
-- that (name, 'SMITHY') tuple didn't exist in the other table)
Actually, the doc is technically inaccurate :) Could you post the full document name, part number, and chapter for this example?. Oracle documents are all numbered by chapter-pagenumber, there are no page 289's in the Server Reference.
The 2 queries are *not* equivalent. They made assumptions about the data (in particular the Nullability of the name column in the workerskill table). Consider this example:
SQL> create table worker( name varchar2(25), lodging varchar2(25) ); SQL> create table workerskill( name varchar2(25), skill varchar2(25) );
SQL> insert into worker values ( 'Tom', 'House' ); SQL> insert into workerskill values ( 'Tom', 'NOT A SMITHY' ); SQL> insert into workerskill values ( NULL, 'SMITHY' );
SQL> select A.Name, Lodging
2 from WORKER A
3 where A.Name NOT IN
4 (select Name 5 from WORKERSKILL 6 where Skill = 'SMITHY')
no rows selected
SQL> select A.Name, Lodging
2 from WORKER A, WORKERSKILL B
3 where A.Name = B.Name(+)
4 and B.Name is NULL
5 and B.Skill(+) = 'SMITHY' order by A.Name;
NAME LODGING ------------------------------ ------------------------- Tom House
If they were equivalent you would expect them to give the same answer, but they don't... The reason they don't is because of the possible NULL values in the NAME column of the workerskill table. Since
A.name NOT IN ( select name from workerskill where skill = 'SMITHY' )
is neither TRUE nor FALSE if name is NULL, the where clause ALWAYS fails (a not in (subquery) where the subquery returns a NULL always fails). OTOH, the outer join finds some rows that satisfy its predicate quite easily since it uses more of a 'row at a time' processing instead of a SET.
Proof that NULLS can really mess you up if you don't understand them....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Sep 04 1998 - 13:06:31 CDT
 |
 |