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

This second post, we will talk about how Oracle evaluate conditions :

        A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-20 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

Table 2-20 Conditions Containing Nulls

Condition	Value of A		Evaluation
a IS NULL	10	SQL> SELECT 1 FROM DUAL WHERE 10 IS NULL; No rows selected	FALSE
a IS NOT NULL	10	SQL> SELECT 1 FROM DUAL WHERE 10 IS NOT NULL;          1 ----------          1	TRUE
a IS NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL IS  NULL;          1 ----------          1	TRUE
a IS NOT NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL IS NOT  NULL; No rows selected	FALSE
a = NULL	10	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected	UNKNOWN
a != NULL	10	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected	UNKNOWN
a = NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE N!ULL=NULL; No rows selected	UNKNOWN
a != NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL!=NULL; No rows selected	UNKNOWN
a = 10	NULL	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected ucune ligne sÚlectionnÚe	UNKNOWN
a != 10	NULL	SELECT 1 FROM DUAL WHERE NULL!=10; No rows selected	UNKNOWN
NOT(a=NULL)	NULL	SQL>  select * from dual where not(null=null) No rows selected	UNKNOWN

For the truth tables showing the results of logical conditions containing nulls, see Table 7-5, Table 7-6, and Table 7-7.

Nulls with Comparison Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Refer toD ECODE for syntax and additional information.

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.(Constraints)

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

Last post: Understand Nulls values into Oracle (1)

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

Four useFul dTrace scripTs you can porT To sysTemTap

Read it ==>

Oratop - an interesting database monitoring tool for linux

Oratop - an interesting database monitoring tool for linux

 

oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Using DTrace on Oracle Linux by Richard Friedman

Using DTrace on Oracle Linux

by Richard Friedman

An introduction to DTrace probes and providers for Oracle Linux and how they are different from those in Oracle Solaris. Also covers DTrace commands and scripting. 

Read ==>