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.
The statement that "three-valued logic" is the foundation of the SQL language refers to the way SQL handles the truth values of conditions^[1] in its query operations, specifically incorporating a third logical value beyond the traditional true and false. This third value is "unknown."
In standard binary logic, each proposition can either be true or false. However, SQL introduces a third value, unknown, to handle situations where the truth value of a proposition cannot be determined. This typically arises in SQL due to NULL values, which represent missing or inapplicable data. NULL is neither true nor false; it is unknown, and thus affects how SQL evaluates expressions and conditions:

Logical Operations: In SQL, logical operators like AND, OR, and NOT have to accommodate the unknown value. For example, in a logical AND operation, if one operand is true and the other is unknown, the result is unknown, because the true outcome depends on the unknown value.
Comparisons: Any comparison involving a NULL value results in an unknown. For example, if you compare a column that contains NULL with another value (e.g., `column_name = 5`), the result is unknown because it’s not clear whether the NULL value is equivalent to 5 or not.
Filtering Rows: The presence of unknown values affects how rows are filtered in SQL queries. For instance, a WHERE clause condition that evaluates to unknown does not qualify a row to be included in the result set.

The implementation of three-valued logic is crucial for dealing with partial or missing information in databases, and it is a foundational aspect of SQL's design, influencing how queries are written and results are interpreted. This approach allows SQL to handle data more flexibly and robustly, accommodating the uncertainties inherent in handling large and complex data sets.

Null values are Missing Values

Essentially, "three valued 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
BURLEEZE  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.”

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.
Question: 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?

Making a datablock 100% full

There is a trick to making a data file 100% full. Remember that Oracle always allocates one data file header block at the beginning of each data file, and we must account for this data block if we want to pack the file to 100%. For example, assume that we want to create a one-megabyte data file. The file size would be 1,048,576 bytes. However, we would not be able to place a table into this data file with an INITIAL extent of 1M, because of the annoying header block. However, if we make the data file 1M+8K (assuming we have an 8K db_block_size), then we will be able to allocate a 10-megabyte extent into this data file, and fill it to 100%. Here we see the importance of NULL values when joining a table row with NULL values from a non-existent row value. Without the NVL clause, outer joins would be very difficult to manage.

Using the Null-Value Clause with Oracle SQL

The null value clause of Oracle SQL arises from the "three valued logic" that is the foundation of the SQL language. Essentially, three valued logic understands that a missing value (NULL inside Oracle), 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. Therefore, we 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
 ---------- -------------------------------------
 
 WOOLLY     85,000
 MAMMOTH    101,000
 SABERTOOTH NOT YET ASSIGNED
```

You can view the query that was just observed in the series of images below. Using the null value BIF with Oracle SQL

1) Perform outer join using the + operator. An outer join will return rows in dba_data files, even if the values are missing. So, what about the missing values from dba_free_space?

2) 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, that this value must be zero.

3) We have enclosed all queries with the NVL clause, telling Oracle to replace null values with a numeric zero.

4) Here is another way to look at the problem. When you join two tables, some rows from the first table will not have a match in the second table — 4)Here is another way to look at the problem. When you join two tables, some rows from the first table will not have a match in the second table

5) Because of this, your result will have missing values.

6) You can set these to zero instead by specifying SELECT NVL (column name, 0). This says, "If this is NULL, replace with zero." — 6) You can set these to zero instead by specifying SELECT NVL(column name, 0). This says, "If this is NULL, replace with zero."

SELECT 
B.TABLESPACE_NAME,                                              
SUBSTR(B.FILE_NAME,1,100),                                 
B.FILE_ID,                                                     
NVL(COUNT(*),0) PIECES,
NVL(ROUND
((SUM(b.BYTES)/COUNT(*))/1048576),0) 
ALLOC_MBYTES,
NVL(ROUND
((SUM(A.BYTES))/1048576),0) FREE_MBYTES,
NVL(ROUND
 (100 - 
 (NVL(100-
 (SUM(NVL(A.BYTES,0))/(SUM(NVL(B.BYTES,0))
 /COUNT(*)))*100,0))),0)PCT_FREE_MBYTES,  
NVL(ROUND
 (MAX(A.BYTES/1048576)),0)
CONTIGUOUS_FREE_MBYTES,
NVL(ROUND
 (100 - 
 (NVL(100-
 (MAX(NVL(A.BYTES,0))/(SUM(NVL(B.BYTES,0))
 /COUNT(*)))*100,0))),0)PCT_CONTIGUOUS_FREE,
SYSDATE STATS_DATE,
NVL(ROUND
 ((SUM(B.BYTES)/COUNT(*)-NVL(SUM(A.BYTES),0))
 /1048576),0)USED_MEG,
NVL(ROUND
 (NVL(100-
 (SUM(NVL(A.BYTES,0))/(SUM(NVL(B.BYTES,0))
 /COUNT(*)))*100,0)),0) PCT_USED_MBYTES,   
NULL DB_NAME,
NULL SERVER_NAME
FROM 
SYS.DBA_FREE_SPACE A,                                             
SYS.DBA_DATA_FILES B                                              
WHERE
A.FILE_ID (+) = B.FILE_ID 
AND
B.STATUS = 'AVAILABLE'                                           
GROUP BY 
B.TABLESPACE_NAME, 
B.FILE_NAME, 
B.FILE_ID;

Now, let us look at another powerful translation BIF called DECODE.

[1]:truth values of conditions In SQL, conditions are expressions that evaluate to either TRUE, FALSE, or UNKNOWN (NULL). These truth values determine which rows of data are affected by SQL statements like WHERE (for filtering), UPDATE (for modifying), or in the logic of control flow structures.