RelationalDBDesign RelationalDBDesign

ANSI SQL Extensions  «Prev 

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

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."