SQL outer join question

From: Paul Sellars <paul.sellars_at_virgin.net>
Date: Fri, 31 Jul 1998 18:09:56 +0100
Message-ID: <6pstgc$pb3$1@nclient1-gui.server.virgin.net>

I've been reading 'Oracle the complete reference' from Osborne Press. I found the following: assume 2 tables
create table worker ( name char(10), location char(10)) create table workerskill ( name char(10), skill char(10)) sample date worker:

white       LA
brown     NY
green      LO
blue        DN
sample data workerskill:
white       forms
brown     oracle
green       unix

i.e. blue has no skills

Need a query that shows all workers, name and location, who do NOT have unix. This should include those who have no skills.

Normally I would use;
select name, location
from worker
where name not in ( select name from workerskill ) The book gives a more efficient piece of sql that uses outer joins

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.

Paul Sellars Received on Fri Jul 31 1998 - 12:09:56 CDT