Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

SQL Queries using Null fields -- Must use "is null" or "is not null"?

  1. Sep 28, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi all,
    This is for SQL queries, sorry ran out of space in the title.
    Say we are doing a query that includes a null field/attribute. Must we always use either an " Is null" or
    " Is not null" statement?
    Thanks.
     
  2. jcsd
  3. Sep 28, 2015 #2
    Depends if that factor is important for your result. I don't think there is a requirement. Are you getting errors?
     
  4. Sep 28, 2015 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Hi Greg, no, I am learning the topic and the source material did not seem clear on this, i.e., on whether we should use, for the null field 'field' either "where field is null" , or " where field is not null".
     
  5. Sep 28, 2015 #4
    Again depends on what result you are after. If you want to factor in nulls for a field then use IS NULL, if you want to factor in records that have a field that is not null, then use IS NOT NULL. If you don't care, then don't add the clause :)
     
  6. Sep 28, 2015 #5

    jedishrfu

    Staff: Mentor

  7. Sep 29, 2015 #6

    jim mcnamara

    User Avatar

    Staff: Mentor

    NULL is not equal to anything - including itself - what jedishrfu is telling you. When you employ a where clause you are looking for a resultset that matches what you are looking for. There may be some 'yellow' values but you do have to say '!= yellow', just say '=red' instead.

    So you can ignore NULLS

    Except when the select statement uses some kind of function and operates on a field that can be NULL. You cannot have a NULL in a function, so you must either exclude those rows or wrap the result in a default NVL() function to transform the NULL to something usage.
    Code (Text):
    select UPPER(NVL(column_name, 'dummy')) from some_table;
    In this pretend example upper will barf if column_name returns a NULL, so you throw some 'dummy' value out there to feed it a usable value when the column_name is NULL.

    All indexed columns in a table should have the NOT NULL property. For the same reason.
     
  8. Sep 29, 2015 #7
    When people first get into computer science, they have trouble with certain special values. The concept that x = x is not necessarily true is hard to grasp, it's even worse, when that equal sign doesn't even resolve to true or false.

    In most computer languages NULL is a constant, and it does equal itself. That's not true in SQL.

    PHP
    Code (Text):

    echo (NULL == NULL) ? 'true' : 'false';
    echo (NULL === NULL) ? 'true' : 'false';
    echo (NULL == 0) ? 'true' : 'false';
    echo (NULL === 0) ? 'true' : 'false';
    echo (NULL == 1) ? 'true' : 'false';
    echo (NULL === 1) ? 'true' : 'false';
     
    Will spit out
    true
    true
    true
    false
    false
    false


    MySQL
    Code (Text):

    SELECT NULL = NULL;
    SELECT NULL IS NULL;
    SELECT NULL = 0;
    SELECT NULL = 1;
    SELECT 0 = 0;
    SELECT 0 = 1;
     
    Will spit out
    NULL
    1
    NULL
    NULL
    1
    0
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: SQL Queries using Null fields -- Must use "is null" or "is not null"?
  1. Using a proxy (Replies: 6)

  2. STL to use or not to (Replies: 1)

  3. Using a mac (Replies: 25)

  4. Using Linux (Replies: 1)

  5. Use of biometric in IT (Replies: 4)

Loading...