The UNKNOWN null.

4

We all are at least a bit aware of the awkward behaviour of NULL in Oracle. But it still is difficult every time we encounter it in a function or where-clause.
Some examples to keep in mind when comparing NULL values:

1. null equals what?

Null equals nothing, in fact. It equals even not to it self. ....
And a condition does not always behave in the way you would expect.

Let’s start straightforward.

a := 10<br /><br />a is null		results in		FALSE<br />a is not null		results in 		TRUE<br /><br />a := null<br /><br />a is null		results in 		TRUE<br />a is not null		results in		FALSE

We’ve all been there, done that and got ourselves the t-shirt.

a := null<br />b := null

This is a situation that might very well occur. Both a and b are null. Let’s compare it!

select 'x' from dual where a = b;

What is the result? FALSE?

Wrong! But it’s certainly not TRUE either. This expression results to UNKNOWN. And that state is a very dangerous one to be in!

Because UNKNOWN acts very similar to FALSE you might be tricked in thinking that they always behave the same. And that is not true. When a condition evaluates to UNKNOWN, no rows will be returned. In that sense it is similar. But look at this:

SQL&gt; create table nulls( id number(9), value1 varchar2(20));<br /><br />Table created.<br /><br />SQL&gt; insert into nulls values ( 1, 'A collection of ');<br /><br />1 row created.<br /><br />SQL&gt; insert into nulls values( 2, null );<br /><br />1 row created.<br /><br />SQL&gt; insert into nulls values( 3, 'something else');<br /><br />1 row created.<br /><br />SQL&gt; select id<br />  2  from nulls<br />  3  where value1 = 'A collection of '<br />  4  /<br /><br />        ID<br />----------<br />         1<br />

That is exactly what we expected. "Where value1 = ‘A coll…’ " evaluates to TRUE, so a row is returned, and "Where value1 = null" evaluates to UNKNOWN. No row is returned. Finally, "Where value1 = ‘something else’ ", evaluates to FALSE and no row is returned. All expected behaviour.

SQL&gt; select id<br />  2  from nulls<br />  3  where NOT( value1 = 'A collection of ') <br />  4  /<br /><br />        ID<br />----------<br />         3<br />

If we place a NOT operator around the conditions, the difference becomes clear. TRUE has become FALSE, FALSE has become TRUE, and UNKNOWN… is still UNKNOWN. And hence it is not displayed.

2. Nulls in indexes

I’ve created a table with one varchar2 column and 5000 records. The column contains a normal B-Tree index (tst1) and a function based-index tst2 (with function "where NVL( value1, ‘|empty|’ )". One record is NULL, and unfortunately, that is exactly the one I am looking for. How does this affect performance?

SQL&gt; select count(*) from psi_test where value1 is null;<br /><br />1 row selected.<br /><br />Explain plan<br />----------------------------------------------------------<br />   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=11)<br />   1    0   SORT (AGGREGATE)<br />   2    1     TABLE ACCESS (FULL) OF 'PSI_TEST' (TABLE) (Cost=11 Card=509 Bytes=5599)<br /><br />Statistics<br />----------------------------------------------------------<br />          1  recursive calls<br />          0  db block gets<br />         37  consistent gets<br />          0  physical reads<br />          0  redo size<br />        228  bytes sent via SQL*Net to client<br />        276  bytes received via SQL*Net from client<br />          2  SQL*Net roundtrips to/from client<br />          0  sorts (memory)<br />          0  sorts (disk)<br />          1  rows processed<br /><br />SQL&gt; select count(*) from psi_test where nvl(value1, '|empty|') = '|empty|';<br /><br />1 row selected.<br /><br />Execution Plan<br />----------------------------------------------------------<br />   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11)<br />   1    0   SORT (AGGREGATE)<br />   2    1     INDEX (RANGE SCAN) OF 'TST2' (INDEX) (Cost=2 Card=510 Bytes=5610)<br /><br />Statistics<br />----------------------------------------------------------<br />          1  recursive calls<br />          0  db block gets<br />          3  consistent gets<br />          2  physical reads<br />          0  redo size<br />        228  bytes sent via SQL*Net to client<br />        276  bytes received via SQL*Net from client<br />          2  SQL*Net roundtrips to/from client<br />          0  sorts (memory)<br />          0  sorts (disk)<br />          1  rows processed<br /><br />SQL&gt;

As you see, the optimizer does a full table scan on the first example, and an index range scan on the second. Of course, this is a very basic example, but it explains the idea.

3. null functions

NVL is well-known, it’s been there for ages. The function NVL returns the value of its second argument if its first argument is null.

SQL&gt;  select value1, nvl( value1, '!! NULL values....' ) from nulls;<br /><br />VALUE1               NVL(VALUE1,'NULLVALU<br />-------------------- --------------------<br />A collection of      A collection of<br />                     !! NULL values....<br />something else       something else<br />

Powerful, very convenient, so often used.
And what if you want to determine the returned value based on whether an expression is null or not null? NVL is not so useful for that. But NVL2 is. NVL2 has not two but three arguments. The first is the tested value. If that is not null, the second expression is returned. If it’s null, the third expression is returned.

Example:

SQL&gt; select id<br />  2  ,      nvl2( value1<br />                , 'this column is not null'<br />                , 'and this columns is NULL' ) test<br />  3* from   nulls<br />SQL&gt; /<br /><br />        ID TEST<br />---------- ------------------------<br />         1 this column is not null<br />         2 and this columns is NULL<br />         3 this column is not null<br /><br />

I think these little examples cover most of the issues you can encounter with null values. Don’t get tricked by a null!

Share.

About Author

4 Comments

  1. Patrick Sinke on

    And that’s a thing I did not know… I always thought that ” != null. About time they change that…..

  2. Laurent Schneider on

    One I do hate about nulls is this one

    select length(”) from dual;
    LENGTH
    ———
    NULL

    of course one would expect to have 0 as the length of an empty string.

    Still Oracle may change this in a very future release, as stated in
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements005.htm#sthref486

    Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

    it will be fun I promise !

  3. Chen Shapira on

    Thats a terriffic summary! No matter how long you’ve been looking at databases, NULLs always remain a bit unexpected. I’ve bookmarked this article and will probably refer to it time and again when things get murky again :-)