What is NULL?
NULL values have a special function. They represent nothing 🙂 Anytime you see NULL in a table that means there is nothing.
Blank space, zero and NULL difference
The difference between space, zero a NULL is:
- NULL is nothing. It has no data type
- Zero is a number
- Space is a character
But foremost, NULL does not occupy any space unlike zero or blank space.
NULL requires special handling due to its nature. There are several ways how NULL behaves in certain situations. I will now show you what is NULL’s behavior and how to handle it safely.
All arithmetic operations where there is at least one NULL value involved will always result in NULL. Therefore, you have to be very careful when performing these operations and handle NULL values properly.
SELECT 1 + NULL as rslt FROM dual rslt ------------ NULL
NULL values in all string operations will be treated as an empty string.
SELECT 'Oracle'||NULL||' World' as rslt FROM dual rslt -------------- Oracle World
JOIN tables on NULL values
While joining two tables when one of the values in join conditions is NULL it will be evaluated as a “not equal”. This means tables will not be paired/joined.
Comparison of NULL
There are ways how to evaluate whether the column value is NULL (=empty) or not.
= vs !=
You cannot use equals or not-equals signs to check whether the value is NULL or not. However, you can use them still in a comparison of values; You just have to consider how it reacts on NULL values.
/* equal */ SELECT col_A , col_B FROM test_table WHERE col_A = col_B; -- null value is ignored col_A | col B ------------------ 2 | 2 /* not equal */ SELECT col_A , col_B FROM test_table WHERE col_A != col_B; -- null value is ignored (no rows returned) col_A | col B ------------------
IS NULL vs IS NOT NULL
There are two default ways how to check whether the value is null or not.
-- show only records from employee table where id_department is NULL SELECT * FROM employee WHERE 1=1 AND id_department IS NULL; -- show only records from employee table where id_department is NOT NULL SELECT * FROM employee WHERE 1=1 AND id_department IS NOT NULL;
Aggregate functions – sum, count, …
When using aggregate functions NULL value is skipped.
SELECT sum(id_row) as sum_id , count(id_row) as count_id FROM test_table; sum_id | count_id ----------------------- 8 | 3
There are several ways how to handle NULL values. Most commonly used and highly recommended are built-in functions.
- this function accepts two parameters; first is the testing value and second is the replacement if the first one is NULL
SELECT nvl('Hello','Bye') as rslt FROM dual; rslt ---------- Hello SELECT nvl(NULL,'Bye') as rslt FROM dual; rslt ---------- Bye
- works in a similar way as nvl() but accepts three parameters.
- the first parameter is a tested value, second is a value if the tested one is NOT NULL and the third is the replacement if the tested value is NULL
SELECT nvl2('Hello', 'Is not NULL', 'Is NULL') as rslt FROM dual; rslt ------------- Is not NULL SELECT nvl2(NULL, 'Is not NULL', 'Is NULL') as rslt FROM dual; rslt ------------- Is NULL -- this function might be useful when testing how many values in a tested column are NULL and how many are not SELECT nvl2(tested_column, 'NOT NULL', 'NULL') as rslt , count(*) as cnt_values FROM test_table GROUP BY nvl2(tested_column, 'NOT NULL', 'NULL'); rslt | cnt_values -------------------------- NOT NULL | 10 NULL | 20
This function is not built to deal with NULL values by default but it might be used to handle them as well.
SELECT DECODE(tested_column, NULL, 'IS NULL', 'IS NOT NULL') as rslt , count(*) as cnt_values FROM test_table GROUP BY DECODE(tested_column, NULL, 'IS NULL', 'IS NOT NULL'); rslt | cnt_values ------------------------- NOT NULL | 10 NULL | 20
COALESCE function will return first non-NULL value from a given list.
SELECT coalesce(NULL, NULL, 'Oracle', NULL, 'World') as coalesce FROM dual coalese ---------------- Oracle
This function accepts two parameters and if they are both equal, it will return NULL. If they are not equal, it will return the first parameter. There is one limitation: You cannot specify NULL literal as a first parameter manually.
Quiz: A table T1 has a column A with 10 records. Eight of them are not null and the rest is null. What is the result of this query “SELECT count(a) FROM t1”?