Oracle NULL Handling
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose NOTE: Null In Oracle is an absence of information. A null can be assigned but it can not be equated with anything: Even itself.

While this behavior is ANSI compliant it is not similar to the behavior in many other commercial RDBMS products.
 
Equality with NULL
A simple SELECT statement to use for demonstrating the properties of NULL SELECT COUNT(*)
FROM all_tables
WHERE 1 = 1;
A NULL is not equal to a NULL SELECT COUNT(*)
FROM all_tables
WHERE NULL = NULL;
A NULL cannot be not equal to a NULL SELECT COUNT(*)
FROM all_tables
WHERE NULL <> NULL;
A NULL is does not equal an empty string SELECT COUNT(*)
FROM all_tables
WHERE NULL = '';
A NULL can  be used in an INSERT conn uwclass/uwclass@pdbdev

CREATE TABLE test (
test1   NUMBER(10),
test2   VARCHAR2(20));

INSERT INTO test
(test1, test2)
VALUES
(1, NULL);

INSERT INTO test
(test1, test2)
VALUES
(NULL, 'A');

SELECT *
FROM test;
 
NULL is a state of being that can be interrogated as to whether it does or does not exist.
A simple SELECT based on a column with a NULL SELECT *
FROM test
WHERE test1 IS NULL;

SELECT *
FROM test
WHERE test1 IS NOT NULL;
A NULL can be used in an UPDATE UPDATE test
SET test1 = '2'
WHERE test2 IS NULL;

SELECT *
FROM test;

UPDATE test
SET test2 = 'B'
WHERE test2 IS NOT NULL;

SELECT *
FROM test;
A column can be updated to not contain a value UPDATE test
SET test1 = NULL
WHERE ROWNUM = 1;

SELECT *
FROM test;
NULL can be used as part of the WHERE clause criteria in a DELETE statement DELETE FROM test
WHERE test1 IS NULL;

SELECT *
FROM test;
Understand the implications of NULL conn uwclass/uwclass@pdbdev

CREATE TABLE t (
col1 NUMBER(3),
col2 NUMBER(3),
col3 NUMBER(3));

desc t

INSERT INTO t
(col1, col2, col3)
VALUES
(1, NULL, NULL);

INSERT INTO t
(col1, col2, col3)
VALUES
(NULL, 2, NULL);

INSERT INTO t
(col1, col2, col3)
VALUES
(NULL, NULL, 3);

INSERT INTO t
(col1, col2, col3)
VALUES
(4, 4, 4);

COMMIT;

SELECT *
FROM t;

SELECT SUM(RESULT_TMP) RESULT
FROM (
  SELECT col1 - (col2 + col3) RESULT_TMP
  FROM t);

SELECT SUM(col1) - (SUM(col2) + SUM(col3)) RESULT
FROM t;

Note: For any row that has one of the values null, the entire row sums to null and is not included in the second query but the other columns in the row contribute to the sums in the query. So the first query includes more terms than the second.

Related Topics
Built-in Functions
Miscellaneous Functions: NVL
Miscellaneous Functions: NVL2
Select Statements

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved