Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    difference between 'is null' and '= null'

    Both works, with different result. What is the logic ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Regular Coder
    Join Date
    May 2002
    Location
    Virginia, USA
    Posts
    621
    Thanks
    0
    Thanked 6 Times in 6 Posts
    You'll want to use IS NULL. = NULL is an arithmetic operator that can have some odd results:

    http://dev.mysql.com/doc/refman/5.0/...with-null.html

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Yes, I was convinced, until special case query(where I got a bit confused (- that '= null' does not exists at all.

    But see this dilema:
    tab1
    Code:
    id      
    1
    2
    tab2
    Code:
    id      varcharfiield
    1       null
    I would say here in condition :

    select * from tab2 where varcharfield = null'

    and in this case:

    select * from tab1 left join tab2 on tab1.id = tab2.id were tab1.id is null

    coz, in first case null is actualy there, in table, in second it is not, it is produced by query.

    Do you mean 'is null' should work in any upper case & in any case at all ?
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    In databases NULL means the value is unknown and therefore NULL == NULL is false since two unknowns are rarely going to be the same value if you ever find out what they are. So testing for = NULL will never find a match as NULL is never equal to anything.

    Once you move out of the database null has a completely different meaning and two fields set to null are equal. The NULL in the database doesn't extract as a meaningful value though because outside the database there is no corresponding value meaning unknown to give the field.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    Quote Originally Posted by felgall View Post
    Once you move out of the database null has a completely different meaning and two fields set to null are equal.
    NOT TRUE AT ALL!

    That may be true in PHP and JavaScript, but it is *NOT* true in some computer languages.

    This is a table of the comparison operators from VBScript, just for example:
    http://msdn.microsoft.com/en-us/libr...(v=vs.84).aspx

    VB and VB.NET are essentially the same: They use the "IS" operator for testing object equality, and two nulls will not match using anything except the IS operator. Pascal, in most variations I've seen, is the same.

    Only languages that don't seem to have a built in "isNull" or equivalent function/operator seem to treat a pair of nulls as being equal. It *IS* misleading to say that a pair of nulls are equal, for the reason Felgall gave: NULL means "unknown" and how can you know that a pair of unknowns are equal. I think this is a case where VB/VB.NET/VBScript do it better than JavaScript, C, C++, et al. If nothing else, how does it make sense to ask
    Code:
        if ( a <= null )
    or
        if  ( b > null )
    ??? Clearly, those are nonsense questions. But stupid JavaScript, in these conditions, treats null the same as zero. UGH! I think that's uglier than pig snot.
    Last edited by Old Pedant; 02-25-2013 at 07:13 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    NOT TRUE AT ALL!

    That may be true in PHP and JavaScript, but it is *NOT* true in some computer languages.
    I should have said CAN HAVE rather than HAS. (Just about all the languages I know treat null as meaning something completely different from what it means in any other language).

    Quote Originally Posted by Old Pedant View Post
    But stupid JavaScript, in these conditions, treats null the same as zero.
    That's why you should always use === and !== in comparisons in JavaScript. Null in JavaScript is a placeholder for an object that has not yet been created and so two placeholders can be considered to have the same value hence null === null in JavaScript but comparing to anything else using the correct comparison operators will return false.

    In JavaScript (null === 0) will return false because it does not treat them the same unless you are sloppy and use == which first tries to convert both values to the same type and both null and 0 can be converted to false giving false == false which is true..
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    YES! I think I wish that JS disallowed using null with == and !=. I certainly admit to sloppily using them way too often.

    I *REALLY* dislike code that does stuff such as
    Code:
    if ( ! document.getElementsByClassName ) { ... }
    because it implies that a method reference is a boolean value. I think you should *HAVE* to write
    Code:
    if ( document.getElementsByClassName === null ) { ... }
    Ah, well.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,639
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Old Pedant View Post
    I think you should *HAVE* to write
    Code:
    if ( document.getElementsByClassName === null ) { ... }
    There is an error in your logic there. The mention of document.getElementsByClassName would presumably create it if it doesn't exist. As it now exists without having had a value allocated it now has a value of undefined and not null. When using === undefined !== null.

    The correct way to write it is:

    Code:
    if (typeof document.getElementsByClassName === undefined ) { ... }
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    SORRY! Senior moment. I knew that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •