Re: SQL outer join question

Paul Sellars <paul.sellars_at_virgin.net> wrote in article <6pstb6$p9h$1_at_nclient1-gui.server.virgin.net>... [snip]

> select name, location
> from worker w, workerskill s
> where w.name = s.name(+)
>     and s.name is null
>     and s.skill(+) = 'unix'
> 
> Can anybody explain how this works ? The book just says the login
> is 'extremely obscure'
> I can see the first condition produces a list of all workers
> (outer join), I don't understand the reason for next 2 conditions.
> Thanks for any help.

==>select name, location
==>from worker w, workerskill s
==>where w.name = s.name(+)

This would produce everything where w.name = s.name, plus anything where w.name has no match with s.name, but still avoiding a Cartesian product (mismatches between values where they don't equal each other).

==>and s.name is null

This "throws out" the ones with a match, producing a list of only those where w.name isn't even in s.name. If you run the first part above (with just the one "where" condition), you'd see a bunch of rows, but adding the "and s.name is null" would only return the ones from the original query that have no value for s.name. In fact, redo the query with s.name in your select and it will be clearer:

==>select w.name, location, s.name
==>from worker w, workerskill s
==>where w.name = s.name(+)

Also note the w.name in the select clause. Did you leave off the 'w.'? If you did, you'd get a "Column ambiguously defined" error because 'name' lives in both tables in the from clause.

Now note you'd have rows where s.name is null and some where it isn't. Now add in this:

==>and s.name is null

And you get only the ones where s.name is null.

Make more sense now?

==>and s.skill(+) = 'unix'

This is saying that you don't want anything that is 'unix' for a skill, but you also want to include nulls for s.skill as well. This can also be written/understood as:

==>and (s.skill != 'unix' or s.skill is null)

Meaning, s.skill is not 'unix' or the ones where it's null.

Note when saying that column_name != 'value', that means that the column isn't equal to 'value', but it is equal to some other value, but nulls don't count in Oracle because a null isn't possible to evaluate as true or false in terms of cardinality (being equal or not equal to something) so that's why the outer join (or the "is null") is used.

--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.) Received on Fri Jul 31 1998 - 12:37:37 CDT