RE: Where Not In with unexpected results
> Thanks to Scott for suggestion, however, no nulls exist. If nulls had
> existed in the database, it should not have mattered since any rows not
> in the table table2 should have been returned regardless. This would
> suggest a bug in the Informix engine. Anymore suggestions are more than
> welcome.
While I won't argue whether NULLs exist in your database or not (I assume you've checked),
I believe the second sentence above is incorrect. Since NULL indicates an unknown value,
any comparison with that unknown results in a result of unknown. Thus, if there was a NULL
in table2, as each row from table1 was compared with the NULL, the result would be unknown,
and the row from table1 would be discarded.
When you run the query with NOT IN, it is equivalent to saying:
select distinct col1
from table1
where col2 = 'ValueB'
and col1 not = 'first value from table2'
and col1 not = 'second value from table2'
...
and col1 not = 'nth value from table2';
Each row in table1 is filtered through the above condition. If any of the values from
table 2 are NULL, that part of the condition is false, and since all conditions are ANDed
together, the whole condition evaluates to false.
When you swith NOT IN to IN, you get:
select distinct col1
from table1
where col2 = 'ValueB'
or col1 = 'first value from table2'
or col1 = 'second value from table2'
...
or col1 = 'nth value from table2';
Since the condition is now an OR, any row that matches a non-NULL value from table2 would
be evaluated true and returned in the result set.
This is easily tested by the sql below:
create table table1(
col0 serial,
col1 char(10),
col2 char(10)) in test;
create table table2(
cola serial,
colb char(10)) in test;
insert into table2 values (0,'BALL');
insert into table2 values (0,'ORANGE');
insert into table1 values (0,'BALL','ValueB');
insert into table1 values (0,'ORANGE','ValueB');
insert into table1 values (0,'TRIANGLE','ValueB');
insert into table1 values (0,'HAT','ValueB');
insert into table1 values (0,'CAT','ValueB');
select distinct col1
from table1
where col2 = 'ValueB'
and col1 not in (select colb from table2);
select distinct col1
from table1
where col2 = 'ValueB'
and col1 in (select colb from table2);
The results of the above are:
col1
CAT
HAT
TRIANGLE
col1
BALL
ORANGE
If we now insert a NULL into table2, watch what happens:
insert into table2 values (0,NULL);
select distinct col1
from table1
where col2 = 'ValueB'
and col1 not in (select colb from table2);
select distinct col1
from table1
where col2 = 'ValueB'
and col1 in (select colb from table2);
The results are:
col1
col1
BALL
ORANGE
Which, as I recall, is equivalent to your results. While this can cause unexpected
results, it is in compliance with the defined behavior of NULLs. Other databases should
perform in the same manner.
Mark Collins
mcollins@us.dhl.com
The problem lies in how easily and dangerously we forget that
manipulating things is not the same as understanding them.