The Concept of Null

When writing SQL statements, you must be vigilant for possible nulls, always taking care to consider their possible effect on a WHERE clause or other SQL expression. What is a null ? It's what you get in the absence of a specific value. Suppose that you issue the INSERT statement shown in Example 4-20 to record a new hire in your database:

INSERT INTO employee (employee_id, employee_name)

 VALUES (116, 'Roxolana Lisovsky');

Quick! What value do you have for a hire date? What about the termination date? The answer is that it depends. The employee table happens to have a default value specified for the employee_hire_date column. Because the INSERT statement doesn't specify a hire date, the hire date defaults to the current date and time. What about the termination date? There's no default for that column, so what's the value? The answer is there is no value. Because no value is supplied, employee_termination_date is said to be null. Example 4-20 uses the SET NULL command to make the null termination date obvious.

Example 4-20. Inserting a NULL value


INSERT INTO employee (employee_id, employee_name)


VALUES (116, 'Roxolana Lisovsky');


SET NULL ***NULL***


SELECT employee_id, employee_name,


employee_hire_date, employee_termination_date


FROM employee


WHERE employee_id = 116;

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_HIRE_DATE EMPLOYEE_TERMINATI

----------- ------------------ ------------------ ------------------

 116 Roxolana Lisovsky 03-JUN-04 ***NULL***

The problem with nulls is that the theorists who originally conceived the idea of relational databases decided that the existence of nulls warranted the creation of three-valued logic , illustrated in Figure 4-1.

Figure 4-1. The monster that is three-valued logic has bitten more than one unwary SQL developer (sketch by Matt Williams)

figs/sqp2_0401.gif

If I were to ask any person on the street where I live whether Roxolana's termination date is today, the answer I'd most likely get back would be no. People generally think in terms of something being either true or false. Roxolana's termination date is null, which is not the same as today, right? The database however, adds a new truth value, that of unknown . With some specific exceptions, any comparison to null yields a result of unknown. Ask the database whether Roxolana was terminated today, and the database would answer the equivalent of unknown. And unknown is neither true nor false.

Nullity is most critical to understand. You can't be an effective SQL user without a good grounding in three-valued logic and the effects of nulls in expressions.

The three queries in Example 4-21 clearly illustrate the effect of nulls on comparison expressions. You'll notice that Roxolana appears in none of the results. The first query illustrates that null is not not equal to today. The second query shows that null is not equal to today. The third query shows that null is not even equal to itself.

Example 4-21. The effects of "unknown"


SELECT employee_name


FROM employee


WHERE employee_termination_date <> SYSDATE;

EMPLOYEE_NAME

----------------------------------------

Mykhailo Hrushevsky

Pavlo Virsky

Pavlo Chubynsky

Ivan Mazepa

Igor Sikorsky

Mykhailo Verbytsky

SELECT employee_name


FROM employee


WHERE employee_termination_date = SYSDATE;

no rows selected

SELECT employee_name


FROM employee


WHERE employee_termination_date = NULL;

no rows selected

If null is not equal to null, how then do you even detect its existence? How do you go about returning rows with nulls?

I tend to refer to a specific column or expression result as being null. I don't usually refer to null as a value. Others, including even the authors of the ANSI/ISO SQL standard, do use the term "null value." I don't like that approach because, logically, null represents the absence of a value. The term "null value" confuses the issue. Don't get worked up over this terminology issue though. I don't. Just be aware that null and null value mean the same thing: no value at all.

 

4.3.1 Detecting Nulls

Fortunately, SQL's designers had the forethought to provide a mechanism for detecting and dealing with null values. The standard way to detect null values is to use the IS NULL predicate, as demonstrated in Example 4-22. You can use IS NOT NULL to return rows for which a given column is not null.

Example 4-22. Using IS NULL to detect nulls


SELECT employee_name, employee_termination_date


FROM employee


WHERE employee_termination_date IS NULL;

EMPLOYEE_NAME EMPLOYEE_TERMINATI

-------------------- ------------------

Marusia Churai

Mykola Leontovych

Lesia Ukrainka

Taras Shevchenko

Roxolana Lisovsky

SELECT employee_name, employee_termination_date


FROM employee


WHERE employee_termination_date IS NOT NULL;

EMPLOYEE_NAME EMPLOYEE_TERMINATI

-------------------- ------------------

Mykhailo Hrushevsky 05-MAY-04

Pavlo Virsky 01-APR-04

Pavlo Chubynsky 15-NOV-04

Ivan Mazepa 30-SEP-04

Igor Sikorsky 04-APR-04

Mykhailo Verbytsky 31-OCT-04

 

Notice that the null termination dates in Example 4-22 are blank. This is unlike the results you saw in Example 4-20 but represents the default behavior of SQL*Plus. If you want to see nulls as something other than blanks, you will need to issue a SET NULL command to specify an alternative representation.

4.3.2 Nulls in Expressions

The expressions involving nulls in Examples Example 4-21 and Example 4-22 are all WHERE clause expressions. Nulls make their presence felt in other types of expressions. The general rule is that if any value in an expression is null, then the result of that expression will be null. Example 4-23 demonstrates this concept by attempting to add $100 to the hourly rate of Roxolana.

Example 4-23. Adding to NULL yields NULL


SELECT employee_name, employee_billing_rate,


employee_billing_rate + 100 increased_rate


FROM employee


WHERE employee_id = 116;

EMPLOYEE_NAME EMPLOYEE_BILLING_RATE INCREASED_RATE

-------------------- --------------------- --------------

Roxolana Lisovsky

 

In this case, null + 100 is still null. Often treating null as if it were zero can be handy. Oracle's NVL function allows you to do that. NVL takes two arguments. The first argument may be any expression or column name ; the type does not matter. If the first argument is not null, then that argument will be returned. The second argument is an alternate value to be returned only when the first is null. Example 4-24 uses NVL to treat null billing rates as zero values.

Example 4-24. Using NVL to provide an alternative, non-NULL value


SELECT employee_name, NVL(employee_billing_rate,0) billing_rate,


NVL(employee_billing_rate,0) + 100 increased_rate


FROM employee


WHERE employee_id IN (113, 116);

EMPLOYEE_NAME BILLING_RATE INCREASED_RATE

-------------------- ------------ --------------

Mykhailo Verbytsky 300 400

Roxolana Lisovsky 0 100

 

If you work with nulls often, and especially if the NVL and IS NULL functionality is not meeting your needs, you'll want to get familiar with Oracle's NVL2 and DECODE functions, as well as with CASE expressions. Any good book on Oracle SQL will cover these functions and expressions.

     

Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

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