Re: difference nested select union
Posted in 1996
Topics: General Discussion
Bryan,
You may be relieved in knowing that the original poster was
referring to b.id being null in the second query, rather than a.id in
the first! :-)
HTH,
Marco
________________________________________________________________________
____
rem radioterapia, which I immeritately manage, seldom agrees with what
I say
marco greco (Catania, Italy) Work:
marcog@ctonline.it rem radioterapia 39 95 447828 fax
446558
(was mar.greco@agora.stm.it) Achea 39 95 503117
--- On 7 Oct 1996 21:48:59 +1000 Bryan Tonnet <batonnet@zeta.org.au>
wrote:
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
-----------------End of Original Message-----------------