Re: difference nested select union

batonnet@zeta.org.au (Bryan Tonnet) wrote: :In <5335hr$i08@nic.global-one.no> Nils.Myklebust@idg.no (Nils Myklebust) writes: :>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 :) I have assumed b.id contains no null values. If it contains a single null value this select will allways return a count of 0 (zero) - obviously :-) This is due to the fact that the set (or may be bag if there are duplicates in b.id) returned by the select id from b then contains a null. For that null value it is unknown whether it a.id is equal or not to it. With other words one can't know whether a.id is or is not in b.id. This is translated to false by sql and the where clause is allways false. (Remember to think of this as translated to false - sql doesn't realy know about unknown except through the is (is null or is not null) operators.) Did you get that? It isn't hard, but it shows the danger of null values. Moral: Never ever use null values if there is another option. Problem: C.J.Dates suggestion of using default values doesn't solve the problem as far as I can see (not fully at least). You would still need an "is default" operator in the language. :>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 Which it doesn't if b.id can contain null. Nulls are removed here by the = operator in the where clause of the outer select. Whether this or the above count is what you want is up to you to find out. :Yours in the wilderness of the SQL soul, Bevildered in the land of nulls and nothingness? You aren't alone. I've been there so many times. PS. Of course null in a.id also have an effect. I leave it to the reader to figur that out however. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org