List:General Discussion« Previous MessageNext Message »
From:Mark Richards Date:September 15 2003 3:23pm
Subject:RE: Does NULL == ""?
View as plain text  
Null is a special value and cannot be tested in the same manner as a string
or other value.  Yes, there is a difference.  If the column is Null, a
comparison operation such as a.field == ""  or a.field == "something" will
both return Null.

Maybe this document will help:

A.5.3 Problems with NULL Values


The concept of the NULL value is a common source of confusion for newcomers
to SQL, who often think that NULL is the same thing as an empty string "".
This is not the case! For example, the following statements are completely
different: 

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ("");

Both statements insert a value into the phone column, but the first inserts
a NULL value and the second inserts an empty string. The meaning of the
first can be regarded as ``phone number is not known'' and the meaning of
the second can be regarded as ``she has no phone''. 

In SQL, the NULL value is always false in comparison to any other value,
even NULL. An expression that contains NULL always produces a NULL value
unless otherwise indicated in the documentation for the operators and
functions involved in the expression. All columns in the following example
return NULL: 

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

If you want to search for column values that are NULL, you cannot use the
=NULL test. The following statement returns no rows, because expr = NULL is
FALSE, for any expression: 

mysql> SELECT * FROM my_table WHERE phone = NULL;

To look for NULL values, you must use the IS NULL test. The following shows
how to find the NULL phone number and the empty phone number: 

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = "";

Note that you can only add an index on a column that can have NULL values if
you are using MySQL Version 3.23.2 or newer and are using the MyISAM or
InnoDB table type. In earlier versions and with other table types, you must
declare such columns NOT NULL. This also means you cannot then insert NULL
into an indexed column. 

When reading data with LOAD DATA INFILE, empty columns are updated with ''.
If you want a NULL value in a column, you should use \N in the text file.
The literal word 'NULL' may also be used under some circumstances. See
section 6.4.9 LOAD DATA INFILE Syntax. 

When using ORDER BY, NULL values are presented first. If you sort in
descending order using DESC, NULL values are presented last. When using
GROUP BY, all NULL values are regarded as equal. 

To help with NULL handling, you can use the IS NULL and IS NOT NULL
operators and the IFNULL() function. 

For some column types, NULL values are handled specially. If you insert NULL
into the first TIMESTAMP column of a table, the current date and time is
inserted. If you insert NULL into an AUTO_INCREMENT column, the next number
in the sequence is inserted. 




-----Original Message-----
From: Randy Chrismon [mailto:rchrismon@stripped] 
Sent: Monday, September 15, 2003 11:00
To: mysql@stripped
Subject: Does NULL == ""?


The MySQL documentation confuses me a bit. If I create a table with
property NOT NULL and default "", does that mean that a record with a
column so defined will have a zero-length string in that column if I
don't provide a value? I guess what I'm asking is whether there's a
difference between a field with NULL in it and a field with a
zero-length ("") string in it.

Thanks. 

Randy

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1


Thread
Does NULL == ""?Randy Chrismon15 Sep
  • Re: Does NULL == ""?Bruce Feist15 Sep
  • RE: Does NULL == ""?Mark Richards15 Sep
  • Re: Does NULL == ""?Brent Baisley15 Sep
    • Re: Does NULL == ""?Keith C. Ivey15 Sep
  • Re: Does NULL == ""?Paul DuBois15 Sep
Re: Does NULL == ""?Alec.Cawley15 Sep
  • RE: Does NULL == ""?Mark Richards15 Sep
Re: Does Null == ""?Randy Chrismon15 Sep
  • RE: Does Null == ""?Mark Richards15 Sep
  • Re: Does Null == ""?Bob Hall16 Sep
    • Re: Does Null == ""?Bruce Feist16 Sep
      • Re: Does Null == ""?Dan Nelson16 Sep
        • Re: Does Null == ""?Bruce Feist16 Sep
          • Re: Does Null == ""?Bob Hall16 Sep
        • Re: Does Null == ""?Bob Hall16 Sep
      • RE: Does Null == ""?Jon Frisby16 Sep
        • Re: Does Null == ""?Bruce Feist16 Sep
          • RE: Does Null == ""?Jon Frisby16 Sep
            • Re: Does Null == ""?Bruce Feist16 Sep
              • RE: Does Null == ""?Jon Frisby16 Sep
              • Re: Does Null == ""?Bob Hall17 Sep
        • Re: Does Null == ""?Bob Hall16 Sep
      • Re: Does Null == ""?Bob Hall16 Sep
    • Re: Does Null == ""?Bob Hall17 Sep
      • Re: Does Null == ""?Bruce Feist18 Sep
        • Re: Does Null == ""?Bob Hall18 Sep
  • Re: Does Null == ""?Haydies18 Sep
    • Re: Does Null == ""?Bob Hall18 Sep