Understand Nulls values into Oracle (1)

The aim of this post is to explain how oracle process nulls values. It is based on Oracle Documentation. It shows different behaviors related to Nulls values.

        If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Oracle Database treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent.

Note:

Oracle Database currently treats a character value with a length of zero as null.

Character with a length of zeor

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.

Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null.

SELECT NULL+10 FROM DUAL ; è NULL

In fact, all operators (except concatenation) return null when given a null operand.

select null||'STRING' from dual ; è ‘STRING’

ORDER BY

NULL values sort higher than NOT NULL values when ORDER BY sorts on a column containing NULL values.

• If the null ordering is not specified then the handling of the null values is:
  • NULLS LAST if the sort is ASC
  • NULLS FIRST if the sort is DESC
• If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with NULLS LAST.

 

ORDER BY DESC

For functional reasons we need get employees having the highest commissions first. There are two features used in this case:

NULLS FIRST

Specifies that NULL values should be returned before non-NULL values.

NULLS LAST

Specifies that NULL values should be returned after non-NULL values.

NULLS LAST 

In next posts we will continue to explore more behavior related to nulls values .


Next : Understand Nulls values into Oracle (2) : Nulls in Conditions