Home / SQL Statements / Question No: 225

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 they are non-binary or binary strings.

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

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 (non-zero) 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 MySQL-specific <=> 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.

Expressions that cannot be evaluated (such as 1/0) produce NULL as a result. However, in the context of inserting data into tables, division by zero can be treated as an error to prevent invalid data from being entered. This behavior is controlled by setting the SQL mode to enable strict mode in conjunction with the ERROR_FOR_DIVISION_BY_ZERO mode.