Oracle and NULL



  • <font style="BACKGROUND-COLOR: #efefef">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.
    </font>



  • <font style="BACKGROUND-COLOR: #efefef">Please check what you write before you post - shouldn't it be "return all rows where SM_STPD_CD is NOT 'XYZ'?
    </font>



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



  • 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.



  • 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.



  • 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!



  • 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...



  • yep...see my first post.



  • 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.



  • <font face="Verdana">Well you can always use NVL...

    </font><font style="background-color: rgb(239, 239, 239);">SELECT * FROM SM_STPD_TB
      WHERE NVL(SM_STPD_CD, '') != 'XYZ'
    </font><font style="background-color: rgb(239, 239, 239);">
    </font><font face="Verdana">Or even better, the remarkably obtuse DECODE statement...

    </font><font style="background-color: rgb(239, 239, 239);">SELECT * FROM SM_STPD_TB
      WHERE DECODE(SM_STPD_CD, NULL, 'ZYX', SM_STPD_CD) != 'XYZ'

    </font><font face="Verdana">There's nothing more likely to generate a WTF than multiple embedded decode statements!</font>



  • @Frodo said:

    <font face="Verdana">Well you can always use NVL...

    </font><font style="BACKGROUND-COLOR: rgb(239,239,239)">SELECT * FROM SM_STPD_TB
      WHERE NVL(SM_STPD_CD, '') != 'XYZ'
    </font><font style="BACKGROUND-COLOR: rgb(239,239,239)">
    </font>


    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.




  • <font face="Verdana">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.
    </font>



  • @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'":
     
    <FONT style="BACKGROUND-COLOR: #efefef">  SELECT * FROM SM_STPD_TB
      WHERE NOT SM_STPD_CD = 'XYZ'</FONT>
    <FONT style="BACKGROUND-COLOR: #efefef"></FONT> 
    <FONT style="BACKGROUND-COLOR: #efefef">And as usual, this isn't just Oracle, it's all SQL.</FONT>
    <FONT style="BACKGROUND-COLOR: #efefef"></FONT> 
    <FONT style="BACKGROUND-COLOR: #efefef">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." </FONT>
    <FONT style="BACKGROUND-COLOR: #efefef"></FONT> 
    <FONT style="BACKGROUND-COLOR: #efefef">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.</FONT>


  • 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.

     

     

     



  • @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'":
     
    <FONT style="BACKGROUND-COLOR: #efefef">  SELECT * FROM SM_STPD_TB
      WHERE NOT SM_STPD_CD = 'XYZ'</FONT>
    <FONT style="BACKGROUND-COLOR: #efefef"></FONT> 
    <FONT style="BACKGROUND-COLOR: #efefef">And as usual, this isn't just Oracle, it's all SQL.</FONT>
    <FONT style="BACKGROUND-COLOR: #efefef"></FONT> 
    <FONT style="BACKGROUND-COLOR: #efefef">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." </FONT>
    <FONT style="BACKGROUND-COLOR: #efefef"></FONT> 
    <FONT style="BACKGROUND-COLOR: #efefef">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.</FONT>


  • @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.



  • @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.


  • <font face="Tahoma">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




    </font>



  • "<FONT style="BACKGROUND-COLOR: #efefef">In all database models that I know, NULL means there is no data in the field."</FONT>

    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.



  • @mkbosmans said:

    <FONT face=Tahoma>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

    </FONT>

     

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

     

     


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.