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.
Think Different 
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
January 10, 2009 at 3:06 pm
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
January 10, 2009 at 3:10 pm
Thanks @Maxia for your nice example.
Thanks @hartmut for the link.
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
January 10, 2009 at 6:52 pm
@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. 🙂
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
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.
January 27, 2009 at 5:09 am
ANSWER IS NULL is the preferred way to check NULL value.
October 29, 2010 at 12:51 pm
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
December 28, 2010 at 1:56 pm
Thanks GREAT BLOG
August 17, 2013 at 8:36 am
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