Databases and SQL - Library edition

Missing Data

Real-world data is never complete — there are always holes. Databases represent these holes using a special value called null. null is not zero, False, or the empty string; it is a one-of-a-kind value that means “nothing here”. Dealing with null requires a few special tricks and some careful thinking.

To start, let’s have a look at the Works table. There are a total of 20 records. The page number is missing for records #2 and #26 –— for them, the Pages field is null:

SELECT Work_ID, Title, Pages FROM Works;

Null doesn’t behave like other values. If we select the works that have less than 300 pages:

SELECT Work_ID, Title, Pages FROM Works WHERE Pages<300;

we get two results, and if we select the ones that have 300 or more pages:

SELECT Work_ID, Title, Pages FROM Works WHERE Pages>=300;

we get sixteen results. Records #2 and #16 aren’t in either set of results. The reason is that NULL<300 is neither true nor false: null means, “We don’t know,” and if we don’t know the value on the left side of a comparison, we don’t know whether the comparison is true or false. Since databases represent “don’t know” as null, the value of NULL<300 is actually NULL.

NULL>=300 is also null because we can’t answer to that question either. And since the only records kept by a WHERE are those for which the test is true, records #2 and #16 aren’t included in either set of results.

Comparisons aren’t the only operations that behave this way with nulls. 1+NULL is NULL, 5*NULL is NULL, and so on. In particular, comparing things to null with = and != produces null:

SELECT Work_ID, Title, Pages FROM Works WHERE Pages=NULL;

produces no output, and neither does:

SELECT Work_ID, Title, Pages FROM Works WHERE Pages!=NULL;

To check whether a value is null or not, we must use a special test IS NULL:

SELECT Work_ID, Title, Pages FROM Works WHERE Pages IS NULL;

or its inverse IS NOT NULL:

SELECT Work_ID, Title, Pages FROM Works WHERE Pages IS NOT NULL;

Null values can cause headaches wherever they appear. For example, suppose we want to find all books about SQL that have less than 500 pages. It’s natural to write the query like this:

SELECT * FROM Works WHERE Title LIKE "%SQL%" AND Pages<500;

but this query filters omits the records where we don’t know the number of pages. Once again, the reason is that when Pages is NULL, the < comparison produces NULL, so the record isn’t kept in our results. If we want to keep these records we need to add an explicit check:

SELECT * FROM Works WHERE Title LIKE "%SQL%" AND (Pages<500 OR Pages IS NULL);

We still have to decide whether this is the right thing to do or not. If we want to be absolutely sure that we aren’t including any books with less than 500 pages, we need to exclude all the records for which we don’t know the number of pages.

In contrast to arithmetic or Boolean operators, aggregation functions that combine multiple values, such as min, max or avg, ignore null values. In the majority of cases, this is a desirable output: for example, unknown values are thus not affecting our data when we are averaging it. Aggregation functions will be addressed in more detail in the next section.

SELECT * FROM Works WHERE Edition in ("1st ed.", NULL);