Oracle and NULL

apapadimoulis

This just absolutely kills me.

SELECT * FROM SM_STPD_TB
WHERE SM_STPD_CD != 'XYZ'

Seems pretty logical to me ... returns all rows where SM_STPD_CD is NOT 'XYZ'. But, being Oracle, this will not return what you expect ... it will not return rows where SM_STPD_CD is null. Instead, you need to do this:

SELECT * FROM SM_STPD_TB
WHERE SM_STPD_CD != 'XYZ'
AND SM_STPD_CD IS NULL

Yes, I'm quite familar with the theory behind null. Nothing can be compared to null because the comparison will always be null. A test of null requires a special operation because null is so special. Blah blah blah.

But this is #$%&&* rediculous. In all database models that I know, NULL means there is no data in the field. It doesn't mean there's some mystical unknown, cannot be known, immaginary number value. Nothing was put in there. Plain and simple.

mkesh

Please check what you write before you post - shouldn't it be "return all rows where SM_STPD_CD is NOT 'XYZ'?

apapadimoulis

Well, I fixed it. I was too busy fixing the problems that mistake caused [:P] to actually proof read!

Jacob_K

Ha. Fields (columns, whatever they call them with Oracle) with that value 'XYZ' will always be not null, so it seems like rows where SM_STPD_CD is null would be returned because ('XYZ' != null). Hmmmm.

I'm so glad that I've never and that I will never have to use Oracle.

Valeri

Actually you need to do this:

SELECT * FROM SM_STPD_TB
WHERE SM_STPD_CD != 'XYZ'
OR SM_STPD_CD IS NULL

otherwise it will only return values that are not 'XYZ' AND NULL so it will only return the NULL values.

Valeri

Actually...i tried to do this:

SELECT * FROM SM_STPD_TB
WHERE SM_STPD_CD != 'XYZ'
AND SM_STPD_CD IS NULL

and it didn't even bring back the NULL values..."no rows returned".

Yay Oracle!

Katja

Maybe it should be:

SELECT * FROM SM_STPD_TB
WHERE SM_STPD_CD != 'XYZ'
OR SM_STPD_CD IS NULL

Then again, I'm no professional Oracle DBA so what do I know about it...

Valeri

yep...see my first post.

apapadimoulis

Whoops -- typo in the original post. replace AND with OR.

But still, it's pretty stupid that you need to put that clause in anyway. Any value that is not 'XYZ' should include values that are NULL as well.

Frodo

Well you can always use NVL...

SELECT * FROM SM_STPD_TB
WHERE NVL(SM_STPD_CD, '') != 'XYZ'

Or even better, the remarkably obtuse DECODE statement...

SELECT * FROM SM_STPD_TB
WHERE DECODE(SM_STPD_CD, NULL, 'ZYX', SM_STPD_CD) != 'XYZ'

There's nothing more likely to generate a WTF than multiple embedded decode statements!

moondogg

@Frodo said:

Well you can always use NVL...

SELECT * FROM SM_STPD_TB
WHERE NVL(SM_STPD_CD, '') != 'XYZ'

At least in 8i, there is no such thing as a zero-length string, so (if I remember the asinine rules correctly) NVL(SM_STPD_CD,'') = NVL(SM_STPD_CD,NULL) = SM_STPD_CD.

Frodo

Ooops my bad ... i think this is the same in 9i as well. I seem to recall using NVL(field, '¬') or some other arbitary value just to get it to work previously as well.

And of course, not to mention, the inevitable right padding of spaces in your varchar fields that means that 'Bob' and 'Bob ' never match unless you RTRIM every field that you compare.

Ross_Presser

@Alex Papadimoulis said:

Whoops -- typo in the original post. replace AND with OR.

But still, it's pretty stupid that you need to put that clause in anyway. Any value that is not 'XYZ' should include values that are NULL as well.

But you didn't ask for "any value that is not 'XYZ'". You asked for "All values that are not-equal-to 'XYZ'". This would be "any value that is not 'XYZ'":

SELECT * FROM SM_STPD_TB
WHERE NOT SM_STPD_CD = 'XYZ'

And as usual, this isn't just Oracle, it's all SQL.

The problem is that relational logic is not a two-valued, boolean logic. The middle is not excluded. It's not "you're either for us, or against us."

Suppose I asked you to list the names of all people in your family who prefer haggis to blood pudding. I think there's a fair chance that such a question would *not* be the same as asking for the names of all people who do not prefer blood pudding to haggis. There very likely are people who hate both concoctions equally; there very likely are people who have never tasted either and hence have no opinion.

monsterinc

Oh, what a mastermind this Alex is!

Not only that he has no experience using plain and simple SQL

he is shouting all over about it and how much Oracle sucks.

More suck to come on this stupid forum of ignorant people and

I'll be here when another great discovery from "I hate Oracle" geniuses

comes.

Ben_Hutchings

@rpresser said:

Alex Papadimoulis wrote:
Whoops -- typo in the original post. replace AND with OR.

But still, it's pretty stupid that you need to put that clause in anyway. Any value that is not 'XYZ' should include values that are NULL as well.

But you didn't ask for "any value that is not 'XYZ'". You asked for "All values that are not-equal-to 'XYZ'". This would be "any value that is not 'XYZ'":

 SELECT * FROM SM_STPD_TB
WHERE NOT SM_STPD_CD = 'XYZ'



And as usual, this isn't just Oracle, it's all SQL.



The problem is that relational logic is not a two-valued, boolean logic. The middle is not excluded. It's not "you're either for us, or against us." 



Suppose I asked you to list the names of all people in your family who prefer haggis to blood pudding. I think there's a fair chance that such a question would *not* be the same as asking for the names of all people who do not prefer blood pudding to haggis. There very likely are people who hate both concoctions equally; there very likely are people who have never tasted either and hence have no opinion.

Ben_Hutchings

@rpresser said:

The problem is that relational logic is not a two-valued, boolean logic. The middle is not excluded. It's not "you're either for us, or against us."

I'll have to take issue with your use of "relational" there. Relational theory doesn't have NULLs, because NULL is a non-value indicating a missing relation. SQL includes NULLs as a kluge to allow incompletely normalised data, and I agree that that results in a need for three-valued logic (among many other unpleasant complications).

On an unrelated note, the editing widget is broken in Firefox. Pressing "Post" always posts the original text - hence my last comment and the many others that quote comments in full but add nothing.

Ross_Presser

@Ben Hutchings said:

I'll have to take issue with your use of "relational" there. Relational theory doesn't have NULLs, because NULL is a non-value indicating a missing relation. SQL includes NULLs as a kluge to allow incompletely normalised data, and I agree that that results in a need for three-valued logic (among many other unpleasant complications).

D'oh! You are absolutely right. My own brain must have some NULLs in it.

mkbosmans

Look at the explanation of another really nice feature of Oracle:

the null handling of a varchar2 column

http://weblogs.sqlteam.com/brettk/archive/2005/01/05/3943.aspx

Foon

"In all database models that I know, NULL means there is no data in the field."

It means a bit more than that. Relational database theory is based on the idea that a table row is a set of "tuples". IOW: a database row is (mathematicaly speaking) a mapping of keys (the column names) to values (the contents).

A NULL does not mean "the value of this column is null". It means "this column is not there at all". It means that the map that is that row does not even have that column name as a key.

So, yes, a NULL does mean a mystical unknown. It's not a value - it is the absence of a value.

x002548

@mkbosmans said:

Look at the explanation of another really nice feature of Oracle:
the null handling of a varchar2 column
http://weblogs.sqlteam.com/brettk/archive/2005/01/05/3943.aspx

Hey, Thanks for the plug...noticed the trackbacks...