Oracle Database - Null value (Missing Value)

1 - About

null in the Oracle context.

By default all columns allow NULL.

Nulls are stored in the database and require 1 byte to store the length of the column (zero).

Null is not aggregated and selected on filters. If you are calculating an average grade for a student, null values will be ignored in the calculation using standard SQL, giving correct results. (the average of 5, null, 6, 7 is 6 not 4.5)

2 - Article Related

3 - What is NULL ?

3.1 - DataType

[email protected]>CREATE VIEW nullview
  2  AS
  3  SELECT NULL "null" FROM dual;
 
VIEW created.
 
[email protected]>DESCRIBE nullview;
 Name           NULL?    TYPE
 -------------- -------------------------------------
 NULL                    VARCHAR2

3.2 - Empty String

  • NULL = ''
COLUMN NULL format a4
SELECT '' AS "null" FROM dual
null
----
Null
  • Second proof with NVL
SELECT nvl('','That''s null') AS "Is That Null ?" FROM dual;
Is That Null ?
---------------
That's null

4 - Is NULL null ?

SELECT
  CASE NULL
    WHEN NULL
    THEN 'NULL is NULL'
    ELSE 'NULL is Not NULL'
  END AS "Is NULL null?"
FROM
  dual;

Result:

Is NULL null?    
---------------- 
NULL is Not NULL 

5 - I hate Null

C:\Documents AND Settings\Nicolas>sqlplus gerardnico/Password
 
SQL*Plus: Release 10.2.0.4.0 - Production ON Wed Apr 8 15:41:24 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL Rights Reserved.
 
 
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
[email protected]>CREATE TABLE IHateNull (id NUMBER NOT NULL, val1 NUMBER NULL, val2 NUMBER NULL);
 
TABLE created.
 
[email protected]>INSERT INTO IHateNull VALUES (1,2,NULL);
 
1 ROW created.
 
[email protected]>INSERT INTO IHateNull VALUES (1,NULL,1);
 
1 ROW created.
 
[email protected]>INSERT INTO IHateNull VALUES (1,0,0);
 
1 ROW created.
 
[email protected]>INSERT INTO IHateNull VALUES (1,1,1);
 
1 ROW created.
 
[email protected]>SELECT * FROM IHateNull WHERE ( val1 + val2 ) <> 0;
 
        ID       VAL1       VAL2
---------- ---------- ----------
         1          1          1
 
[email protected]>SELECT * FROM IHateNull WHERE ( nvl(val1,0) + nvl(val2,0) ) <> 0;
 
        ID       VAL1       VAL2
---------- ---------- ----------
         1          2
         1                     1
         1          1          1

6 - Null of not null

Count from a Null Column must not count the NULL value and then can not use a b-tree index to count them.

SELECT COUNT(NullColumn) FROM mytable;

The following statement can use a b-tree index.

SELECT COUNT(NotNullColumn) FROM mytable;

7 - Comparison

SYS_OP_MAP_NONNULL(X) = SYS_OP_MAP_NONNULL(Y)

SYS_OP_MAP_NONNULL

8 - Reference

db/oracle/null.txt ยท Last modified: 2017/09/13 11:46 by gerardnico