Re: difference nested select union

In article <53aqnb$rc3@godzilla.zeta.org.au>, Bryan Tonnet <batonnet@zeta.org.au> writes >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) >>:> Means what it says - (a.id) for all rows in a which do not have a row in b with the same id. i.e. you get all rows in a which do not have a matching row in b. >>:> and >>:> select a.id, b.id name >>:> from a, outer b >>:> where a.id = b.id >>:> (a.id,NULL) for all rows in a which DO NOT HAVE a row in b with the same id. AND (a.id,b.id) for all rows in a which HAVE a row in b with the same in. i.e. You get all rows from a with the second column null if there is not a matching row in b. If the id's do not match the first one returns nothing but the second returns (a.id,NULL). > >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. > No problem, I always thinks of outer joins as you get NULL from the 'outer' table if no rows in the 'outer' table match. -- David Williams