In many programming languages, coders often use NULL or null as an equivalent to the numeric ‘0’ (zero) in comparison tests. This normally works out, but it’s worth mentioning that in the database world, ‘NULL’ means something else.

To illustrate this, in MySQL we can see that querying NULL returns a string representing the keyword/value ‘NULL’:

mysql> SELECT NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

When we attempt to cast NULL to a numeric value, we still get the string representing the keyword/value ‘NULL’:

mysql> SELECT CAST(NULL AS UNSIGNED);
+------------------------+
| CAST(NULL AS UNSIGNED) |
+------------------------+
|                   NULL |
+------------------------+
1 row in set (0.03 sec)

Only after explicitly testing for a NULL value, MySQL tells us that zero is not the same as NULL

mysql> SELECT (CASE WHEN 0 = NULL THEN 'Zero is null' ELSE 'Zero is not null' END) AS Test;
+------------------+
| Test             |
+------------------+
| Zero is not null |
+------------------+
1 row in set (0.00 sec)

To actually get a workable numeric value from NULL, we have to jump through some hoops. HINT: imagine replacing the keyword ‘NULL’ with a SELECT statement that could return a NULL value.

mysql> SELECT CAST(IFNULL(NULL, 0) AS UNSIGNED);
+-----------------------------------+
| CAST(IFNULL(NULL, 0) AS UNSIGNED) |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)