I've got this select:

select * from table1,table2 where table1.field1=table2.field1 and
table1.field2=table2.field2

and it does not return any rows for the elements where both "field2" are
null.

the only way i can get those rows is using this query:

select * from table1,table2 where table1.field1=table2.field1 and
table1.field2=table2.field2 or (table1.field2 is null and table2.field2
is null)


is this the normal behaviour? is there any other way in which those rows
where both fields are null are returned?

alex.

En/na alex ha escrit:
ok, it seems this is a generic sql feature. so i'm going to use the "or"
query. btw, the query was wrong as it needed a pair or parenthesis more:

select * from table1,table2 where table1.field1=table2.field1 and
(table1.field2=table2.field2 or (table1.field2 is null and table2.field2
is null))

In article <4518fcd9$0$75040$14726298 (AT) news (DOT) sunsite.dk>, alex says...
In all of the various programming languages I've used so far that have
null, it is valid that (null != null)

--
PleegWat
Remove caps to reply

PleegWat wrote:

That's not exactly correct. In Java, for example, the comparison (null == null) equates to true. This applies to Progress also.

I haven't tested any other languages, as these were the two that I have available.

--

Murdoc wrote:
C/C++ it's also true, but that's because null is actually just a special
value (usually 0, but not necessarily). However, SQL defines any
comparison to NULL as false.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

In SQL null is not a value, but it means actual value is not known for
that column. Hence one unknown value can not be compared with other
unknown value to say weather they are equal or not.So if you want to
join on columns that can be null you have to take special care of using
syntax like 'column is null' as you have done in the query below.

alex wrote:

Jerry Stuckle wrote:

That's interesting. Progress does a similar thing, that any arithmetic or boolean operation with a ? value returns ?, except for something like (? eq ?).

--