1z0-071 Oracle Database SQL – NULL VALUES

1. NULL Values

It is possible to have no data. At certain places, they are represented by null values. Null means no data tables can have null columns. It depends on the design of our application. Let us look at some examples in HR Schema. If I look at Employees table, I see some columns are nullable represented by yes values here, meaning that they allow null values and some are not nullable, represented by no here, meaning that they do not allow null values. In a glance, we can understand that every employee must have an employee ID. And so this column is a not null column, meaning that if a row gets inserted with null values for this column, it will not be allowed.

However, commission percentage may be null because some employees may not work on commission basis. Let me go to HR session. Select star from employees. I see null values here. Actually, these fields are really empty, but SQL developer shows them as open parentheses. Null close parenthesis. It can be changed by going to Tools Preferences database advanced display null value as here. You can change let me change it to no data. Click OK, it got changed. Let me change it back to null. So these are actually null values, meaning that there is no data.

2. Retrieving NULL Values – IS NULL

We have covered the basics of where class in part one of this course. Suppose if I want to select just the rows where a particular column is null. Then I would use ease operator, let me explain it with examples. Suppose I want to see those whose commission percentage is null. For that. Can I use this? SQL?

Select star from employees where commission underscore percentage equals to null. Let me run it. No, I cannot use this sequel. It doesn’t bring any result because a real null or no data is not equal to the string null. Instead, I should use the following select star from employees where commission percentage is null. Here it means that bring me the rows of those whose commission percentage is null. Thank you for watching.

3. Retrieving NON-NULL Values – IS NOT NULL

We saw how to retrieve null values, we would use the Ease null condition in the where class and bring the null values. We see null values here. However, how do we retrieve just the non null values? For that, we would need to use Ease not null condition in the where class. The statement would be like this select star from employees where commission percentage is not null. This will bring only the rows where the commission percentage is not null.

4. Arithmetic Operations on NULL Values

Any arithmetic operation on Null values will result in Null. In this SQL. Select first name. Last name. Commission percentage. Commission percentage times two. As new commission percentage from employees, I am multiplying all the rows of the column commission percentage with two and if I look, the result is Null for the rows where there are Null values and it works fine otherwise. Even if I divide any number by Null, we would get Null as the result.

In this sequel, select First Name Last Name commission Percentage 100 divided by commission percentage as new commission percentage from employees, I get the value the result as Null for rows where there are already Null values and it works fine otherwise. Remember, a Null is not zero.

5. Concatenation on NULL Values

A null value gets ignored during concatenation. For example, let us just say that I want to produce a result set which would look like hi, I am first name Last name and my commission percentage is this much. The end. To do that I will use the following SQL select I am first name, Last name and my commission percentage is commission percentage. The end. Look at the single quotes and spacing carefully. Let me run it. If you look here I have a single space after this.

Yes, and look what happens in the result. If the values are null, then I notice that it is completely ignored. I just see the space that I allocated here. For rows with values we can see this output where you get the values and the space. If you remove that space, we can closely verify that null values are totally getting ignored in concatenation. See after yes, there is a dot which is actually from this one. But if there is a value then the value.

6. Substituting NULL Values in the Results

If needed, Oracle allows us to substitute null values in the results. For example, we get null values as a part of result from this sequel. Now I can use a function called NVL like NVL open parenthesis commission percentage comma zero. What this will do is if there are any rows with null values in this commission percentage column, it will replace it with a value specified here. I have specified zero as an example. Let’s run this SQL.

You see, the null values have been replaced with zero. The non null values remain just as they are. There is one condition in this substitution, though. The substituting value should also be of the same format as the column. If you see the column, commission percentage is a number column, so I cannot do a character substitution here. It fails invalid number. We can also use NVL function in concatenations and my commission percentage is zero, so it’s more readable this way.