Handling Nulls, SQL Simantics

Handling Nulls

Randy Sims Oracle

Null is an unknown value. Null is Schrodinger’s Cat’s state before opening the box. There are ways to deal with null that are unlike anything else in the database, both in concept and in function.

Equalities/Inequalities

Nothing can equal or not equal Null. Not even another Null. It is an unknown value. Only something that is known can ever equal something else that is known. Otherwise, the best answer you can hope for with 0 = Every Possible Value in the Universe is, “Maybe?”

SQL> select case when null=null then 1 else 0 end "null test" from dual;

 null test
----------
 0

SQL> select case when null!=null then 1 else 0 end "null test" from dual;

 null test
----------
 0

SQL> select case when null=0 then 1 else 0 end "null test" from dual;

 null test
----------
 0

SQL> select case when null='' then 1 else 0 end "null test" from dual;

 null test
----------
 0

Querying for NULL

Null can only be searched for by using IS NULL or NOT IS NULL. As shown above, you will never find a column where that column is equal to or not equal to null. It either IS or NOT IS NULL.

SQL> select order_id
2  from orders
3  where order_date is null;

SQL> select customer_id
2  from customers
3  where middle_name is not null;

In Math

Any math expression involving a Null will result in the expression’s result being null.

SQL> set null **NULL**
SQL> select 1+1+null from dual;

 1+1+NULL
----------
**NULL**

SQL> select 1*1+null from dual
 2 ;

 1*1+NULL
----------
**NULL**

With Strings

Empty String

Oracle treats empty strings as nulls when selecting, inserting or updating.

SQL> column null_testing format a12
SQL> set null *NULL*
SQL> select '' null_testing from dual;

NULL_TESTING
----------
*NULL*

SQL> insert into test values (3,'');

1 row created.

SQL> select * from test where test_id = 3;

 TEST_ID   VAL
---------- ----------
 3         *NULL*

SQL> update test set val = '' where test_id = 3;

1 row updated.

SQL> select * from test where test_id = 3;

 TEST_ID   VAL
---------- ----------
 3         *NULL*

Concatenating

When Null is concatenated with a string, Null is treated as an empty string.

SQL> select 'this is a ' || null || 'test with nulls' as null_testing from dual;

NULL_TESTING
-------------------------
this is a test with nulls

Functions for NULL

COALESCE

One way around Nulls is to replace the null with another value. COALESCE is a command that will return the first value in a list that is not null. Coalesce will take as many arguments as you want to supply.

coalesce( arg1, arg2, arg3, ...)

SQL> select coalesce('1','A','P') as null_testing from dual;

NULL_TESTING
------------
1

SQL> select coalesce(null,'A','P') as null_testing from dual;

NULL_TESTING
------------
A

SQL> select coalesce(null,null,'P') as null_testing from dual;

NULL_TESTING
------------
P

NVL

The NVL function works like the COALESCE function except that it only accepts two arguments. The first will be returned if it is not null. The second will be returned if the first is null.

NVL(arg1, arg2)

SQL> select nvl('1','A','P') as null_testing from dual;
select nvl('1','A','P') as null_testing from dual
 *
ERROR at line 1:
ORA-00909: invalid number of arguments


SQL> select nvl('1','A') as null_testing from dual;

NULL_TESTING
-------------
1

SQL> select nvl(null,'A') as null_testing from dual;

NULL_TESTING
-------------
A

NVL2

The NVL2 function works like a CASE statement. If the first argument of NVL2 is not null, then the second argument is returned. If the first argument of NVL2 is null, then the third argument is returned. Below the NVL2 examples is shown how it would look as a CASE statement.

NVL2(arg1, arg2-returned_when_not_null, arg3-returned_when_null)

SQL> select nvl2('1','A','P') as null_testing from dual;

NULL_TESTING
-------------
A

SQL> select nvl2(null,'A','P') as null_testing from dual;

NULL_TESTING
-------------
P

SQL> select case when '1' is not null then 'A' else 'P' end as null_testing 
2  from dual;

NULL_TESTING
-------------
A

SQL> select case when null is not null then 'A' else 'P' end as null_testing 
2  from dual;

NULL_TESTING
-------------
P

As a Column Value

Null is typically the default value for all columns in a table except for those that are made Not Null.

SQL> create table test (test_id integer not null, test_desc varchar2(20));

Table created.

SQL> desc test
 Name                       Null?    Type
 -------------------------- -------- ----------------------
 TEST_ID                    NOT NULL NUMBER(38)
 TEST_DESC                           VARCHAR2(20)

Updating to NULL

When updating a column to be Null, use the equals sign.

SQL> update orders set order_date = null where order_date is not null;

Inserting a NULL

When inserting a Null, specify Null in the insert statement. Even if a column has a default value, the null value specified in the values list will override the default. In the table below, the column VAL has a default value of ‘A’. If nothing is set for that column, the value for that row will be ‘A’. The first insert sets val as null and so the value is null. The second insert only sets the test_id value, so the val value is set to the default of ‘A’.

SQL> create table test (test_id number, val varchar2(10) default 'A');

Table created.

SQL> desc test
 Name            Null?    Type
 --------------- -------- ------------------
 TEST_ID                  NUMBER
 VAL                      VARCHAR2(10)

SQL> insert into test values (1,null);

1 row created.

SQL> insert into test (test_id) values (2);

1 row created. 

SQL> set null *NULL*
SQL> select * from test;

 TEST_ID   VAL
---------- ----------
 1         *NULL*
 2         A

Tagged: Functions, Null