Re: trouble with exclusionary SQL query

Paul Roberts wrote: > In article <6abhs7$iub@cssun.mathcs.emory.edu>, Wanda Beck > <wbeck@sybase.com> wrote: > > > > >Hi, Everyone! > > > >I'm trying to formulate a query to select values from a column in > > one table where the values do not appear in a column in a second > > table. I've tried several different ways of querying and I either > > get all the rows from the first table (even if they are in the > > second table) or no rows at all. > >I've tried the following: > > > [...] > > > >select A from TAB1 > >where A not in( select B from TAB2 where B is not NULL ) ---------------------------------------^^^^^^^^^^^^^^^^^^^Shazzam! I have fallen into this trap more than once. I even once posted a rational for what the engine is thinking - Why the existence of a null value for B causes the outer select to receive no rows. It has been my experience that if column B in TAB2 were NONULLS, Wanda's query would have worked without the "where B is not NULL" business in the subquery. Since Wanda is posting from sybase.com, I conjecture that this is a general quirk in SQL, not merely an Informix logic flaw. Care to confirm or deny that, Wanda? -- -- Jake (Never yelled "CROWDED THEATER!" during a fire) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+