The null value BIF of Oracle SQL arises from the three-valued logic that is the foundation of the SQL language.

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.

SELECT 
   ename, 
   NVL(TO_CHAR(SALARY), ’NOT YET ASSIGNED’) "SALARY" 
FROM 
   employee
;
ENAME  SALARY
---------- -------------------------------------
BARRETT    85,000
BURLESON   101,000
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.”

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.

For example, click the View Code link to see a query that computes data file usage by querying the DBA_DATA_FILES view and the DBA_FREE_SPACE view.
View Code Link
This is a very common DBA script and it is used to show the amount of data file space that is available to Oracle.
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.

Null Built In Function
You can access the query that you just saw in the FlipBook by clicking the View Code link.
View Code Link
Now, let us look at another powerful translation BIF called DECODE.