MySQL – NULL vs ” vs ‘NULL’

Today, 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.

About these ads

11 Responses to “MySQL – NULL vs ” vs ‘NULL’”

  1. Giuseppe Maxia Says:

    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

  2. mahmudahsan Says:

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

  3. Eric Day Says:

    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

    • mahmudahsan Says:

      @Eric Day, Yeah I know ‘NULL’ is a 4 char string. My concern was, how will I get the result that I want, and here I showed the ways I tried. Nothing else. :)

  4. Roland Bouman Says:

    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

  5. LGB Says:

    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.

  6. Rayhan Chowdhury Says:

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

  7. uday Says:

    in to dates are compare if both are equal then print some thing

    but mysql not having a syntax of it

    plz fix the issue

  8. JAmal Says:

    Thanks GREAT BLOG

  9. Mavis Says:

    Exactly how long did it acquire u to publish “MySQL – NULL vs
    vs NULL | Think Different”? It has an awful lot of beneficial material.
    Thanks -Traci


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: