Null Values in SQL DB

by K. Yue

1. Null values

• Generally used for representing missing information.
• SQL DBMS provide a method to test whether a value is null or not (IS NULL and IS NOT NULL).

Example:

-- students with no advisor
SELECT s.*
FROM student AS s
WHERE s.advisor IS NULL;

-- Show all students with a declared minor.
SELECT DISTINCT s.*
FROM student AS s
WHERE s.minor IS NOT NULL;

-- Show enrollment without a n_alerts value.
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts IS NULL;

Result:

MariaDB [toyu]> -- students with no advisor
MariaDB [toyu]> SELECT s.*
    -> FROM student AS s
    -> WHERE s.advisor IS NULL;
+--------+-----------+-------+-------+-------+------+---------+
| stuId  | fname     | lname | major | minor | ach  | advisor |
+--------+-----------+-------+-------+-------+------+---------+
| 100003 | Catherine | Lim   | ITEC  | CINF  |   20 |    NULL |
| 100007 | Ben       | Zico  | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching | ARTS  | NULL  |   90 |    NULL |
+--------+-----------+-------+-------+-------+------+---------+
3 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Show all students with a declared minor.
MariaDB [toyu]> SELECT DISTINCT s.*
    -> FROM student AS s
    -> WHERE s.minor IS NOT NULL;
+--------+-----------+---------+-------+-------+------+---------+
| stuId  | fname     | lname   | major | minor | ach  | advisor |
+--------+-----------+---------+-------+-------+------+---------+
| 100000 | Tony      | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk    | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk    | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |   20 |    NULL |
| 100005 | Linda     | Johnson | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson | CINF  | ITEC  |   18 |    1016 |
| 100009 | Linda     | King    | ARTS  | CSCI  |  125 |    1018 |
+--------+-----------+---------+-------+-------+------+---------+
7 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Show enrollment without a n_alerts value.
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts IS NULL;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100001 |   10000 | NULL  |     NULL |
| 100005 |   10003 | NULL  |     NULL |
| 100004 |   10004 | B+    |     NULL |
| 100006 |   10004 | C+    |     NULL |
| 100006 |   10005 | A     |     NULL |
| 100005 |   10006 | B+    |     NULL |
+--------+---------+-------+----------+
6 rows in set (0.000 sec)

2. Null and Boolean Expressions

1. MySQL does not have a Boolean data type. A Boolean value is converted to TINYINT: 0 as FALSE, otherwise TRUE.
2. If a Boolean value is expected, null is implicitly type converted to FALSE.
3. However, NULL is a special value different with 0 or empty string.
4. Comparing null to other values return false.

Example:

-- 1. Boolean values are TINYINT. FALSE is 0.
SELECT FALSE,
   TRUE;
  
SELECT *
FROM student
WHERE 0;

SELECT *
FROM student
WHERE 1;

SELECT *
FROM student
WHERE 2697;

SELECT *
FROM student
WHERE '0';

SELECT *
FROM student
WHERE '145';

-- warning: '' cannot be converted to a number.
-- "Warning 1292 Truncated incorrect DOUBLE value: ''"
SELECT *
FROM student
WHERE '';

-- warning: '' cannot be converted to a number.
-- "Warning 1292 Truncated incorrect DOUBLE value: ''"
SELECT *
FROM student
WHERE 'Hello world';

SELECT *
FROM student
WHERE 1.49;

Result:

MariaDB [toyu]> SELECT FALSE,
    -> TRUE;
+-------+------+
| FALSE | TRUE |
+-------+------+
|     0 |    1 |
+-------+------+
1 row in set (0.001 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE 0;
Empty set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE 1;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE 2697;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE '0';
Empty set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE '145';
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- warning: '' cannot be converted to a number.
MariaDB [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''"
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE '';
Empty set, 1 warning (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- warning: '' cannot be converted to a number.
MariaDB [toyu]> -- "Warning 1292 Truncated incorrect DOUBLE value: ''"
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE 'Hello world';
Empty set, 1 warning (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE 1.49;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.000 sec)

Example:

-- 2. If a Boolean value is expected, null is implicitly type-converted to FALSE.
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts; 

Result:

MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100000 |   10002 | B+    |        1 |
| 100002 |   10002 | B+    |        2 |
| 100002 |   10003 | D     |        4 |
| 100000 |   10004 | A-    |        1 |
| 100007 |   10007 | F     |        4 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
8 rows in set (0.000 sec)

Example:

-- 3. null is a special value different with 0 or empty string.
SELECT FALSE IS NULL,  
   TRUE IS NULL,
   0 IS NULL,
   1 IS NULL,
   "" IS NULL,
   "Hey" IS NULL,
   NULL IS NULL,
   NULL IS NOT NULL;

Result:

MariaDB [toyu]> SELECT FALSE IS NULL,
    -> TRUE IS NULL,
    -> 0 IS NULL,
    -> 1 IS NULL,
    -> "" IS NULL,
    -> "Hey" IS NULL,
    -> NULL IS NULL,
    -> NULL IS NOT NULL;
+---------------+--------------+-----------+-----------+------------+---------------+--------------+------------------+
| FALSE IS NULL | TRUE IS NULL | 0 IS NULL | 1 IS NULL | "" IS NULL | "Hey" IS NULL | NULL IS NULL | NULL IS NOT NULL |
+---------------+--------------+-----------+-----------+------------+---------------+--------------+------------------+
|             0 |            0 |         0 |         0 |          0 |             0 |            1 |                0 |
+---------------+--------------+-----------+-----------+------------+---------------+--------------+------------------+
1 row in set (0.000 sec)

Example:

-- 4. Comparing null to other values return null, which is converted to false.
SELECT NULL > 3,
   NULL <= 3,
   5 >= NULL,
   5 < NULL,
   NULL > NULL,
   NULL <= NULL;
  
SELECT *
FROM student
WHERE NULL > 3;

-- Comparisons must be mindful of null.
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts >= 2;

SELECT e.*
FROM enroll AS e
WHERE e.n_alerts < 2;

SELECT e.*
FROM enroll AS e;

-- Q. List all enrollment records without 2 or more n_alerts.
-- Naive solution
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts < 2;

-- Q. List all enrollment records without 2 or more n_alerts.
-- More likely solution
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts IS NULL
OR e.n_alerts < 2;

-- Q. List all enrollment records without a value in n_alerts.
-- incorrect answer.
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts <> NULL;

-- Q. List all enrollment records without a value in n_alerts.
-- correct answer.
SELECT e.*
FROM enroll AS e
WHERE e.n_alerts IS NOT NULL;

Result:

MariaDB [toyu]> -- 4. Comparing null to other values return null, which is converted to false.
MariaDB [toyu]> SELECT NULL > 3,
    -> NULL <= 3,
    -> 5 >= NULL,
    -> 5 < NULL,
    -> NULL > NULL,
    -> NULL <= NULL;
+----------+-----------+-----------+----------+-------------+--------------+
| NULL > 3 | NULL <= 3 | 5 >= NULL | 5 < NULL | NULL > NULL | NULL <= NULL |
+----------+-----------+-----------+----------+-------------+--------------+
|     NULL |      NULL |      NULL |     NULL |        NULL |         NULL |
+----------+-----------+-----------+----------+-------------+--------------+
1 row in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT *
    -> FROM student
    -> WHERE NULL > 3;
Empty set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Comparisons must be mindful of null.
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts >= 2;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100002 |   10002 | B+    |        2 |
| 100002 |   10003 | D     |        4 |
| 100007 |   10007 | F     |        4 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
6 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts < 2;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100000 |   10003 | C     |        0 |
| 100004 |   10003 | A     |        0 |
| 100000 |   10004 | A-    |        1 |
| 100005 |   10004 | A-    |        0 |
| 100005 |   10005 | A-    |        0 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
+--------+---------+-------+----------+
10 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10000 | NULL  |     NULL |
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100002 |   10002 | B+    |        2 |
| 100000 |   10003 | C     |        0 |
| 100002 |   10003 | D     |        4 |
| 100004 |   10003 | A     |        0 |
| 100005 |   10003 | NULL  |     NULL |
| 100000 |   10004 | A-    |        1 |
| 100004 |   10004 | B+    |     NULL |
| 100005 |   10004 | A-    |        0 |
| 100006 |   10004 | C+    |     NULL |
| 100005 |   10005 | A-    |        0 |
| 100006 |   10005 | A     |     NULL |
| 100005 |   10006 | B+    |     NULL |
| 100007 |   10007 | F     |        4 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
22 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Q. List all enrollment records without 2 or more n_alerts.
MariaDB [toyu]> -- Naive solution
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts < 2;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100000 |   10003 | C     |        0 |
| 100004 |   10003 | A     |        0 |
| 100000 |   10004 | A-    |        1 |
| 100005 |   10004 | A-    |        0 |
| 100005 |   10005 | A-    |        0 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
+--------+---------+-------+----------+
10 rows in set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Q. List all enrollment records without 2 or more n_alerts.
MariaDB [toyu]> -- More likely solution
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts IS NULL
    -> OR e.n_alerts < 2;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100001 |   10000 | NULL  |     NULL |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100000 |   10003 | C     |        0 |
| 100004 |   10003 | A     |        0 |
| 100005 |   10003 | NULL  |     NULL |
| 100000 |   10004 | A-    |        1 |
| 100004 |   10004 | B+    |     NULL |
| 100005 |   10004 | A-    |        0 |
| 100006 |   10004 | C+    |     NULL |
| 100005 |   10005 | A-    |        0 |
| 100006 |   10005 | A     |     NULL |
| 100005 |   10006 | B+    |     NULL |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
+--------+---------+-------+----------+
16 rows in set (0.000 sec) MariaDB [toyu]> -- Q. List all enrollment records without a value in n_alerts.

MariaDB [toyu]> -- incorrect answer.
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts <> NULL;
Empty set (0.000 sec)

MariaDB [toyu]>
MariaDB [toyu]> -- Q. List all enrollment records without a value in n_alerts.
MariaDB [toyu]> -- correct answer.
MariaDB [toyu]> SELECT e.*
    -> FROM enroll AS e
    -> WHERE e.n_alerts IS NOT NULL;
+--------+---------+-------+----------+
| stuId  | classId | grade | n_alerts |
+--------+---------+-------+----------+
| 100000 |   10000 | A     |        0 |
| 100002 |   10000 | B-    |        3 |
| 100000 |   10001 | A     |        2 |
| 100001 |   10001 | A-    |        0 |
| 100000 |   10002 | B+    |        1 |
| 100002 |   10002 | B+    |        2 |
| 100000 |   10003 | C     |        0 |
| 100002 |   10003 | D     |        4 |
| 100004 |   10003 | A     |        0 |
| 100000 |   10004 | A-    |        1 |
| 100005 |   10004 | A-    |        0 |
| 100005 |   10005 | A-    |        0 |
| 100007 |   10007 | F     |        4 |
| 100008 |   10007 | C-    |        0 |
| 100007 |   10008 | A-    |        0 |
| 100000 |   11001 | D     |        4 |
+--------+---------+-------+----------+
16 rows in set (0.000 sec)

3. Interpretation of null values

• Three possible interpretations of NULL:
  1. Not applicable.
  2. Missing value.
  3. No information at all.

Example:

Consider the attribute SpouseName. A Null value may mean:

1. not applicable: the person is not married.
2. missing information: the person is married but we do not have the name of the spouse.
3. no information at all: we do not know whether the person is married or not.

How do we distinguish between the three meanings of the null value in this case?

By using an extra attribute, such as MaritalStatus.