Re: difference nested select union

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 :> :I think these 2 should be different, shouldn't they? The first one selects :a.id's which are not in b, and the second one selects a.id's that are in b. :Was there a typo in your original post, or just a brain warp on the syntax :(happens to everyone, I get at least 4 a day). 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) 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 *if*: 1. This is actually the way he counts. 2. There is no other activity on the two tables involved between the time the first select is started and the last has finised. If these counts aren't the same something is wrong as far as I can see. Another issue is that the first simple statement very often will take a substantially longer time to execute than the combination of a select with an outer join into a temporary table followed by a select count from this table with the where test for null. This is a substantial problem we have, and I would love to find a way to do these kind of counts (or selects of the actual data) in a faster way. 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