Understanding Null Values


How does a database represent a value that is unknown? The answer is to use a special value, known as a null value . A null value is not a blank string ”it is a distinct value. A null value means the value for the column is unknown.

When you select a column that contains a null value, you see nothing in that column. You saw this (or rather, didn t see it!) in the earlier examples of selecting rows from the customers table: customer #4 has a null value in the dob column, and customer #5 has a null value in the phone column.

You can also check for null values using the IS NULL clause in a SELECT statement. In the following example, customer #4 is retrieved based on the fact that the dob column is null:

  SELECT customer_id, first_name, last_name, dob   FROM customers   WHERE dob IS NULL;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB ----------- ---------- ---------- ---------  4 Gail Black 

Similarly, in the following example, customer #5 is retrieved based on the fact that the phone column is null:

  SELECT customer_id, first_name, last_name, phone   FROM customers   WHERE phone IS NULL;  CUSTOMER_ID FIRST_NAME LAST_NAME PHONE ----------- ---------- ---------- ------------  5 Doreen Blue 

Since null values don t display anything, how do you tell the difference between a null value and a blank string if you retrieve all the rows? The answer is to use one of Oracle s built-in functions: NVL() . NVL() allows you to convert a null value into another value ”which you can actually read. NVL() accepts two parameters: a column (or more generally , any expression that results in a value), and the value that should be substituted if the first parameter is null. In the following example, NVL() is used to convert a null value in the phone column to the string Unknown phone number :

  SELECT customer_id, first_name, last_name,   NVL(phone, 'Unknown phone number') AS PHONE_NUMBER   FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME PHONE_NUMBER ----------- ---------- ---------- --------------------  1 John Brown 800-555-1211  2 Cynthia Green 800-555-1212  3 Steve White 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue Unknown phone number 

In addition to using NVL() to convert string columns that contain null values, you can use NVL() to convert number columns and date columns. In the following example, NVL() is used to convert a null value in the dob column to the date 01-JAN-2000 :

  SELECT customer_id, first_name, last_name,   NVL(dob, '01-JAN-2000') AS DOB   FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB ----------- ---------- ---------- ---------  1 John Brown 01-JAN-65  2 Cynthia Green 05-FEB-68  3 Steve White 16-MAR-71  4 Gail Black 01-JAN-00  5 Doreen Blue 20-MAY-70 

Notice that customer #4 s dob is now displayed as 01-JAN-00 .




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net