Re: trouble with exclusionary SQL query
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);
-- 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) |
+------------------------------------------------------------+