What is NULL?
There is often some confusion about NULL value, as it is treated differently in different languages. So there is an obvious need to clarify what NULL is, how it works in different languages, and what the actual value is behind the NULL. Before going into details, there is also a need to understand the concept of Three-valued logic[1] and Two-valued logic known as bivalent[2]. The bivalent is a concept of boolean value where value can be true or false, but contrary to bivalent the Three-valued logic can be true, false, or (intermediate value) unknown. Now, back to NULL. In some languages NULL acts as bivalent, and in others, Three-valued logic (especially in databases).
C/C++
In “C/C++” the NULL is defined as 0 in the “stddef.h” which is included <cstddef> in case of C++ and stdlib.h in case of C.
1 2 3 4 5 6 7 8 9 10 11 12 13 | #if defined (_STDDEF_H) || defined (__need_NULL) #undef NULL /* in case <stdio.h> has defined it. */ #ifdef __GNUG__ #define NULL __null #else /* G++ */ #ifndef __cplusplus #define NULL ((void *)0) #else /* C++ */ #define NULL 0 #endif /* C++ */ #endif /* G++ */ #endif /* NULL not defined and <stddef.h> or need NULL. */ #undef __need_NULL |
The value can be tested against NULL directly by using the equality operators “==” or !=. Now take an example program and try to check the values against NULL in C.
1 2 3 4 5 6 7 8 9 10 11 12 13 | #include <stddef.h> #include <stdio.h> void main() { if ('0' == NULL) printf("NULL is '0' \n"); if ("" == NULL) printf("NULL is empty string \n"); if (' ' == NULL) printf("NULL is space \n"); if (0 == NULL) printf("NULL is 0 \n"); } |
The output of the above program will be “NULL is 0”, so it is quite evident that NULL is defined as “0” in C language.
Java
Contrary to C where NULL is 0, in Java NULL means the variable references do have value. The value can be tested against NULL by equality operators. When we print the null value, it will print the null. In Java, null is case sensitive and it must be in all small letters as “null”.
1 2 3 4 5 6 7 8 | public class Test { public static void main (String[] args) throws java.lang.Exception { System.out.println("Null is: " + null); } } Null is: null |
PostgreSQL
In PostgreSQL, NULL means no value. In other words, the NULL column does not have any value. It does not equal 0, empty string, or spaces. The NULL value cannot be tested using any equality operator like “=” “!=” etc. There are some special statements to test the value against NULL, but other than that, no statement can be used to test the NULL value.
Let’s do some interesting comparisons, which will clear up the concept of NULL in PostgreSQL. In the following code snippet, we are comparing 1 with 1 and the obvious result is “t” (TRUE). This leads us to understand that the PostgreSQL equality operator gives us true when two values match. Similarly, the equality operator works for the textual value.
1 2 3 4 5 | postgres=# SELECT 1 = 1 result; result -------- t (1 row) |
1 2 3 4 5 | postgres=# SELECT 'foo' = 'foo' result; result -------- t (1 row) |
Let’s do some more experiments, comparing NULL with NULL. If NULL is a normal value, then the result should be “t”. But NULL is not a normal value, therefore, there is no result of that.
1 2 3 4 5 6 | postgres=# SELECT NULL = NULL result; result -------- (1 row) |
Let’s compare NULL with NULL using an in-equality operator. The result is the same as what we got previously. That proves that we cannot compare NULL with NULL using equality and inequality operators.
1 2 3 4 5 6 | postgres=# SELECT NULL != NULL result; result -------- (1 row) |
Similarly, no mathematical operation can be performed on NULL. PostgreSQL produces nothing when any NULL is used as an operand.
1 2 3 4 5 | postgres=# SELECT NULL * 10 is NULL result; result -------- t (1 row) |
How to Use NULL
Therefore it is proved that NULL cannot be compared with any value using equality operators. Then how we can use the NULL if we cannot use any operator or mathematical operation? PostgreSQL provides special statements and functions to check and test values against NULL. There is the only way to use the NULL in PostgreSQL.
IS NULL/IS NOT NULL
1 2 3 4 5 | postgres=# SELECT NULL is NULL result; result -------- t (1 row) |
1 2 3 4 5 | postgres=# SELECT NULL is NOT NULL result; result -------- f (1 row) |
COALESCE
PostgreSQL has a function name “COALESCE” [3]. The function takes n number of arguments and returns the first, not null arguments. You can test your expression against NULL using the function.
1 | COALESCE (NULL, 2 , 1); |
NULLIF
There is another function called “NULLIF”[3], returns NULL if first and second arguments are equal, otherwise returns the first argument, here is the example where we are comparing 10 with 10 and we already know that these are equal so it will return NULL. In the second example, we are comparing 10 with 100 and in that case, it will return 10 the first value.
1 2 3 4 5 6 7 8 9 10 11 12 | postgres=# SELECT NULLIF (10, 10); nullif -------- (1 row) postgres=# SELECT NULLIF (10, 100); nullif -------- 10 (1 row) |
Usage of NULL
If NULL does not have any value, then what is the advantage of NULL? Here are some examples of usage:
In case a field does not have any value, and for example, we have database fields with first/middle and last name. Does, in reality, everybody have a first/middle and last name? The answer is no, there shouldn’t be a field that cannot have any value.
1 2 3 4 5 6 7 8 9 | postgres=# CREATE TABLE student(id INTEGER, fname TEXT, sname TEXT, lname TEXT, age INTEGER); postgres=# SELECT * FROM STUDENT; id | fname | sname | lname | age ----+-------+-------+-------+----- 1 | Adams | Baker | Clark | 21 2 | Davis | | Evans | 22 3 | Ghosh | Hills | | 24 (3 rows) |
Let’s select students who do have a middle name. Does that query work here? No, and the reason behind it is the same we have discussed in earlier queries.
1 2 3 4 | postgres=# SELECT * FROM STUDENT WHERE sname = ''; id | fname | sname | lname | age ----+-------+-------+-------+----- (0 rows) |
Let’s select by using the proper statements and get the desired results.
1 2 3 4 5 | postgres=# SELECT * FROM STUDENT WHERE sname IS NULL; id | fname | sname | lname | age ----+-------+-------+-------+----- 2 | Davis | | Evans | 22 (1 row) |
The field does not make sense, for example, because the spouse’s name of a single person or children’s details is not “KID”. Here is the example where KID in the divorced field does not make sense. We cannot put true or false, so NULL is the right value here.
1 2 3 4 5 6 7 8 | postgres=# CREATE TABLE person(id INTEGER, name TEXT, type TEXT, divorced bool); postgres=# SELECT * FROM person; id | name | type | divorced ----+-------+-------+--------- 1 | Alice | WOMAN | f 3 | Davis | KID | 2 | Bob | MAN | t (3 rows) |
The other usage of NULL is to represent an empty string and empty numeric value. Numeric 0 has significance so it cannot be used to represent the empty numeric filed, the unknown value at a certain time.
In this example there are three students: Alice has 90 marks, Bob has 0 marks, and Davis does not have marks yet. In the case of Bob, we have inserted 0, and for Davis, we have inserted NULL. By doing this we can easily distinguish between who has 0 marks and who does not have results yet.
1 2 3 4 5 6 7 | postgres=# SELECT * FROM students_mark; id | name | marks ----+-------+------- 1 | Alex | 90 2 | Bob | 0 2 | Davis | (3 rows) |
1 2 3 4 5 | postgres=# SELECT * FROM students_mark WHERE marks IS NULL; id | name | marks ----+-------+------- 2 | Davis | (1 row) |
1 2 3 4 5 | postgres=# SELECT * FROM students_mark WHERE marks = 0; id | name | marks ----+------+------- 2 | Bob | 0 (1 row) |
Conclusion
The purpose of this blog is to be clear about the fact that every language has its own meaning of NULL. Therefore, be careful when you are using NULL, otherwise, you will get erroneous results. Especially in databases (PostgreSQL), NULL has some different concepts, so be careful when writing queries involving NULL.
[1] – https://en.wikipedia.org/wiki/Three-valued_logic
[2] – https://en.wikipedia.org/wiki/Principle_of_bivalence
[3] – https://www.postgresql.org/docs/current/functions-conditional.html
Comments (12)
> Especially in databases (PostgreSQL), NULL has some different concepts, so be careful when writing queries involving NULL.
In what databases?
NULL meaning is dictated by SQL standard, and Postgres follows it.
It would be interesting to see if some databases treat it differently compared to the standard.
PostgreSQL is “following” the SQL standard, but NULL is definitely different in other Language like C and Java. This is specifically for those who have programming background and try to write SQL queries.
Good article, but I dissent on one part: I believe that SQL NULL meaning is unclear, and depends on the context.
I explained my point in an article last year:
https://federico-razzoli.com/what-does-null-mean-in-sql
To sum up, my view is that some earlier versions of relational algebra defined two markers (I-marker and A-marker) that defined, respectively, an absent value and an unknown value. SQL standard merged these idea into a single NULL marker in an inconsistent way. The result is that, in practice, sometimes NULLs semantics only make sense if it means absent, sometimes they make sense if it means unknown, and sometimes they don’t make sense in both cases.
@Federico Razzoli, agreed, I just explained very basic difference of NULL in normal programming language and SQL.
@Ibrar Ahmed, on that part I agree. I find most of your article good and useful, and I’m sorry for not stressing this in my first comment. It’s good to explain developers that SQL NULL is not what they could expect if they are familiar with C or Java.
The sentence I disagree with is “In PostgreSQL, NULL means no value”. I agree that this is PostgreSQL intention (because this is what SQL standard says), but in practice all DBMSs I know treat it too inconsistently.
Every so often a cringy article like this comes out from someone who just found the word NULL in a few different places and went about trying to determine why the same word seemingly means different things.
First, you’re comparing an RDBMS engine (PostgreSQL) to programming languages (C & Java). That doesn’t even make sense. These are not the same thing.
Second, you need to understand it in the historical context. In C, NULL is 0. Why? In Unix systems, the first page of memory (which includes address 0) is typically set as inaccessible to a process. When pointers are initialized to 0, they point to this inaccessible piece of memory. This is one of the best ways to catch an uninitialized pointer, as using it will result in your program crashing immediately. So the uninitialized pointer is a null pointer. We don’t use NULL in other contexts – we just use 0. I wouldn’t put “for (x=NULL; …”. There’s a reason we have this, and it’s not to make typing “0” more difficult.
Third, your database examples are surprisingly good given the rest of this, but you missed one of the qualities of NULL values that is useful. They aren’t used in aggregate functions. For instance, taking the students_mark example, “select count(marks) from students_mark” will give you “2”, and “select average(marks) from students_mark” will give you “4.5”. In a database context, NULL means “missing or unknown”. Unfortunately, those are different concepts and you have to use your programming to make up for it.
Please also see the remarks from Federico Razzoli.
I would also add info about “null” in javascript.
@Michael I don’t disagree with you, but I just wrote this article for the people who has some programming experience in some language and try to write some SQL queries. I am comparing NULL in C/Java and SQL because name NULL is same but concept is 100% different. I met many people who does not know the basic concept of NULL in SQL. Secondly I really don’t want to go in very detail in user level blog, hope you understand.
@Ibrar I guess the article is targeting a specific audience that seem to have a niche gap in their knowledge. When looked at from that point of view, a decent write up with good relevant examples.
Also, Federico’s article goes nicely with it. Thanks for the reminder @Federico.
Yes, this is for some specified users who comes from different languages and try to learn SQL’s NULL.
NULL is not always good
Yes, we should hope to have data in all column 🙂
Nice article. It’s also interesting to note that NULL values are not regarded in unique indices. So, while a duplicate non-NULL value is not allowed in a column that has a unique index on it, more than one NULL value *is* allowed. I’m not sure if this behavior is consistent across all major DMBSs but I suspect so.