This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: Nov 28, 2012 1:32 AM by BluShadow Go to original post RSS
  • 15. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    KBH wrote:
    The point about NULL is that it is "unknown".
    Yes, I got that on Day 1.
    select value1 / nullif(value2, 0) division
    from my_table;
    Yet another cute tool. But how often does one need it? It is pretty easy to code:
    select value1 / value2 division
    from my_table
    where value2 <> 0;
    BluShadow's example:
    select x, dump(x) as dmp from x;
    You also know that a null value in a column takes up one byte in a record in a table. So even the code behind dump(x) has to jump through hoops to return "NULL".

    BluShadow:
    Your example of the number of students enrolled in class is excellent. I have worked in educational information systems for nearly my entire career -- 35 years, 20 before SQL, and now 15 with Oracle software. We set up records for an upcoming term, it has never been a problem not knowing how many students may eventually enroll in a course. The default number is zero, and as students enroll, the number climbs. Setting the initial value null would just create trouble for the sql and pl/sql software using the data. Nobody cares to even ask what the number will be -- they know it is an unknown, and would be worthless asking.

    For the 20 years I worked in information systems before moving to a SQL database, there was NEVER a case where the question of unknown values was an issue. If there were ever a time that a data column needed to be set as unknown, it would have been set to zero (for a number) or blank for text, and an associated indicator or status value could be set to indicate its special ("unknown") state. But needing that special "unknown" state never came up, that I can recall.

    The problem has been the past 15 years, working with a SQL database. Three-value logic and the non-binary treatment of null has always created a hurdle that our developers had to be careful and be wary of lest they write code that would take the wrong logic branch if they forgot about the null problem. We have to analyze the complex pl/sql conditions to make sure each value that might be null would be handled properly. It is tedious and time-consuming, causing a drag on productivity.

    People can quote database theory and ANSI SQL Standard stuff all day long, but it does NOT address the problem that all this special treatment of nulls has created for developers who have to write and maintain packages with hundreds of procedures and many thousands of lines of code. "Ora DBA", the OP, knows what I am writing about. I just don't understand why so few others do.
  • 16. Re: SQL - NULL vs IS NULL?
    rp0428 Guru
    Currently Being Moderated
    >
    People can quote database theory and ANSI SQL Standard stuff all day long, but it does NOT address the problem that all this special treatment of nulls has created for developers who have to write and maintain packages with hundreds of procedures and many thousands of lines of code. "Ora DBA", the OP, knows what I am writing about. I just don't understand why so few others do.
    >
    The 'few others' DO understand that, at least I do.

    The question wasn't why is the world the way it is. The question was
    >
    is there a workaround?
    >
    That is the question we are addressing in this thread. Why ANSI or Oracle defined or implemented things the way they did may be related but it is a different issue altogether. Only the respective authors could provide information about the real reasons; anything else is just conjecture.

    Of course this thread 'does NOT address the problem that all this special treatment of nulls has created' nor should it. Create a new thread in the General Database forum if you want to discuss the issue. That way it won't take away the focus from what OP was asking and the help we are trying to provide them.

    As I said above
    >
    Further discussion of Oracle's rules and alternatives IS appropriate for the current thread. But any discussion of what anyone might do in another language or environment is of no value in addressing OPs issue or in providing any 'work around' and so can only distract people from focusing solely on OPs question. I'm not trying to stifle commentary just trying to make it easier for people reading the thread to understand the issue OP presented and Oracle-related responses to that issue. Back-and-forth comments about side issues, even if they might be related, detracts from that focus.
  • 17. Re: SQL - NULL vs IS NULL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Steve Cosner wrote:

    The problem has been the past 15 years, working with a SQL database. Three-value logic and the non-binary treatment of null has always created a hurdle that our developers had to be careful and be wary of lest they write code that would take the wrong logic branch if they forgot about the null problem. We have to analyze the complex pl/sql conditions to make sure each value that might be null would be handled properly. It is tedious and time-consuming, causing a drag on productivity.
    There are a couple of issues here Steve.

    The SQL database is not the problem when it comes to three-value logic. There is the not null constraint. There is the check constraint. The tools are there to ensure (two-value) boolean data values. The fact that these tools are not used, the data model is designed and/or implemented incorrectly, is not the fault of the SQL database.

    PL/SQL is a programming language. It supports boolean data types like other programming languages. A boolean variable can be constrained with a not null, forcing it to only be true or false. Again, if this is not a PL/SQL problem - the issue is developers designing and writing shoddy code.

    Three-value logic is an artefact of the designer/developer. Not the SQL database. Not the PL/SQL programming language.
  • 18. Re: SQL - NULL vs IS NULL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Example: enforcing a boolean to be only ever true or false
    SQL> declare
      2          b       boolean not null;
      3  begin
      4          if b then
      5                  dbms_output.put_line( 'true' );
      6          else
      7                  dbms_output.put_line( 'false' );
      8          end if;
      9  end;
     10  /
            b       boolean not null;
                    *
    ERROR at line 2:
    ORA-06550: line 2, column 4:
    PLS-00218: a variable declared NOT NULL must have an initialization assignment
    
    
    SQL> 
    The same approach applies to other data types used in PL/SQL. The NULL issue is, the vast majority of times, self inflicted by the developer. PL/SQL provides the features required for not having to deal with nulls when the data and programming logic need not to deal with null states of variables.
  • 19. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    Billy V, that is a good example of the extra work required to bypass or disable 3-value logic. It takes extra code to add the "not null" phrase to the boolean declaration. That is the nonsense that I referred to as a bad practical joke in my first post in this thread. Sure, good coding practice will enable a smooth-working program. But the fact that we need to add those extra pieces makes the job more difficult. And please give developers a break -- when trying to write code for a really complex situation, we sometimes just plain miss something. Labeling it "shoddy code" is pretty harsh.

    Playing with your PL/SQL example a bit, check this out:
    SQL> Declare
      2    t1  Varchar2(1) := 'b';
      3    t2  Varchar2(1) := '';
      4    b   boolean  not null := True;
      5  Begin
      6    b := ( t1 = t2 );
      7    If b then
      8      dbms_output.put_line('True');
      9    Else
     10      dbms_output.put_line('False');
     11    End if;
     12  End;
     13  /
    Declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 6
    Pretty ridiculous message, don't you think?

    In my coding, I declare a boolean only in a few places. It is more efficient to just use an IF statement to determine a True or False condition (...oops, there I did it again! True or False or Null). It is just plain annoying that in every if condition, I MUST forget about the actual problem I am trying to write code for, and analyze the condition with an experts eye, to make sure it is coded to work properly in a three-value logic situation. It always takes more time.

    What would really make progress in this area would be special operators that would automatically handle null, so that ( A = null ) would return true or false rather than always null. Rather than forcing the use of IS NULL or NVL(), create an equality operator that would switch to two-value logic. I'd bet PL/SQL developers would abandon the three-value mode 99% of the time!

    As Ora DBA wrote:
    I need to refer this in a PL/SQL procedure and prefer to use =NULL, is there a workaround?
    If there were something better than NVL and IS NULL, it would save many developers a lot of grief, not to mention increase productivity.
  • 20. Re: SQL - NULL vs IS NULL?
    BluShadow Guru Moderator
    Currently Being Moderated
    Steve Cosner wrote:
    BluShadow:
    Your example of the number of students enrolled in class is excellent. I have worked in educational information systems for nearly my entire career -- 35 years, 20 before SQL, and now 15 with Oracle software. We set up records for an upcoming term, it has never been a problem not knowing how many students may eventually enroll in a course. The default number is zero, and as students enroll, the number climbs. Setting the initial value null would just create trouble for the sql and pl/sql software using the data. Nobody cares to even ask what the number will be -- they know it is an unknown, and would be worthless asking.

    For the 20 years I worked in information systems before moving to a SQL database, there was NEVER a case where the question of unknown values was an issue. If there were ever a time that a data column needed to be set as unknown, it would have been set to zero (for a number) or blank for text, and an associated indicator or status value could be set to indicate its special ("unknown") state. But needing that special "unknown" state never came up, that I can recall.
    I was simply giving a theoretical 'real world' example to explain the fact that in some businesses there may be a need to differentiate a positively known default value from an unknown value.

    We have such examples in our business, though I cannot give specific examples on the public forums. We do have a need by our business where things have to be recorded as positively unknown (meaning the information has been sought, but is not known) compared to data that has yet to be recorded (which remains NULL).

    Of course you're free to be pedantic about it because my theoretical example is in your field of expertise, but that completely misses the point of what was being explained. I'm happy for you that you've never had a need to record nulls or your business doesn't care about such things... but your business is not everyone elses, and other businesses do have such a need, without having to create additional fields to flag if a field is unknown or not (with the amount of data we store in our systems we'd end up with hundreds of flag columns on the tables, and no space on the application screens to put them all).
  • 21. Re: SQL - NULL vs IS NULL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Steve Cosner wrote:
    Billy V, that is a good example of the extra work required to bypass or disable 3-value logic. It takes extra code to add the "not null" phrase to the boolean declaration.
    Extra code, or a total lack of the proper understanding of the programming language being used?

    Do we need to delve into the idiosyncrasies of Cobol, Pascal, C and other languages to illustrate that PL/SQL is not unique in this respect?
    Sure, good coding practice will enable a smooth-working program. But the fact that we need to add those extra pieces makes the job more difficult.
    Beg to differ. There are 2 basic requirements when writing a program language statement:
    a) know WHAT that statement does
    b) know WHY that statement is needed

    And this is true for all programming languages.

    The problem is that Mr Clever Developer assumes he/she knows what a statement does - without confirming that it is the case. This part of the learning curve being seen as irrelevant. Why understand that language's statement when it seems to be just like that of another language?
    And please give developers a break -- when trying to write code for a really complex situation, we sometimes just plain miss something. Labeling it "shoddy code" is pretty harsh.
    Harshness is what is needed IMO. I wished I had learned these type of lessons early when I started development as a junior programming. As learning these lessons the hard way was significantly more painful - I would have preferred getting a "harsh" comment instead, pointing out what I did wrong and calling my shoddy code for exactly what it was...
    Playing with your PL/SQL example a bit, check this out:
    Invalid example. As you demonstrated the exact issue I referred to above. Know WHAT you write. There are no empty strings in PL/SQL. As a string with no character values indicates a variable WITHOUT a value. Thus it indicates a null variable state.

    And no, this type of thing is not unique to PL/SQL. Dealing with other languages and interfaces require one to deal with other states besides just NULL and NOT NULL - like isNull and isEmpty. I recall using the COM interface of Microsoft SAPI5 and having to deal with 3 different variable states (6 in total taking the negatives of these into consideration too) when using a COM variable.

    So I honestly do not understand what the big deal is about treating variables, all PL/SQL variables, as either having a value (not null), or no value (null). This is consistent behaviour in PL/SQL.

    Arguing that it is not like another language's implementation, is like arguing that C/C++ use <i>i++</i> and not the clearer Cobol statement of ADD 1 TO i.
  • 22. Re: SQL - NULL vs IS NULL?
    kendenny Expert
    Currently Being Moderated
    Null for numbers or dates makes sense the way Oracle handles it. However for strings, there should be a distinction made between a null and a zero length string.
  • 23. Re: SQL - NULL vs IS NULL?
    BluShadow Guru Moderator
    Currently Being Moderated
    kendenny wrote:
    Null for numbers or dates makes sense the way Oracle handles it. However for strings, there should be a distinction made between a null and a zero length string.
    Well don't hold your breath waiting for that to change in Oracle. If they changed it to allow 0 length strings as being different from null, it would break many people's applications where they have used an empty string to define a null value.
  • 24. Re: SQL - NULL vs IS NULL?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    kendenny wrote:
    Null for numbers or dates makes sense the way Oracle handles it. However for strings, there should be a distinction made between a null and a zero length string.
    Yes. However, a string is still a scalar data type. It is not an array of characters (or is it?). So why should a scalar number be treated differently from a scalar string?

    Also consider the logical meaning of an empty versus null variable? Logically, what is the difference in meaning between an empty string and a null string? What meaning does an empty string have, that a null string does not?

    And before answering that, think about why empty string and null string need to be used to convey that logic, and not a proper variable that reflects the different states a variable can have (e.g. flag variable indicating second name string variable can be person-has-no-second-name, second-name-for-person-not-listed, second-name-status-unknown, second-name-specified, etc)?

    One of the important characteristics of programming languages is consistency. You may think that Oracle SQL and PL/SQL should provide null support differently (and perhaps in a more flexible do-not-have-to-think-about-it way). But you cannot argue that is does treat nulls in a consistent way.
  • 25. Re: SQL - NULL vs IS NULL?
    William Robertson Oracle ACE
    Currently Being Moderated
    I do quite like your argument. However if NULL = NULL, then 1 &lt;> NULL and so where we currently code
    and somecol &lt;> 'X'
    under your simplified regime we would have to code
    and somecol not in ('X', NULL)
    which is more code :)

    Not to mention what is supposed to happen to
    and somecol > someothercol
    I suppose we could have a convention that NULL sorted lower than anything else - however that would arguably be inconsistent with the current "nulls first"/"nulls last" syntax of "order by" where we can specify it either way.
    Steve Cosner wrote:
    select value1 / nullif(value2, 0) division
    from my_table;
    Yet another cute tool. But how often does one need it? It is pretty easy to code:
    select value1 / value2 division
    from my_table
    where value2 &lt;> 0;
    Not always - usually I want all the rows but I don't want it to break for zeroes (most recent example that comes to mind is reporting cursor performance from dba_hist_sqlstat and dividing elapsed time by executions to get average time per execution).

    An additional operator is an interesting idea ("<tt>==</tt>" for example) but arguably it just complicates things and we'd have questions here about the difference between "<tt>=</tt>" and "<tt>==</tt>" and why code didn't work as expected because people had picked the wrong one.

    Edited by: William Robertson on Nov 27, 2012 5:03 PM
    Added the missing word 'like' :)
  • 26. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    BluShadow wrote:
    I was simply giving a theoretical 'real world' example to explain the fact that in some businesses there may be a need to differentiate a positively known default value from an unknown value.

    We have such examples in our business, though I cannot give specific examples on the public forums.
    I am sorry to have targeted the example. In reality, my applications make full use of null columns. Many columns are left null if a value is not necessary, does not apply, was not entered -- whatever the reason or reasons, a null value in a column is quite useful.

    What it really comes down to is this:

    I would most definitely like to have a "more flexible +do-not-have-to-think-about-it+" tool where relational operators are used. Current relational operators are:  =   &lt;   >   &lt;&gt;   <=   and   >=

    William Robertson wrote:
    An additional operator is an interesting idea ("==" for example) but arguably it just complicates things and we'd have questions here about the difference between "=" and "==" and why code didn't work as expected because people had picked the wrong one.
    "==" is a start. But I would suggest using a single character in addition to the existing relational operators. We already have the well known ":=".  For relational operators returning a two-value (only True or False) result, something like these would be easiest to use:  +=   +&lt;   +>   +&lt;>   +<=   and   +>=     ...Or maybe these:   .=   .&lt;   .>   .&lt;&gt;   etc.
    A single additional character, either preceding or following would be easiest to use.

    I would bet that developers would really latch onto the new operators, if they could forget about the more cumbersome NVL() and IS/IS NOT NULL.

    I think there would be less confusion than the need for those workarounds already creates.
  • 27. Re: SQL - NULL vs IS NULL?
    BluShadow Guru Moderator
    Currently Being Moderated
    Steve Cosner wrote:
    "==" is a start. But I would suggest using a single character in addition to the existing relational operators. We already have the well known ":=".  For relational operators returning a two-value (only True or False) result, something like these would be easiest to use:  +=  
    Already used by other languages as an assignment operator e.g.
    i+=1;
    to add 1 to i.
    +&lt;   +>   +&lt;>   +<=   and   +>=    
    + is arithmentic so not suitable for comparison operators.

    ...Or maybe these:   .=   .&lt;   .>   .&lt;&gt;   etc.

    To dotty, making code messy.
    A single additional character, either preceding or following would be easiest to use.
    Why?
    I would bet that developers would really latch onto the new operators, if they could forget about the more cumbersome NVL() and IS/IS NOT NULL.
    Why are they cumbersome? They make it readable and clear what it means. Far clearer than using full stops or some other arbitrary character. Such odd syntax takes the language back to being like C or Java etc. so defeats the purpose of being a readable and clear syntax.
    I think there would be less confusion than the need for those workarounds already creates.
    I think it's only you that sees them as 'workarounds'. To me they're just part of the language and part of how we deal with null values, just as we use different operators to concatenate strings as opposed to adding together numbers (unlike some other languages where "+" is used to add numbers or concatenate strings). I'm not having to work around anything by using the Null specific operators.
  • 28. Re: SQL - NULL vs IS NULL?
    DBA112 Newbie
    Currently Being Moderated
    Could someone please help me with the code? All columns are varchar2

    Below is the DDL of the procedure and the way I'm executing it. How can I use IS NULL inside procedure, instead of = NULL?
    CREATE OR REPLACE PROCEDURE APPS.RA_CUSTOMER_TRX_ALL_PROC(ATTRIBUTE2_1 IN VARCHAR2, TRX_NUM IN VARCHAR2, ATTRIBUTE2_2 IN VARCHAR2)
    IS 
    BEGIN
    UPDATE APPS.RA_CUSTOMER_TRX_ALL
    SET ATTRIBUTE2 = ATTRIBUTE2_1
    WHERE TRX_NUMBER = TRX_NUM
    AND ATTRIBUTE2 = ATTRIBUTE2_2;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' row(s) updated');
    END RA_CUSTOMER_TRX_ALL_PROC;
    /
    
    
    SQL> EXEC APPS.RA_CUSTOMER_TRX_ALL_PROC(&ATTRIBUTE2_1, &TRX_NUM, &ATTRIBUTE2_2);
    
    Enter value for attribute2_1: 'DUMMY'
    Enter value for trx_num: '100132'
    Enter value for attribute2_2: NULL  (This should be IS NULL)
    0 row(s) updated (This is not right, 1 row should have been updated actually)
    
    PL/SQL procedure successfully completed.
  • 29. Re: SQL - NULL vs IS NULL?
    kendenny Expert
    Currently Being Moderated
    First off. Change your parameter names to something other than the column names in the table.
    Second. Pick a value that the column attribute_2 can never have, say '~~~~~' then change
    AND ATTRIBUTE2 = ATTRIBUTE2_2
    to
    AND NVL(attribute_2,'~~~~~') = NVL(p_attribute_2,'~~~~~')
    I used p_attribute_2 as the parameter. It must have a different name than the table column.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points