Re: What is this SQL statement doing?

Thanks Martin, I am now half way there, but ... "and b.skill(+) = 'SMITHY'" seems to say to me "return rows where skill IS 'SMITHY' " and what is apparently being returned is rows where skill is NOT 'SMITHY'.

How is this logic getting reversed? Maybe this is just an error in my book. Mike

"Martin Haltmayer" <Martin.Haltmayer_at_0800-einwahl.de> wrote in message news:3AC7EA26.808A8CED_at_0800-einwahl.de...
> The Oracle8i SQL Reference Release 2 (8.1.6) A76989-01, "Expressions,
 Conditons
> and Queries, 4 of 4", "Outer Joins", reads
>
> "If the WHERE clause contains a condition that compares a column from
 table B
> with a constant, the (+) operator must be applied to the column so that
 Oracle
> returns
> the rows from table A for which it has generated NULLs for this column.
> Otherwise Oracle will return only the results of a simple join. "
>
> Take the following test data:
>
> insert into worker (name, lodging) values ('George', 'abc');
> insert into worker (name, lodging) values ('Bill', 'def');
>
> insert into workerskill (name, skill) values ('George', 'WIELDER');
> insert into workerskill (name, skill) values ('Bill', 'SMITHY');
> insert into workerskill (name, skill) values ('Fred', 'SMITHY');
>
> The query with the (+) as well as the not in query will correctly return
> 'George', 'abc', whereas the one without the (+) at the line with the
 literal
> 'SMITHY' will not return any rows.
>
> Martin
>
>
>
> "Michael J. Moore" wrote:
> >
> > select a.name, lodging
> > from worker a, workerskill b
> > where a.name = b.name(+)
> > and b.name is NULL
> > and b.skill(+) = 'SMITHY';
> >
> > This is an example from a book. It selects all of the workers who do not
> > have 'SMITHY' as a skill.
> >
> > table worker is ( name, lodging) .... table skill is (name,skill)
> >
> > The above select will yield the same results as ...
> > select a.name ,lodging
> > from worker a
> > where a.name NOT IN
> > (select name
> > from workerskill
> > where skill = 'SMITHY')'
> >
> > I DO understand an Outer Join, but
> > I don't understand why the first select statement works. I particularly
> > don't
> > understand what the
> > "and b.skill(+) = 'SMITHY'"
> > is doing. Can anybody explain this to me or direct me to literature that
> > would
> > explain it?
> >
> > Thanks,
> > Mike
>
Received on Sun Apr 01 2001 - 22:18:36 CDT