Re: trouble with exclusionary SQL query
In article <6al5oo$s9s@cssun.mathcs.emory.edu>, Wanda Beck
<wbeck@sybase.com> writes
>
>A colleague of mine tested this. Seems to be consistent on all systems.
>
>Thanks,
>--
>wanda
>
>---------------------- Forwarded by Wanda Beck/SYBASE on 01/27/98 10:00 AM
>---------------------------
>
>I just ran the following sequence of statements in Sybase, Oracle and DB2:
>
>-- Create and populate test tables
>create table tablea (charcol char(50));
>create table tableb (charcol char(50));>
>insert into tablea values('val1');
>insert into tablea values('val2');
>insert into tablea values('val3');
>insert into tablea values('val4');
>insert into tablea values('val5');
>insert into tablea values('val6');
>insert into tablea values('val7');
>insert into tablea values('val8');
>insert into tablea values('val9');
>insert into tablea values('val0');>
>insert into tableb values(null);
>insert into tableb values('val4');
>insert into tableb values('val6');>
>-- Verify setup
>select * from tablea;
>select * from tableb;>
>-- Test outer join not watching out for nulls
>select charcol
>from tablea
>where charcol not in (select charcol
> from tableb);>
OK Pick one row from A with charcol = 'a'
select charcol from tableb
returns at least one row where charcol is NULL.
For this row Since NULL means undefined
it could be 'a' in which case 'a' in 'a' is TRUE
It could be 'b' in which case 'a' in 'b' is FALSE
since we can't tell which is true
the answer is undefined i.e. NULL.
this applies to every row.
Hence query becomes
select..where NULL
Remember that SQL is based on set theory hence the above
becomes the NULL set, which of course contains no rows!!
>-- Test out join while watching out for nulls
>select charcol
>from tablea
>where charcol not in (select charcol
> from tableb
> where charcol is not null
> );>
>-- Cleanup
>drop table tablea;
>drop table tableb;>
>Results: They all behaved identically. That is, the first outer join
>returned no rows,
>the second returned what was expected.
>
>
>
>
>Wanda Beck
>01/26/98 05:16 PM
>
>To: Greg Carter/SYBASE@SYBASENOTES
>cc:
>Subject: Re: trouble with exclusionary SQL query
>
>FYI
>---------------------- Forwarded by Wanda Beck/SYBASE on 01/26/98 05:18 PM
>---------------------------
>
>
>jake@garpac.com on 01/26/98 10:53:30 AM
>
>Please respond to jake@garpac.com
>
>To: informix-list@rmy.emory.edu
>cc: (bcc: Wanda Beck/SYBASE)
>Subject: 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) |
>+------------------------------------------------------------+
>
>
--
David Williams
Maintainer of the Informix FAQ
Primary site (Beta Version) http://www.smooth1.demon.co.uk
Official site http://www.iiug.org/techinfo/faq/faq_top.html
I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care