Re: Boolean complement of a pair of OR clause
Jacob Salomon wrote:
> So what's the problem? To my chagrin, when I do this pair of WHERE
> clauses against a much larger table (a customer table with thousands of
> rows) the "valid row" query returns 0 rows.
>
> THIS IS NUTS! And it's turning me into squirrel bait myself!
As Bryan says, this is a nulls problem.
Try the following:
insert into t1 values (11, NULL);
insert into t1 values (NULL, "S");
-- returns 0 rows as you found out.
select * from t2
where number not in (select number from t1)
and name not in (select name from t1);
-- returns result set you want
select * from t2
where ( number not in (select number from t1 where number is not null ))
and ( name not in (select name from t1 where name is not null ))
i.e. number name
13 T
14 U
15 V
16 W
17 X
18 Y
19 Z
Mark Fisher
Britannic Assurance
--
------------------------------------------
The views expressed here are mine and not
those of my employer, Britannic Assurance
------------------------------------------