Re: I'm baffled by this one.

In article <349c3423.5340771_at_news.u-net.com>, neil_at_nmb.u-net.com (Neil Brown) wrote:

> I'm baffled by a unexpected responce from a simple query using Oracle
> 7 server version 7.3.2.3.2. It's this:
>
> Select item from tablea
> where item not in (select item from tableb)
> /
> Gives no data found. Where
>
> Select item from tablea
> minus
> select item from tableb
> /
> Gives some items. Further
>
> Select a.item from tablea a
> where a.item not in (select b.item from tableb b where b.item =
> a.item)
> /
> Gives the same response as the second query.
>
> Obviously tablea and tableb both have the same definition of item.
> I've not been able to reproce this on other tables, just two
> particular ones. My only guess is there is an index being used or not
> that is causing the server to give the wrong responce to the first
> query.
>
> Anyone?
>
> Neil.

I deduce that tablea(item) has a null value in at least one row.

The query "select ... where item not in (select item ...)" is identical to "select ... where item <> ALL (select item ...) When the subquery eturns a null value the prdicate fails (null is not equal to null) and so no row is returned.

Irritating but logical.

HTH. Chrysalis.

-- 
To get random signatures put text files into a folder called ³Random
Signatures² into your Preferences folder.