6.6 NULL Values

NULL is unusual because it doesn't represent a specific value the way that numeric, string, or temporal values do. Instead, NULL stands for the absence of a known value. The special nature of NULL means that it often is handled differently than other values. This section describes how MySQL processes NULL values in various contexts.

Syntactically, NULL values are written in SQL statements without quotes. Writing NULL is different from writing 'NULL' or "NULL". The latter two values are actually strings that contain the word "NULL". Also, because it is an SQL keyword, NULL is not case sensitive. NULL and null both mean "a NULL value," whereas the string values 'NULL' and 'null' may be different or the same depending on whether or not they are binary strings.

Note that some database systems treat the empty string and NULL as the same value. In MySQL, the two values are different.

6.6.1 NULL Values and Column Definitions

NULL can be stored into columns of any type, except columns that are defined as NOT NULL. Allowing NULL values in a column complicates column processing somewhat because the query processor has to treat NULL values specially in some contexts. This results in a slight speed penalty.

If you specify no DEFAULT option, defining a column as NULL or NOT NULL affects how MySQL assigns the default value. For a column that allows NULL values, NULL becomes the default value as well. Otherwise, the default depends on the column type, as described in section 4.10.4, "Column Options."

6.6.2 NULL Values and NOT NULL Columns

A column that is defined as NOT NULL may not be set to NULL. An attempt to do so has different effects depending on the context in which the attempt occurs:

• In a CREATE TABLE statement, specifying a DEFAULT value of NULL for a NOT NULL column results in an error, and the table is not created.

• In a single-row INSERT statement, inserting NULL into a NOT NULL column causes the statement to fail with an error, and no record is created.

• In a multiple-row INSERT statement, inserting NULL into a NOT NULL column has these effects:

  • The column is set to zero, an empty string, or the "zero" temporal value, depending on the column type.

  • The record is created.

  • The warning count is incremented.

The preceding rules do not apply to TIMESTAMP columns or to integer columns defined with the AUTO_INCREMENT option. These types of columns can never contain a NULL value. However, setting them to NULL does not result in an error; instead, they're set to the current date and time or the next sequence number.

6.6.3 NULL Values in Expressions and Comparisons

Expressions that cannot be evaluated (such as 1/0) produce NULL as a result.

Use of NULL values in arithmetic or comparison operations normally produces NULL results:

mysql> SELECT NULL + 1, NULL < 1;

+----------+----------+

| NULL + 1 | NULL < 1 |

+----------+----------+

|     NULL |     NULL |

+----------+----------+

Even comparing NULL to itself results in NULL, because you cannot tell whether one unknown value is the same as another:

mysql> SELECT NULL = 1, NULL != NULL;

+----------+--------------+

| NULL = 1 | NULL != NULL |

+----------+--------------+

|     NULL |         NULL |

+----------+--------------+

LIKE evaluates to NULL if either operand is NULL:

mysql> SELECT NULL LIKE '%', 'abc' LIKE NULL;

+---------------+-----------------+

| NULL LIKE '%' | 'abc' LIKE NULL |

+---------------+-----------------+

|          NULL |            NULL |

+---------------+-----------------+

The proper way to determine whether a value is NULL is to use the IS NULL or IS NOT NULL operators, which produce a true (nonzero) or false (zero) result:

mysql> SELECT NULL IS NULL, NULL IS NOT NULL;

+--------------+------------------+

| NULL IS NULL | NULL IS NOT NULL |

+--------------+------------------+

|            1 |                0 |

+--------------+------------------+

You can also use the special <=> operator, which is like = except that it works with NULL operands by treating them as any other value:

mysql> SELECT 1 <=> NULL, 0 <=> NULL, NULL <=> NULL;

+------------+------------+---------------+

| 1 <=> NULL | 0 <=> NULL | NULL <=> NULL |

+------------+------------+---------------+

|          0 |          0 |             1 |

+------------+------------+---------------+

ORDER BY, GROUP BY, and DISTINCT all perform comparisons implicitly. For purposes of these operations, NULL values are considered identical. That is, NULL values sort together, group together, and are not distinct.

Functions intended specifically for use with NULL values include ISNULL() and IFNULL(). ISNULL() is true if its argument is NULL and false otherwise:

mysql> SELECT ISNULL(NULL), ISNULL(0), ISNULL(1);

+--------------+-----------+-----------+

| ISNULL(NULL) | ISNULL(0) | ISNULL(1) |

+--------------+-----------+-----------+

|            1 |         0 |         0 |

+--------------+-----------+-----------+

IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument:

mysql> SELECT IFNULL(NULL,'a'), IFNULL(0,'b');

+------------------+---------------+

| IFNULL(NULL,'a') | IFNULL(0,'b') |

+------------------+---------------+

| a                | 0             |

+------------------+---------------+

Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value. For example, any NULL argument to CONCAT() causes it to return NULL:

mysql> SELECT CONCAT('a','b'), CONCAT('a',NULL,'b');

+-----------------+----------------------+

| CONCAT('a','b') | CONCAT('a',NULL,'b') |

+-----------------+----------------------+

| ab              | NULL                 |

+-----------------+----------------------+

But not all functions behave that way. CONCAT_WS() (concatenate with separator) simply ignores NULL arguments entirely:

mysql> SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b');

+------------------------+-----------------------------+

| CONCAT_WS('/','a','b') | CONCAT_WS('/','a',NULL,'b') |

+------------------------+-----------------------------+

| a/b                    | a/b                         |

+------------------------+-----------------------------+

For information about the behavior of specific functions, consult the MySQL Reference Manual.

6.6.4 NULL Values and Aggregate Functions

NULL values are ignored by all aggregate functions except COUNT(*), which counts rows and not values. Suppose that you have the following table:

mysql> SELECT * FROM t;

+------+------+------+

| i    | j    | k    |

+------+------+------+

|    0 |    1 | NULL |

| NULL |    2 | NULL |

|    2 | NULL | NULL |

|    3 |    4 | NULL |

|    4 | NULL | NULL |

+------+------+------+

COUNT(*) counts rows, including those that contain NULL values:

mysql> SELECT COUNT(*) FROM t;

+----------+

| COUNT(*) |

+----------+

|        5 |

+----------+

COUNT(expression) ignores NULL values:

mysql> SELECT COUNT(i), COUNT(j), COUNT(k) FROM t;

+----------+----------+----------+

| COUNT(i) | COUNT(j) | COUNT(k) |

+----------+----------+----------+

|        4 |        3 |        0 |

+----------+----------+----------+

COUNT(DISTINCT) also ignores NULL values:

mysql> SELECT COUNT(DISTINCT j), COUNT(DISTINCT k) FROM t;

+-------------------+-------------------+

| COUNT(DISTINCT j) | COUNT(DISTINCT k) |

+-------------------+-------------------+

|                 3 |                 0 |

+-------------------+-------------------+

The other aggregate functions always ignore NULL values:

mysql> SELECT SUM(i), SUM(j), SUM(k), SUM(i+j+k) FROM t;

+--------+--------+--------+------------+

| SUM(i) | SUM(j) | SUM(k) | SUM(i+j+k) |

+--------+--------+--------+------------+

|      9 |      7 |      0 |          0 |

+--------+--------+--------+------------+

mysql> SELECT AVG(i), AVG(j), AVG(k) FROM t;

+--------+--------+--------+

| AVG(i) | AVG(j) | AVG(k) |

+--------+--------+--------+

| 2.2500 | 2.3333 |   NULL |

+--------+--------+--------+

mysql> SELECT MIN(i), MIN(j), MIN(k) FROM t;

+--------+--------+--------+

| MIN(i) | MIN(j) | MIN(k) |

+--------+--------+--------+

|      0 |      1 |   NULL |

+--------+--------+--------+

mysql> SELECT MAX(i), MAX(j), MAX(k) FROM t;

+--------+--------+--------+

| MAX(i) | MAX(j) | MAX(k) |

+--------+--------+--------+

|      4 |      4 |   NULL |

+--------+--------+--------+

Note that SUM(), AVG(), MIN(), and MAX() produce a result of NULL when given a set of input values that contain no non-NULL values (such as the column k in the preceding examples).