Re: What is this SQL statement doing?

Michael J. Moore wrote in message ...
>select a.name, lodging
> from worker a, workerskill b
>where a.name = b.name(+)
> and b.name is NULL
> and b.skill(+) = 'SMITHY';
>
> [snip]
>
>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?

Let's simplify things by removing the "and b.name is NULL" clause. What remains is an outer join which returns all rows from table a where there is a matching row in table b with b.skill = 'SMITHY', together with rows from table a which have no matching row in table b.

The (+) in the "b.skill(+) = 'SMITHY'" condition is necessary to ensure that the unmatched rows are returned (without it the condition "b.skill = 'SMITHY'" would eliminate them, as b.skill is null in these cases).

Now add back in the condition: "and b.name is NULL". This ensures that *only* the unmatched rows are returned and, consequently, the matched rows (which have b.skill = 'SMITHY' and b.name is not-null) are eliminated.

As a result the query returns rows from table a which do not have a matching row on table b with skill = 'SMITHY'

HTH, Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.