This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: Nov 28, 2012 1:32 AM by BluShadow RSS

SQL - NULL vs IS NULL?

DBA112 Newbie
Currently Being Moderated
Dear SQL Experts,

Here's is the problem, need your help understanding the fix:

I'm making an update in below 2 ways, all the columns referred below are varchar2. IS NULL works fine, but =NULL doesn't. I need to refer this in a PL/SQL procedure and prefer to use =NULL, is there a workaround?
SQL> UPDATE APPS.RA_CUSTOMER_TRX_ALL
  2  SET ATTRIBUTE2 = 'DUMMY'
  3  WHERE TRX_NUMBER = '100132'
  4  AND ATTRIBUTE2 IS NULL;

1 row updated.

SQL> rollback;

Rollback complete.

SQL> UPDATE APPS.RA_CUSTOMER_TRX_ALL
  2  SET ATTRIBUTE2 = 'DUMMY'
  3  WHERE TRX_NUMBER = '100132'
  4  AND ATTRIBUTE2 = NULL;

0 rows updated.
  • 1. Re: SQL - NULL vs IS NULL?
    ranit B Expert
    Currently Being Moderated
    NULL should NEVER be checked using '='

    '=' is only when you want to check for a particular value.
    And, no value called 'NULL' exists.

    Even NULL is never equal to NULL.

    We always go for IS NULL or IS NOT NULL

    Hope this Helps.
    Ranit B.
  • 2. Re: SQL - NULL vs IS NULL?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Ora DBA wrote:
    Dear SQL Experts,

    Here's is the problem, need your help understanding the fix:

    I'm making an update in below 2 ways, all the columns referred below are varchar2. IS NULL works fine, but =NULL doesn't. I need to refer this in a PL/SQL procedure and prefer to use =NULL,
    Why? What's wriong with IS NULL?
    is there a workaround?
    Yes, but whatever you find wrong with IS NULL might apply to the alternatives, too.
    SQL> UPDATE APPS.RA_CUSTOMER_TRX_ALL
    2  SET ATTRIBUTE2 = 'DUMMY'
    3  WHERE TRX_NUMBER = '100132'
    4  AND ATTRIBUTE2 IS NULL;
    
    1 row updated.
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> UPDATE APPS.RA_CUSTOMER_TRX_ALL
    2  SET ATTRIBUTE2 = 'DUMMY'
    3  WHERE TRX_NUMBER = '100132'
    4  AND ATTRIBUTE2 = NULL;
    
    0 rows updated.
    = returns TRUE if the values on either side of it are the same.
    NULL is not a value. In fact, NULL is just the opposite; it's the absence of any value.
    x = NULL
    is never TRUE, even if x is NULL. (It's never FALSE either; x = NULL is always UNKNOWN, regardless of what x is.)

    When comparing strings, you can concatenate a constant. For example
    a || 'x' = b || 'x'
    returns TRUE if a and b are both NULL (or if they are equal).
    DECODE (a, b, 1, 0)
    returns 1 if a and b are both NULL (or if they are equal), and 0 otherwise.

    NVL and COALESCE are often used for comparing variables that might be NULL, especially if you know a value that cannot possibly occur in those variables.

    LNNVL and NVL2 are also handy for dealing with NULLs in certain situations.
  • 3. Re: SQL - NULL vs IS NULL?
    BluShadow Guru Moderator
    Currently Being Moderated
    You cannot use =NULL because it won't work.

    If you consider NULL to mean "unknown value", then how can anything be equal to an unknown value? Just as you cannot test if something is not equal to an unknown value? The result is always unknown. That's why there are conditioanl operators IS and IS NOT especially for NULL.

    Why do you want to use "="?
  • 4. Re: SQL - NULL vs IS NULL?
    AlbertoFaenza Expert
    Currently Being Moderated
    Ora DBA wrote:
    Dear SQL Experts,

    Here's is the problem, need your help understanding the fix:

    I'm making an update in below 2 ways, all the columns referred below are varchar2. IS NULL works fine, but =NULL doesn't. I need to refer this in a PL/SQL procedure and prefer to use =NULL, is there a workaround?
    SQL> UPDATE APPS.RA_CUSTOMER_TRX_ALL
    2  SET ATTRIBUTE2 = 'DUMMY'
    3  WHERE TRX_NUMBER = '100132'
    4  AND ATTRIBUTE2 IS NULL;
    
    1 row updated.
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> UPDATE APPS.RA_CUSTOMER_TRX_ALL
    2  SET ATTRIBUTE2 = 'DUMMY'
    3  WHERE TRX_NUMBER = '100132'
    4  AND ATTRIBUTE2 = NULL;
    
    0 rows updated.
    Hi,
    If an operator receives a null operator, the result is always null.
    ATTRIBUTE2  = NULL result NULL
    ATTRIBUTE2  != NULL result NULL
    ATTRIBUTE2  IS NULL result TRUE or FALSE
    NULL must be used with IS NULL. Unless you use NVL function to force a different value when it is null

    Check here "Null", version 9i

    Regards.
    Al
  • 5. Re: SQL - NULL vs IS NULL?
    SomeoneElse Guru
    Currently Being Moderated
    I need to refer this in a PL/SQL procedure and prefer to use =NULL, is there a workaround?
    Well, it doesn't matter if you prefer it. It won't work.

    NULL is not a value it's like a state. That's why you must use the IS [NOT] NULL syntax.

    If you prefer to use =, you can try this:

    and nvl(attribute2,'x') = 'x'

    But then you run the risk of a false positive in the case where attribute2 really is an 'x'.
  • 6. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    BluShadow wrote:
    If you consider NULL to mean "unknown value", then how can anything be equal to an unknown value?
    As soon as you set a variable := NULL, it is a known, reproducible, repeatable situation. It is NOT "unknown" because every time you work with it, or compare it using "IS NULL", store it in a column in a database table, retrieve it from the row in the table, you will ALWAYS get the same result.

    Using the examples Frank K provided, it is pretty obvious Oracle is treating null as a known value:
    This condition is true when a and b are null:
    a || 'x' = b || 'x'
    This returns 1 when a and b are null:
    DECODE (a, b, 1, 0)
    If you select and order by from a table where some values are null, Oracle always treats the null values as higher than any other value, unless you specify "ORDER BY Col_x NULLS FIRST"

    You can sum a column of numbers, and all null values are treated as zero.

    So in order to do ANYTHING meaningful with data where some values are null, then a null value (or null "state") is a known entity.

    Database theorists may have lots of fun philosophically when discussing null values as something unknown, but when the rubber meets the road, in actual day-to-day SQL and PL/SQL programming, pretending null is unknown, and forcing three-value logic on everyone is a stupid practical joke._

    I've worked with SQL now for 15 years, and can handle three-value logic and nulls as well as anyone else, but it would sure make my job and others' easier if there were a high-level switch, as in Alter Session, or relational operators, (Examples: .= or =. or <.> ) where we could tell Oracle to treat nulls as known values.
  • 7. Re: SQL - NULL vs IS NULL?
    Stew Ashton Expert
    Currently Being Moderated
    Steve Cosner wrote:
    As soon as you set a variable := NULL, it is a known, reproducible, repeatable situation. It is NOT "unknown" because every time you work with it, or compare it using "IS NULL", store it in a column in a database table, retrieve it from the row in the table, you will ALWAYS get the same result.
    I'm not sure I follow you here. "Known" does not mean the same thing as "deterministic".
    I'm still not sure I follow you here: see? ;)
    This condition is true when a and b are null:
    a || 'x' = b || 'x'
    Yes, Oracle admits in its documentation that it treats a NULL varchar2 as equivalent to an empty string.
    This returns 1 when a and b are null:
    DECODE (a, b, 1, 0)
    DECODE is indeed an exception, which is why I prefer it when doing equality comparisons on NULLable values.
    If you select and order by from a table where some values are null, Oracle always treats the null values as higher than any other value, unless you specify "ORDER BY Col_x NULLS FIRST"
    True, and null values are considered equivalent in GROUP BY and PARTITION BY clauses.
    You can sum a column of numbers, and all null values are treated as zero.
    Wrong.
    select sum(x) from (
      select cast(null as number) x from dual
      connect by level <= 9
    );
    X
    --
    (null)
    
    select avg(x) from (
      select cast(null as number) x from dual
      connect by level <= 9
      union all
      select 1 from dual
    );
        AVG(X)
    ----------
             1
    See, the NULL values are ignored.

    It's not so cut and dried as you say - which is probably why it's frustrating.
  • 8. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    "Known" does not mean the same thing as "deterministic".
    We quibble over semantics. "NULL value" is an oxymoron, ...pretty ugly, isn't it. ;-)
    DECODE is indeed an exception, which is why I prefer it when doing equality comparisons on NULLable values.
    Ah, you prefer something that treats null as a known value, too.
    It's not so cut and dried as you say - which is probably why it's frustrating.
    So we agree it is quite frustrating. Nice examples, by the way, illustrating more of mess.

    Can you (or anyone) give an example or describe a situation where they treat null as an unknown in a meaningful and useful manner in their real-world programming business logic, where it is definitely easier to code and handle than if null were just another value?
  • 9. Re: SQL - NULL vs IS NULL?
    rp0428 Guru
    Currently Being Moderated
    >
    Can you (or anyone) give an example or describe a situation where they treat null as an unknown in a meaningful and useful manner in their real-world programming business logic, where it is definitely easier to code and handle than if null were just another value?
    >
    Please don't HIJACK another user's thread. Although the original comments were related to OPs issue that question is not.

    If you have a question or issue you want to discuss create your own thread and post it.
  • 10. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    Please don't HIJACK another user's thread.
    The OP wrote:
    IS NULL works fine, but =NULL doesn't. I need to refer this in a PL/SQL procedure and prefer to use =NULL, is there a workaround?
    The point is, the OP is similarly frustrated as I am with the problem that the easy-to-read "A = Null" or "X &lt;> Y" conditions cannot be used in PL/SQL without creating buggy code. I think it would help him as well as me, if we could understand why it is so necessary that we go to great trouble to work around those rules.
  • 11. Re: SQL - NULL vs IS NULL?
    rp0428 Guru
    Currently Being Moderated
    This is the question that is hijacking the thread and should be ask in your own thread if you want to discuss it.
    >
    Can you (or anyone) give an example or describe a situation where they treat null as an unknown in a meaningful and useful manner in their real-world programming business logic, where it is definitely easier to code and handle than if null were just another value?
    >
    As for
    >
    I think it would help him as well as me, if we could understand why it is so necessary that we go to great trouble to work around those rules.
    >
    There isn't any 'great trouble' involved; just use IS NULL or IS NOT NULL as other respondents have said.

    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.
  • 12. Re: SQL - NULL vs IS NULL?
    user346369 Expert
    Currently Being Moderated
    Thank yew, Mr Thread Police, for your off topic comments. Your debating what is on or off topic is completely off the topic.

    At least you have an opinion on the topic:
    There isn't any 'great trouble' involved; just use IS NULL or IS NOT NULL as other respondents have said.
    Unfortunately, your tone of "That's the rule, so just follow it" doesn't make it any easier for "Ora DBA" or anyone else to do our work more easily. But it does relate to your fretting about what is on or off topic... It seems that following the rules "because them are the rules" is more important than thinking outside the box to create better tools to get a job done more easily.

    It would help people understand the necessity to use IS NULL if we could see a good reason for forcing everyone into that box.
  • 13. Re: SQL - NULL vs IS NULL?
    Kim Berg Hansen Expert
    Currently Being Moderated
    The point about NULL is that it is "unknown".

    Whenever a comparison operator ( =, !=, >=, etc ) compares "unknown" to "something", it must evaluate that to "boolean null" (unknown), which is neither true nor false.

    Use case:

    Column order_status - has values of 1, 2 or 3 for 10% of the data, it is null for 90% of the data.
    You can select "where order_status = 1" and get 2% of the rows and "where order_status != 1" and get the 8% of the rows having values 2 or 3.
    The 90% rows evaluate the condition to "boolean null" and thus are never output in either of the two queries !!
    The 90% rows simply are not relevant for those two queries - they have an "unknown" order_status.
    If the = operator could compare to null (as you desire), then the query "where order_status != 1" would include the 90% rows where order_status is unknown.

    That is so in any database that complies to SQL standard. You may like it or not (and it is often discussed), but some reasonably smart guys have thought a great deal about NULL being "unknown" and they decided this is how it is going to be. So any database has special operators and special functions for those special cases where you wish to query "which are my unknown data".

    Personally I make use of the fact that NULL is unknown and operators give a NULL result when one of the operands are NULL:
    select value1 / nullif(value2, 0) division
      from my_table;
    Here I use one of the special functions to turn a zero into a NULL. That way I do not get division-by-zero error, but instead my result is also NULL because anything divided by NULL is also NULL.

    You should get the understanding that NULL is handled specially, almost all operators and functions return NULL if they get a NULL operand, and condition logic have three boolean values: false, true, null.
    Once you understand that, then you won't have trouble using IS NULL instead of = NULL ;-)

    It is just a matter of learning it once and forall - and if you wish it to be otherwise, you need to try and become a member of the ANSI SQL Standard committee ;-)
  • 14. Re: SQL - NULL vs IS NULL?
    BluShadow Guru Moderator
    Currently Being Moderated
    Steve Cosner wrote:
    Thank yew, Mr Thread Police, for your off topic comments. Your debating what is on or off topic is completely off the topic.
    Actually rp is correct, asking other questions on top of someone else's that take it away from the original question is hijacking, so please don't do that.
    At least you have an opinion on the topic:
    There isn't any 'great trouble' involved; just use IS NULL or IS NOT NULL as other respondents have said.
    Unfortunately, your tone of "That's the rule, so just follow it" doesn't make it any easier for "Ora DBA" or anyone else to do our work more easily. But it does relate to your fretting about what is on or off topic... It seems that following the rules "because them are the rules" is more important than thinking outside the box to create better tools to get a job done more easily.

    It would help people understand the necessity to use IS NULL if we could see a good reason for forcing everyone into that box.
    You may consider that thinking of NULL as a value rather than an unknown value is wrong, but if NULL is just another value as you suggest:
    As soon as you set a variable := NULL, it is a known, reproducible, repeatable situation. It is NOT "unknown" because every time you work with it, or compare it using "IS NULL", store it in a column in a database table, retrieve it from the row in the table, you will ALWAYS get the same result
    then you would be able to compare NULL to NULL with an equality or inequality operater and get a true or false result.

    However, it doesn't, because NULL is a way of stating that the value of that column (item or whatever it is), is not known, whereas if you knew the item was a known value you would set it to one.

    Let's take a simple example of a value stored on a table for e.g. number of students in a particular class.
    Now a school may set up the classes on it's database during the school holidays, so that they can prepare the timetables etc. and they may know the number of students in each class that are moving up from previous years to start in a new year at the school. But what about the lowest (first year) classes for the school? They don't know at that point how many students are going to be in those classes because they've got to wait for the new intake of students as the year starts. So what do they store in the number of students column for those classes? They could store a zero value, but is that correct? In principle you could code your application to say that a zero value means that the number of students is unknown, but then what if some classes are optional depending on what subjects students want to take, and there's a possibility that a class may genuinely have no students signed up to it, and you actually want to indicate there are zero students for that class, in which case your logic will break. So, instead, you store zero when you explicitly know there are zero students for that class, and you store null to indicate that the value is actually unknown. One is a positively known value indicating nothing and the other is a positively unknown value, because you don't know as yet what value needs to be stored.

    So your question is really, why can't we just test nulls using "=" because we should be able to treat null as a value in it's own right.

    Using the above example, if we wanted a report to show all classes that have, let's say, the same number of students in them as a chosen class, if the chosen class contained NULL students and NULL was treated as equalling NULL, then we would get all the other classes with NULL, but that may be wrong, because at present we cannot say that those classes have the same number of students. It may however, be what is required, but the fact that we don't treat it as equal by default means that the logic has to be explicitly coded to indicate that we want to treat these unknown values as the same thing. If they were treated as equal by default, then we wouldn't have the ability to treat them as seperate, but as the default is to treat NULL as not equal to NULL, then we have the ability to treat positively unknown values as seperate things.

    NULL is special, and should never be treated as being equal to anything, otherwise we can lose that ability to positively record that a value is unknown. If you started treating NULL as being equal to anything (including other NULLs) then you may as well do away with NULLs altogether and force people to have to store some known value, which may mislead people into thinking that the data has been positively known at the time of entry. i.e. we lose our ability to record something as 'unknown'.

    If you look at the storage of NULLs... e.g.
    SQL> select x, dump(x) as dmp from x;
    
             X DMP
    ---------- ----------------------------------------
             0 Typ=2 Len=1: 128
             1 Typ=2 Len=2: 193,2
               NULL
    a NULL value isn't even considered as having a size (length), because it's unknown. It's not stored as having zero length.

    And all that is the reason we have special comparison operators for NULL, so we can say "tell me if the value IS unknown" or "tell me if the value IS NOT unknown (is known)"

    Yes, it can introduce three state logic, and yes there are theoretical discussions and issues for people with that, and yes, it does mean that SQL doesn't deal with boolean values ^*1^ because of it.... but in truth.... I've never found it a problem in the 20 odd years I've been database programming in any of the languages I've programmed in, and considering it as "unknown" is a perfectly good basis for understanding it.

    ^*1 Yes, I know SQL standards do allow for Boolean values in the standard, but I've yet to come across any large rdbms vendor who has actually implemented boolean datatypes for SQL, because of the three state logic issue^

    Edited by: BluShadow on 22-Nov-2012 08:44
1 2 3 Previous Next

Legend

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