Re: What is this SQL statement doing?

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 - 21:55:34 CDT