Re: difference nested select union
In <5335hr$i08@nic.global-one.no> Nils.Myklebust@idg.no (Nils Myklebust) writes:
>Bryan Tonnet <wgtonb@sydsun.itsyd.bhp.com.au> wrote:
>:Jason Wickline wrote:
>:>
>:> I am unsure why I am getting different results from the following:
>:> select id
>:> from a
>:> where id not in (select id
>:> from b)
>:>
>:> and
>:> select a.id, b.id name
>:> from a, outer b
>:> where a.id = b.id
>:>
>You forgot to include the key sentence:
>:the count of the null name is not equal to the count of id in first
>:sql.
>So what he realy does is:
>The first count:
>select count(*)
>from a
>where id not in (select id
> from b)
I'm intrigued by this. I've spent quite a while staring at this and can't
for the life of me work out why this returns a count of null a.id's.
Surely it must *at least* also include those non-null a.id's that are not
in b.
I'm suffering badly, please ease my pain :)
>The second count:
>First he must do:
>select a.id, b.id name
>from a, outer b
>where a.id = b.id
>into temp tt_1
>Then the count of null name as he says:
>select count(*) from tt_1 where name is null
>Which should return the same count as the first
Yours in the wilderness of the SQL soul,
Bryan Tonnet
batonnet@zeta.org.au