Is A Null Different from Another Null

ramk_1978 · January 28th, 2005, 12:24 PM

Hi,

My Question is:

Whether a Null value is different from another Null of the same field.

Ramkumar

A.D.Ramkumar

shahchi1 · January 28th, 2005, 01:07 PM

Yes, basically NULL is something you do not know? NULL is something which is undefined so you can not compare it with another undefined.

owain · January 28th, 2005, 01:23 PM

Not technically a definite 'Yes.' Because you do not know what the data in the field should be you can not be sure if indeed they are different or not. In SQL you would have to use 'IS NULL' for example:

Code:

WHERE MyField IS NULL

And not the equals operator, for example:

Code:

WHERE MyField = NULL

I hope this helps.

Regards
Owain Williams

shahchi1 · January 28th, 2005, 01:28 PM

*Is A Null Different from Another Null* The answer is yes or to be more precise you can't tell. You can certainly use IS NULL OR Is NOT NULL to check your column value is NULL or not.

owain · January 28th, 2005, 01:35 PM

That was my point. The answer is not yes, it is maybe. As you said, you can not tell.

Regards
Owain Williams

ramk_1978 · January 29th, 2005, 10:31 AM

Hi,

Thanks for the reply, but still i am not clear after seeing the reply.
I have got again in dilemma.

I should firmly say either "Yes" or "No" to this question. So, say good answer.

Ramkumar

A.D.Ramkumar

Imar · January 29th, 2005, 10:45 AM

The answer is probably No. You can check for null using IS NULL, as suggested.

However, you can't compare null values. Try this:

1. Create a table with a few non-nullable columns and a few that allow nulls.

2. Insert a few records, leaving the nullable columns empty.

3. Perform the following select:

SELECT Column1 FROM YourTable WHERE NullableColumn1 = NullableColumn2

This will give you no results, because the null in NullableColumn1 is not equal to the null in NullableColumn2

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Candle by Sonic Youth (Track 9 from the album: Daydream Nation) What's This?

owain · January 31st, 2005, 10:43 AM

In database terminology NULL means unknown. For example let's say you have a table containing people and a column called Age that allows NULL values. Here is an example of the data that could be in this table:

Code:

Name     Age
----     ---
Alice    NULL
Bob      41
Charlie  NULL

The age's of both Alice and Charlie are unknown (or NULL), they may be different or they may be the same, it is not known. Therefore both Alice and Charlie might be 28 years old, or Alice might by 26 and Charlie might be 54, it is simply not know.

Therefore the answer to your question of whether a NULL value is different from another NULL value is that they might be. Your particular database might imply that NULL values are the same, for example let's say you have a customer table with a NULLable column containing the number of orders they have placed, the data might look like this:

Code:

Name     OrderCount
----     ----------
Alice    NULL
Bob      2
Charlie  NULL

The fact that the number of orders that Alice and Charlie have placed are both NULL might mean that they have not placed any orders, therefore in this situation both the NULL values are indeed the same they are both zero.

I would like to point out that I would not design my databases this way, the number of orders that Alice and Charlie have placed is not unknown, it is zero or none. Therefore in the real world I would make this a non-NULLable column with a default of zero.

You should not assume that 2 NULL values are the same or different. NULL means unknown, you can not tell, will not be able to tell and never have been able to tell if 2 NULL values are the same or different.

Regards
Owain Williams

rockyrak · February 11th, 2005, 01:34 PM

Hi,

In Database concepts, NULL => means nothing. You don't know what is it. so why do you want to compare the value of something which you don;t know with another unknown value.

As everybody said, if u really want to compare NULL with another NULL. yes, you can do it. But :) again a NULL will come as a result.

There is no logic behind comparing one NULL with another. That is why oracle gives NULL as output again for any comparison with NULL variables with another variable using relational operators.

The only thing that is given to check whether the value of a column is NULL or not is IS NULL. And again, if you compare like this

where column = NULL, even the column is NULL it will result in NULL which is a unknown value and the condition will fail. you have to use the IS NULL.

is that clear?

Ram

Marden Lee · February 12th, 2005, 03:18 PM

ramk_1978, regarding your last post where you want a definite answer to if a Null is equal to another Null...

Like rockyrak and others before me just said you will not get a true or false result if you compare the two Nulls. You will get a Null in return.

It cannot be "No", because the result is not false. And it cannot be "Yes" because the result is not true either. The result is Null. So the definite answer you are looking to your question is... Null.

As a side note, if you want to make any kind of logical comparison between Null and a known value it will also return Null. For a distracted mind, asking if Null was diferent than "5" the answer would be yes, they are different. But in fact the answer to that is also Null.