| Lesson 4 || Using the null value BIF with Oracle SQL |
| Objective || Use the NVL BIF with Oracle SQL. |
Using null value BIF with Oracle SQL
Three Valued Logic
The null value BIF of Oracle SQL arises from the three-valued logic that is the foundation of the SQL language.
Null values are missing values
Essentially, three values logic understands that a missing value (what Oracle recognizes as NULL) is NOT the same as the default for that data type. For example, a NULL salary for an employee is not the same thing as a salary of zero.
Hence, you need to be able to display data from Oracle tables, and quickly convert null values.
’NOT YET ASSIGNED’) "SALARY"
DAHLSTROM NOT YET ASSIGNED
Here we are using the NVL clause to supply a value for new employees who have not yet been assigned a salary. Within the NVL clause, we are using the TO_CHAR function to change the numeric salary to a character string labeled “NOT YET ASSIGNED.”
Outer join problems with NULL values
The null value issue is even more problematic when two tables are joined via an outer join. In an outer join, two tables are joined together but there may not always be a corresponding row in both tables.
When an outer join is requested, Oracle will display all of the rows of the joining table, even if there is not a matching row in the other table. For these rows, we must handle the NULL values that are returned by the Oracle query.
DBA_DATA_FILES describes database files.
Unfortunately, some data files will be 100% full
and will not have an entry in DBA_FREE_SPACE.
So what can you do? If there is not an entry in DBA_FREE_SPACE, then how can you indicate that the data file is 100% full?
The following Slide Show shows you how.
- Perform outer join using the + operator. An outer join will return rows in dba_data files, even if the values are missing.
- As you see from the query, we are querying dba_free_space.bytes, and we know that because there is no entry in dba_free_space for full data files
- We have enclosed all queries with the NVL clause
- Here is another way to look at the problem. When you join two tables
- Because of this, your result will have missing values.
- You can set these to zero instead by specifying SELECT NVL(column name, 0).
A.FILE_ID (+) = B.FILE_ID
B.STATUS = 'AVAILABLE'
Now, let us look at another powerful translation BIF called DECODE.