Thread: Some more weird NULL behavior

Hi:
 I tried the following: CREATE TABLE x(      a  NUMERIC(5,0),      b  VARCHAR(5) );
 CREATE TABLE y(      a  INTEGER,      b  VARCHAR(5) );
 INSERT INTO x(b) VALUES ('LUDZ'); INSERT INTO y(b) VALUES ('TEST');
 SELECT x.b,y.b FROM x,y WHERE x.a=y.a  returns zero rows.
 However: SELECT b FROM x WHERE a IS NULL returns 'LUDZ' SELECT b from y WHERE a IS NULL returns 'TEST'
 I also tried SELECT x.b,y.b FROM x,y WHERE x.a = CAST(y.a AS INTEGER);
 But it also returns zero rows.


Thank you in advance,
ludwig. 

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

On Fri, 8 Nov 2002, Ludwig Lim wrote:

> Hi:
>
>   I tried the following:
>   CREATE TABLE x(
>        a  NUMERIC(5,0),
>        b  VARCHAR(5)
>   );
>
>   CREATE TABLE y(
>        a  INTEGER,
>        b  VARCHAR(5)
>   );
>
>   INSERT INTO x(b) VALUES ('LUDZ');
>   INSERT INTO y(b) VALUES ('TEST');
>
>   SELECT x.b,y.b FROM x,y WHERE x.a=y.a
>   returns zero rows.

A comparison of two NULL values does not
return true, it returns unknown.
If you want to say equal or both null you
should do it explitly.
(x.a=y.a) or (x.a is null and y.a is null)