SEO

#1 (**permalink**) 02-28-2008, 08:14 AM

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.

#2 (**permalink**) 02-28-2008, 08:14 AM

En/na alex ha escrit:
>
> select * from table1,table2 where table1.field1=table2.field1 and
> table1.field2=table2.field2 or (table1.field2 is null and table2.field2
> is null)

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))

#3 (**permalink**) 02-28-2008, 08:14 AM

In article <4518fcd9$0$75040$14726298@news.sunsite.dk>, alex says...
> is this the normal behaviour? is there any other way in which those rows
> where both fields are null are returned?

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

#4 (**permalink**) 02-28-2008, 08:15 AM

PleegWat wrote:

> In article <4518fcd9$0$75040$14726298@news.sunsite.dk>, alex says...
> > is this the normal behaviour? is there any other way in which those rows
> > where both fields are null are returned?
>
> In all of the various programming languages I've used so far that have
> null, it is valid that (null != null)

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.

--

#5 (**permalink**) 02-28-2008, 08:15 AM

Murdoc wrote:
> PleegWat wrote:
>
>
>>In article <4518fcd9$0$75040$14726298@news.sunsite.dk>, alex says...
>>
>>>is this the normal behaviour? is there any other way in which those rows
>>>where both fields are null are returned?
>>
>>In all of the various programming languages I've used so far that have
>>null, it is valid that (null != null)
>
>
> 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.
>

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@attglobal.net
==================

#6 (**permalink**) 02-28-2008, 08:15 AM

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:
> En/na alex ha escrit:
>>
>> select * from table1,table2 where table1.field1=table2.field1 and
>> table1.field2=table2.field2 or (table1.field2 is null and
>> table2.field2 is null)
>
> 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))

#7 (**permalink**) 02-28-2008, 08:15 AM

On Tue, 26 Sep 2006 12:11:34 +0200, alex wrote:
> 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?

This is normal. NULL cannot be compared to anything else, even another
"value of NULL", because NULL does not have a value. Comparing NULL
returns a NULL result as well, neither true nor false.

--
"The last refuge of the insomniac is a sense of superiority to the
sleeping world."
--Leonard Cohen, The Favourite Game

#8 (**permalink**) 02-28-2008, 08:15 AM

Jerry Stuckle wrote:

> Murdoc wrote:
> > PleegWat wrote:
> >
> >
> > > In article <4518fcd9$0$75040$14726298@news.sunsite.dk>, alex says...
> > >
> > > > is this the normal behaviour? is there any other way in which those rows where both fields are null are returned?
> > >
> > > In all of the various programming languages I've used so far that have null, it is valid that (null != null)
> >
> >
> > 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.
> >
>
> 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.

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

--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

weird results: (null!=null)??