NULL

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 handling

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.

Arithmetic operations

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

String concatenation

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.

col_A	col_B
NULL	1
2	2

/* 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.

id_row	value
2	A
NULL	B
1	C
5	D

SELECT sum(id_row) as sum_id
, count(id_row) as count_id
FROM test_table;


 sum_id   |   count_id 
-----------------------
   8      |      3

NULL-related functions

There are several ways how to handle NULL values. Most commonly used and highly recommended are built-in functions.

nvl()

• 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

nvl2()

• 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

Decode()

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()

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

Nullif()

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”?

Answer: 8

Show answer