MySQL – NULL vs ” vs ‘NULL’


mysql_tipsToday, in one of my project I’ve to check empty fields.

The field name is: answer it’s type is TEXT and Default value is NULL

In my SQL query I was checked at first answer != NULL (if the answer is not null, i.e if the answer is not empty), But this was not showing the correct result.

Then I changed it to answer != ” ( i.e ” means empty string) then it showed the correct result.
Then I test with this answer != ‘NULL’, and it also showed the correct result.

mahmud ahsan

Computer programmer and hobbyist photographer from Bangladesh, lives in Malaysia. My [Business | Twitter | Linkedin | Instagram | Flickr | 500px]

You may also like

10 Comments

  • Giuseppe Maxia
    January 10, 2009 at 3:04 pm

    The “correct result” depends on what you consider correct.
    See this for example:

    create table tx (t text default null);
    Query OK, 0 rows affected (0.47 sec)

    insert into tx values (‘a’), (‘b’), (null), (”);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4 Duplicates: 0 Warnings: 0

    select * from tx where t != null;
    Empty set (0.05 sec)

    select * from tx where t is not null; # this is the “correct” result
    +——+
    | t |
    +——+
    | a |
    | b |
    | |
    +——+
    3 rows in set (0.00 sec)

    select * from tx where t != ‘null’;
    +——+
    | t |
    +——+
    | a |
    | b |
    | |
    +——+
    3 rows in set (0.00 sec)

    select * from tx where t != ”;
    +——+
    | t |
    +——+
    | a |
    | b |
    +——+
    2 rows in set (0.00 sec)

    Cheers

    Giuseppe

  • mahmudahsan
    January 10, 2009 at 3:10 pm

    Thanks @Maxia for your nice example.
    Thanks @hartmut for the link.

  • Karlsson
    January 10, 2009 at 5:42 pm

    Any comparison along the lines of
    somevalue != NULL
    somevalue = NULL
    Will return FALSE. Why? As NULL means “no value”. In your case, when answer is EMPTY is is one thing, but that is NOT the same as answer being NULL. And empty string is not the same as NULL, nor is the value 0 the same as NULL.

    One example is to look at middle names. Lets say you have a middle_name field in the database. I am stretching this a liottle bit here, but all the same, we can have three situations:
    – middle_name is set to some non NULL and non-empty string value, which is the middle name of the person.
    – middle_name is NULL. This means that the person might have or might not have a middle name, and even if the person HAS a middle name, we don’t know what it is.
    – middle_name is an empty string. In this case, the åerson doesn’t have a middle name and we know this is the case.

    Until you get used to working with NULL values, they might seem confusing, and it might even be tempting to use empty strings instead. Don’t do that. For example, look at my blog-post here: http://karlssonondatabases.blogspot.com/2008/12/forgotten-foreign-key-constraint.html to have one reason (foreign keys) why you should handle NULLs appropriately.

    And one other thing, now when I am at it. The way MySQL does this is in line with the SQL standard, and in line with how relational algebra treats it.

    An example of how this can be confusing:
    SELECT SUM(col1) + SUM(col2) FROM sometable
    does NOT necessarily give the same result as:
    SELECT SUM(col1 + col2) FROM sometable

    So watch your NULLs and understand them. Just because they are a bit different inrelational algebra, doesn’t mean we should avoid them, they DO have some cool uses.

  • Eric Day
    January 10, 2009 at 6:49 pm

    Hi! It sounds like you’ve gotten your answer already from Giuseppe and hartmut, but I wanted to emphasize one point:

    ‘NULL’ as you used in “answer != ‘NULL’” is a 4-character string, not the absence of a value (which is NULL with no quotes). Generally, you will never want to put quotes around the word NULL.

    -Eric

  • Roland Bouman
    January 11, 2009 at 6:36 pm

    Hi!

    @Eric: just want to point out that NULL, at least according to standard SQL, does *not* denote the absence of a value. This is what they say:

    “the null value is neither equal to any other value nor not equal to any other value — it is unknown
    whether or not it is equal to any given value”

    But it sure is a value. Another way to think about this is to consider the following INSERT statement:

    UPDATE t SET c = NULL;

    Here, the c column is updated and set to the null value. It’s there and it’s a value denoted by the keyword NULL. Now consider this:

    UPDATE t SET c = ;

    Here your really do have the absence of value, and I am sure you know this is not valid SQL.

    Of course, this this not prevent anyone from establishing the convention that for this or that paricular table, the null value should be taken to mean “no value specified” or “value not applicable”. That however does not mean there is no value. It just means you informally attached some special semantics to the null value, quite similar to the way some people attach boolean true/false semantics to an ENUM(‘Yes’,’No’) column (or what have you).

    kind regards,

    Roland

    Here you do have the absence of value, and it is not valid SQL

  • LGB
    January 11, 2009 at 8:19 pm

    NULL cannot be simply tested, it should be tested with IS NULL for example. It’s the nature of logic in SQL which is three stated: false, true, null.

  • Rayhan Chowdhury
    January 27, 2009 at 5:09 am

    ANSWER IS NULL is the preferred way to check NULL value.

  • mahmud ahsan
    mahmud ahsan
    February 4, 2009 at 4:50 pm

    Thanks all for such a nice discussion

LEAVE A COMMENT