Re: comparing nulls

You always have to do something special for the NULL columns.

In this case, you need to rewrite your query:

select count(*) from t1, t2
where

NVL(t1.a1,'N') = NVL(t2.a2,'N')
NVL(t1.b1,'N') = NVL(t2.b2,'N')
NVL(t1.c1,'N') = NVL(t2.c2,'N');

You may substitute N with any other character -- as long as it's the same in both NVL functions in the same line.

Regards,
Paul

<orauser_at_my-deja.com> wrote in message news:8llgst$l62$1_at_nnrp1.deja.com...
> I am comparing the values in two tables, like
>
> select count(*) from t1, t2
> where
> t1.a1 = t2.a2
> t1.b1 = t2.b2
> t1.c1 = t2.c2;
>
> and all is well until say t1.b1 and t2.b2 are both null. Then
> the equality is not true.
>
> Can someone tell me the correct way to write this without having
> to do something special for the nullable columns??
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jul 26 2000 - 00:00:00 CDT