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