Null or Empty

If you are in IT long enough, you know the difference between a NULL value and an Empty string. I remember it well because it caused my production dashboard to go empty and my cube was full of managers and directors. Oracle started to treat empty string as NULL. How would you describe empty string? Oracle Database treats NULL as character value with a length of Zero. Null has a Greek Omega symbol (ω). Null indicates data that is unknown which is different from empty or zero value. No two NULL values are equal because it has unique value, therefore you can’t use the operands like equals, less than, greater than etc.

Example: Table that accepts NULL value. The table named nulloremptly has id, first name and last name that are mandatory whereas middle name is not. Only column middle name is NULL, the remaining columns are set to be NOT NULL.

CREATE TABLE baniya.nullorempty 
  ( 
     id         NUMBER NOT NULL, 
     firstname  VARCHAR2(25) NOT NULL, 
     middlename VARCHAR2(25) NULL, 
     lastname   VARCHAR2(25) NOT NULL 
  ); 

How do you check if the column is null or not null for existing table? use oracle command describe or desc. command displays the column lists for a table along with their properties.

describe baniya.nullorempty; 

NULL DML:

INSERT INTO baniya.nullorempty(
   id,
   firstname,
   middlename,
   lastname)
  VALUES(
   51,
   'Jay',
   NULL,
   'Smith'); -- Inserting null

EMPTY DML:

INSERT INTO baniya.nullorempty(id, firstname, middlename, lastname) 
 VALUES(53,'Joy', '', 'Theisan');  -- Inserting empty string

'' gets converted to NULL on Insert or treats '' as NULL which I am not sure but it is NULL for sure. Is this empty string we are inserting here?

SELECT id, 
       firstname, 
       middlename, 
       lastname 
FROM   baniya.nullorempty 
WHERE  middlename IS NULL; 

Query Result:

SELECT * FROM baniya.nullorempty WHERE middlename = '';     

This query displays nothing, why? Oracle now treats empty string as NULL whereas in previous versions of Oracle, NULL and empty string were treated differently which caused confusion and issue that were hard to troubleshoot. You may still see this empty/null with other database management system.

NOT NULL is reverse of NULL. It displays everything that has record.

SELECT * FROM baniya.nullorempty WHERE middlename IS NOT NULL;    

In this insert example, we are inserting an empty space of length 1. Is this empty string?

INSERT INTO baniya.nullorempty 
            (id, 
             firstname, 
             middlename, 
             lastname) 
VALUES      ( 55, 
             'Anil', 
             ' ', 
             'Thompson' );    

Query Result:

We have 2 records with NULL values, and one record with empty string of length 1. Selecting a record with empty string of length 1 isn’t easy since we don’t know the length therefore It is a good idea not to insert empty records like we did in this example. This can cause a serious problem with the application using it or for someone who is doing some analytic and is new to database.

How to check if the record is empty or null?

SELECT middlename, 
       DUMP(middlename) 
FROM   baniya.nullorempty 
ORDER  BY middlename;    

Query Result:

UPDATE:

UPDATE baniya.nullorempty
SET middlename = 'X'
WHERE middlename IS NULL;   

DELETE:

DELETE FROM baniya.nullorempty WHERE middlename IS NULL;  

PL/SQL:

DECLARE
 counter varchar2(20) NULL;
BEGIN
 IF counter IS NULL  THEN
  dbms_output.put_line('NULL Value');
 ELSE
  dbms_output.put_line('NOT NULL');
 END IF; 
END;
/

Sorting: When sorting, will it appear first or last? This is something you can control and I would consider this as a best practices.

SELECT * 
FROM   baniya.nullorempty 
ORDER  BY middlename; -- null last 

SELECT * 
FROM   baniya.nullorempty 
ORDER  BY middlename DESC; -- null first 

SELECT * 
FROM   baniya.nullorempty 
ORDER  BY middlename DESC nulls first; --null first 

SELECT * 
FROM   baniya.nullorempty 
ORDER  BY middlename DESC nulls last; -- null last 

Function: NULL Vs Empty:  We will experiment what functions will behave when passing NULL and empty string.

Function with NULL and empty string:

SELECT ' dept 30 has: ', 
       baniya.Get_dept_count(30) AS count 
FROM   dual; -- Passing real dept number 

Query Result:

SELECT ' dept 30 has: ', 
       baniya.Get_dept_count(NULL) AS count 
FROM   dual; -- Passing null 

Query Result:

SELECT ' dept 30 has: ', 
       baniya.Get_dept_count('') AS count 
FROM   dual; -- empty string 

Query Result:

SELECT ' dept 30 has: ', 
       baniya.Get_dept_count() AS count 
FROM   dual; -- passing nothing 

Error Code:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_DEPT_COUNT'

06553. 00000 - "PLS-%s: %s"

*Cause:   

*Action:

Error at Line: 12 Column: 26

Note: You will also see this ORA- Error code when you pass incomplete input parameter to a functions or procedure also.

Stored Procedure: We have a procedure called spin that takes two number as input. Let’s experiment a procedure with null, empty string and wrong parameter to study the behavior PL/SQL error.

exec baniya.spin (5,5); -- Passing correct params, works

exec baniya.spin(null, null); -- Passing NULL as param, works

exec baniya.spin('',''); --Passing empty string as param, works

exec baniya.spin(); -- Passing nothing as param, throws error

exec baniya.spin('','','');

Output:

you get same error as above:

Error starting at line : 19 in command -

exec baniya.spin()

Error report -

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'SPIN'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

Database Constraints:

What database constraints enforce null or not null? There are no database constrains that enforce null but there are constraints like UNIQUE, PK that enforce not null. Whenever these constraints are missing from the table, you can assume null allowable column. The safest and surest way is to describe a table and look for null able.

Built In Function:

NVL is a SQL built in Function that checks for NULL and replaces with a null column with a string that you have provided to be replaced.

NVL Example:

SELECT id,
 firstname,
 NVL(middlename, 'No Middle Name'),
 lastname
FROM baniya.nullorempty; 

Null is confusing to new comers but it is relatively easy to understand and use it. Anytime you work with table with NULL or empty string, you should test it thoroughly with different datatype and may behave differently.