compare null values

Hi, I hope to select tuples in one table "ta" but not in another one "tb" who have the same columns(c1, c2). When both "ta" and "tb" has the same tuple (null, 2), I use: select * from ta where not exist( select * from tb where ta.c1=tb.c1 and ta.c2=tb.c2); the tuple (null, 2) was retrieved although it exist in both ta and tb. Why the second one can't get the result I want? Then I tried: select ta.* from ta, tb where ta.c1=tb.c1 and ta.c2=tb.c2; This time, no tuples returned. I suppose the results of the two sql are opposite to my opinion. What's wrong with them? It seems that null value has caused the problem because I have no such problem if the tuple is (1,2). How can I get what I want if there are null values?

Jing Liu <jingliu@ics.uci.edu> wrote: > Hi, I hope to select tuples in one table "ta" but not in another one > "tb" who have the same columns(c1, c2). [...] > where ta.c1=tb.c1 and ta.c2=tb.c2); > > the tuple (null, 2) was retrieved although it exist in both ta and tb. [...] > wrong with them? It seems that null value has caused the problem because > I have no such problem if the tuple is (1,2). How can I get what I want > if there are null values? A comparison with NULL is undetermined (even NULL=NULL). So those rows doesn't match the WHERE condition, because the expression doesn't become TRUE. You might try instead: WHERE (ta.c1=tb.c1 OR (ta.c1 IS NULL AND tb.c1 IS NULL)) AND (ta.c2=tb.c2 OR (ta.c2 IS NULL AND tb.c2 IS NULL)) Mit bestem Gruss, Marcus. -- Marcus.Gelleschun, ProSieben Information Service GmbH Gutenbergstr. 3, D-85767 Unterfoehring, Tel: 089/9507-5149

1) Prior to 7.3 select * from ta where not exist ( select * from tb where ( ta.c1=tb.c1 and ta.c2=tb.c2) or ( ta.c1 IS NULL and tb.c1 IS NULL and ta.c2=tb.c2) or ( ta.c1=tb.c1 and ta.c2 IS NULL and tb.c2 IS NULL) or ( ta.c1 IS NULL tb.c1 IS NULL and ta.c2 IS NULL and tb.c2 IS NULL) ); 2) >= 7.3 select * from ta where not exist ( select * from tb where NVL(ta.c1,-8888888)=NVL(tb.c1,-8888888) andNVL(ta.c2,-8888888)=NVL(tb.c2,-8888888)); -- Andrew Svikhnushin Inist Ltd. E-mail: san@inist.ru Jing Liu <jingliu@ics.uci.edu> wrote in message news:37BCD30B.D97695D4@ics.uci.edu... > Hi, I hope to select tuples in one table "ta" but not in another one > "tb" who have the same columns(c1, c2). > > When both "ta" and "tb" has the same tuple (null, 2), > > I use: > > select * > from ta > where not exist( > select * > from tb > where ta.c1=tb.c1 and ta.c2=tb.c2);> > the tuple (null, 2) was retrieved although it exist in both ta and tb. > > Why the second one can't get the result I want? > > Then I tried: > > select ta.* > from ta, tb > where ta.c1=tb.c1 and ta.c2=tb.c2; > > This time, no tuples returned. > > I suppose the results of the two sql are opposite to my opinion. What's > wrong with them? It seems that null value has caused the problem because > I have no such problem if the tuple is (1,2). How can I get what I want > if there are null values? >