Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.5Mb
PDF (A4) - 37.5Mb
PDF (RPM) - 36.9Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 211.3Kb
Man Pages (Zip) - 320.9Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

B.5.4.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 the person is known to have no phone, and thus no phone number.

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

In SQL, the NULL value is never true 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);

To search for column values that are NULL, you cannot use an expr = NULL test. The following statement returns no rows, because expr = NULL is never true 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 statements show 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 = '';

See Section, “Working with NULL Values”, for additional information and examples.

You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.

When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. To load a NULL value into a column, use \N in the data file. The literal word NULL may also be used under some circumstances. See Section 13.2.6, “LOAD DATA INFILE Syntax”.

When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are regarded as equal.

When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order.

Aggregate (summary) functions such as COUNT(), MIN(), and SUM() ignore NULL values. The exception to this is COUNT(*), which counts rows and not individual column values. For example, the following statement produces two counts. The first is a count of the number of rows in the table, and the second is a count of the number of non-NULL values in the age column:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

For some data types, MySQL handles NULL values specially. If you insert NULL into a TIMESTAMP column, the current date and time is inserted. If you insert NULL into an integer or floating-point column that has the AUTO_INCREMENT attribute, the next number in the sequence is inserted.

User Comments
  Posted by Tinel Barb on December 15, 2011
Trying to know exactly how NULL is treated (to know when not to use the IFNULL replacement for large calculations), I was putting it in tests like this:

select 111+222+null as `how much?`; -- returns: NULL (logical evaluation?? should ignore it)

select sum(111+222+null) as `how much?`; -- returns: NULL (logical evaluation?? should ignore it)

select sum(a) as `how much?`
from (select 111 as a union select 222 union select null ) as b; -- returns: 333 (as SUM() on columns is ignoring NULL values)

select sum(a) as `how much?`
from (select 111 as a,'nume1' as nume
union select 222,'nume2'
union select null,'nume1' ) as b
group by nume; -- returns: 111 for nume='nume1' (testing GROUP BY for SUM)

select sum(a) as `how much?`
from (select 111 as a,'name1' as name
union select 222,'name2'
union select null,'name1' ) as b
where name='name1'; -- returns: 111 (testing filter for SUM)

As seen, when used in arithmetic operations NULL turns anything to NULL:
111+NULL => NULL
111*NULL => NULL
NULL/111 => NULL
111-NULL => NULL

BUT, when it comes to working on columns, NULL is ignored in most cases, EXCEPT when the NULL value is used in calculations between columns or columns and other type of values!

select sum(@myconst+t.col1) from t [...]
will return NULL for fields with NULL values, not the fixed @myconst value!

Hope that helps the coding.

Sign Up Login You must be logged in to post a comment.