Understanding and handling NULL

In this lesson, you will learn about NULL.

So, First things First: What is NULL?

NULL is the absence or the lack of a value, so, it is not an empty string, it is not a blank space, and it is not zero.

So, when a row in a table has some column set to null, because we didn’t put any value in it or we intentionally decide it to set it to null, the value for that column is undefined, or unknown, and  you can’t compare it to other values, because, again, its value is unknown.

Here is an example: I don’t know how many students are reading this tutorial right now, I mean, in this exact moment.  So, if you ask me if the number of students reading the tutorial right now is greater than 5, I can’t say ‘yes’, because I don’t know, and I can’t say no either, because I just don’t know.  So, the question itself doesn’t make sense to me, because I don’t have the data needed to do such a comparison.

In a similar way, you can’t compare nulls to other values in queries, because NULL is the equivalent to unknown.

You can try, but you will get unexpected results 😄.

For example, please run this query on your database:

SELECT *
FROM employee;

How many rows did you get?

Okay, now write a query to list employees whose phone number is ‘1’.  Yes, just one digit: 1.

No, I’m not going to write it or run it.  If you want to know what it returns, you will have to run it yourself.

Stop reading for a moment, and continue when you get your results.

How many rows did you get now?

Okay, now write a query to list employees whose phone number is different from or not equal to ‘1’.  By the way, you can use this operator  “!=” for “different from” comparisons.

Stop reading again and write and run your query.

Hey, write the query! 😄

Okay, how many rows did you get now?

As you noticed, something odd happened here.  You have 13 employees in total, and you have 0 employees whose phone number is equal to 1, but you have only 11 employees whose phone number is not equal to 1, so, there are two employees whose phone number is not ‘1’ but is not different from ‘1’ either.  😕

That’s weird, isn’t it?

Okay, now list all employees again, and look at the row for the employee with id 13. What does it have in the phone column?

Yeah, you probably guessed.  It has NULL.

How Do I list rows with NULL in some column?

Now, if I want to list those employees whose phone number is null, I could write a query like this, right?

SELECT *
FROM employee
WHERE phone = NULL;

Go ahead, try it!

I guess it didn’t give you anything. Do you know why?

Because NULL is not equal to NULL either.  Since both NULLs are unknown, they could be different or equal unknowns, so, who knows! 😄

So, this is what you have to remember:  In Oracle a condition can result in 3 different logical values:  It can evaluate to TRUE, it can evaluate to FALSE, and it can evaluate to UNKNOWN, and the only rows that will be returned by the query will be those for which the condition evaluates to TRUE.

And when will a condition evaluate to unknown? When one of the values being compared is NULL and you are using “normal” comparison operators like equal to, not equal to, greater than, etc…

The only way to compare NULLs correctly is by using the IS NULL or IS NOT NULL operators (also called conditions).

So, to list employees whose phone number is null, you have to write the query this way:

SELECT *
FROM employee
WHERE phone IS NULL;

Practice Challenge 1

So, here is a task for you.  When finished, continue with the lesson.

The company has a cell phone that is assigned to the employee who is in charge of server support.  All employees in the company can do that job, and they switch positions constantly, so the person in charge of support can change at any time.  The phone number for the server support person is ‘1.234.567.8901’, and that is what you have to use to identify such a person.

Your task is to write a query to list ALL employees whose salary is greater than 4000, but you don’t have to include the person currently in charge of server support.

Expected Output:

Solution to the Challenge

Okay, your first attempt could have been something like this:

SELECT id, name, phone, salary
FROM employee
WHERE salary > 4000
AND phone != '1.234.567.8901';

But if you tested it, you realized that one employee was missing in the results, and it was the one with a NULL phone number.

Why does that happen?

Because when the phone column has NULL, the condition “phone != ‘1.234.567.8901’” evaluates to UNKNOWN, and thus, the row is not returned.

So, you might have tried something like this next:

SELECT id, name, phone, salary
FROM employee
WHERE salary > 4000
AND phone != '1.234.567.8901' OR phone IS NULL;

But this doesn’t work, because the results include one employee who earns 2500, and one of the conditions was to include only those who earn more than 4000.

So, what is the problem?

Do you remember the lesson about “More Complex Where Conditions”?  That lesson has the answer to this question, but I will repeat that part here because it is important.

When you have conditions that are composed of several sub conditions connected by ANDs and ORs, Oracle will decide the order in which they are applied based on the order in which they appear in the query, and the operators’ precedence.

In this case, Oracle will apply the AND first, so, the first part of the condition will be true for rows in which the salary is greater than 4000 AND the phone is different from ‘1.234.567.8901’.

That partial result will be now the first part of the resulting condition, so, a row will be returned if the first part of the condition is true OR the phone is null, which is the case of the missing row, in which the salary is 2500 but the phone is null, and as you might remember, when you connect two conditions with an OR, the result is TRUE if any of them is TRUE.

So, this query produces incorrect results because we are letting Oracle decide the order in which the conditions are evaluated.  To make it right, we have to define the order in which we want the conditions evaluated, and we do that by means of parentheses.

So, we want the 2 conditions about the phone number to be evaluated as one, and after they are evaluated, use the result as the second part of the bigger condition. In other words, we need to enclose the 2 conditions about the phone inside parentheses, so, the final condition will be “salary > 4000 AND

”:

SELECT id, name, phone, salary
FROM employee
WHERE salary > 4000
AND (phone != '1.234.567.8901' OR phone IS NULL);

Okay, let’s continue with the lesson.

How NULLs affect Ordering

You should also think about nulls when you order the results of your queries by columns that can contain null values.

When you order in ascending order by a column that has nulls, by default Oracle will sort leaving nulls at the end of the result set, after all the non-null values, and if you order in descending order, nulls by default will be put before all the non-nun values.

But if that is not what you want, you can specify whether you want nulls at the beginning or at the end by adding the optional NULLS FIRST or NULLS LAST clause.

So, for example, if I want to order employees by phone number, but I want those with no phone number to appear first, this is how I would write my query:

SELECT *
FROM employee
ORDER BY phone NULLS FIRST;

And if I want to order by phone in descending order, but I want employees with no phone to appear at the end, I would do something like this:

SELECT *
FROM employee
ORDER BY phone DESC NULLS LAST;

What Tools do we Have to Work with NULL?

So, as you can see, nulls are quite important, and because of that, Oracle has a couple of functions that were specially designed to simplify our work with NULLs.

NVL

What this function does, is that it lets you specify a substitute for NULL values, so, you apply it to a column or expression, and you tell it what value to return in case that column or expression is null.

Please write and run this query:

SELECT id, name, NVL(phone, '***NO PHONE***')
FROM employee
ORDER BY id;

As you noticed in your results, when phone is null, it returns ‘***NO PHONE***’.

So, another way to list employees with a null phone number would be something like this:

SELECT *
FROM employee
WHERE NVL(phone, '*') = '*';

Notice that now I can use the equal sign because I’m no longer comparing nulls.  When phone is null, the expression in line 3 will return an asterisk, so I will be comparing an asterisk to another asterisk.

Practice Challenge 2

Okay, if you think about it, this function can help you simplify the query you wrote for the previous task (practice challenge).

This task is exactly the same, but now you have to use the NVL function.

So, stop reading now, and continue when you finish the task.

The company has a cell phone that is assigned to the employee who is in charge of server support.  All employees in the company can do that job, and they switch positions constantly, so the person in charge of support can change at any time.  The phone number for the server support person is ‘1.234.567.8901’, and that is what you have to use to identify such a person.

Your task is to write a query to list ALL employees whose salary is greater than 4000, but you don’t have to include the person currently in charge of server support.

Expected Output:

Now, take a look at this query and the results it produces:

SELECT id, name, salary, bonus, salary + bonus
FROM employee
WHERE department_id = 1;

Results:

See what happens when you perform operations involving nulls?

So here is a tip: Every time you perform operations with columns that can be null, make sure to use the NVL function on those columns, to save you from present and future problems.

The correct way, in this case, would be:

SELECT id, name, salary, bonus, salary + NVL(bonus)
FROM employee
WHERE department_id = 1;

So, when bonus is null, you add zero to the salary, and you get always correct results.

There are more functions that can be used to work with NULLs in Oracle. I will briefly talk about a couple of them here:

COALESCE

This is a standard SQL function, and returns the first non-null value from the list passed as parameter.

So, if you have

COALESCE (col1, col2, col3, …, coln);

If col1 is not null, col1 is returned.  If col1 is null, and col2 is not null, col2 is returned, if col1 and col2 are null, and col3 is not null, then col3 is returned, and so on.

NVL2

This is an Oracle-specific function that works this way:

NVL2(expression1, expression2, expression3)

In this case, expression1 is the one that is evaluated. If it is not null, then expression2 is returned, but If it is null then expression3 is returned.

Sounds confusing?

Go ahead and write some queries to test these two functions. You will understand them when you use them. 

Okay, see you in the next section!

Okay, maybe I should not be doing this, because you really will learn if you try and test things, but if you want more information or want to look a bit deeper into these functions, there is an article on the blog, which talks about them.  Here is the link, if you are interested:

Oracle NVL, NVL2 and COALESCE functions