- database solutions and downloads for microsoft access

What does NULL mean in a Database?

What exactly does "null" mean?

First I tried to look at it as a "zero" (with a little confusion though) when I came across it in some queries, or think about it as a text string of blank spaces.

Null means either "not applicable" or "don't know": it's not really the same as zero (0) or any other default value, but more importantly, null is treated quite differently from other values in SQL, because it literally has no value.

Here's an example of a "don't know" null -

StudentName TestResult
Samuel Jones 78
Hilary Smith 89
Jed Thomas 67
Lee Neal NULL
Sally Lane 72

As you can see, Lee's value is NULL, which could be interpreted as meaning that Lee did not sit the test (maybe he was off ill and will take the test another day). It would be wrong to assign a zero (0) to the test result, because that would be interpreted as Lee having taken the test and not getting a single answer right!

Now let's consider the following query -

SELECT AVG(TestResult) 
FROM Students

Aggregate functions like AVG() and SUM() ignore NULL's, so this query will return (78+89+67+72)/4=76.5, which is certainly better than (78+89+67+0+72)/5=61.2 which you'd get using a zero (0) default.

Often a default value is just wrong for a column where you expect to take aggregates.

An example of a column that would take a "not applicable" NULL is DateTerminated in a human resources database, where the value would be null for all active employees. To test for nulls, you can filter them out in the WHERE clause -

SELECT EmployeeID, (DateTerminated - DateHired) AS LengthOfService
FROM EmployeeTable
WHERE DateTerminated IS NOT NULL

Which would give results only for terminated employees. If you didn't have the WHERE clause, the above query would return null for every active employee, because any expression involving a null yields a null result.

Further information can be found relating to NULL values in the following article: Defining Null Values